3

I have the following query, but it takes a long time to run. I tried creating an index, but there hasn't been much improvement. Any suggestions?

CREATE INDEX temp_clusters_geom_idx
ON temp_clusters
USING GIST(concave_hull)

CREATE INDEX temp_da_geom_idx
ON temp_da
USING GIST(concave_hull)

SELECT 
c.id, 
ST_Area(di.concave_hull) - ST_Area(ST_Difference(di.concave_hull,c.concave_hull)) as internal_area,
(ST_Area(di.concave_hull) - ST_Area(ST_Difference(di.concave_hull,c.concave_hull))) * cast(di."Income" as float) as overall_weighted_area,
c.concave_hull
FROM temp_clusters c
JOIN
    (
        SELECT d.concave_hull,"Income" 
        FROM temp_da d 
        JOIN temp_da_income i on d."DAUID" = i."DAUID"
        WHERE "Income" is NOT NULL
    )di
ON ST_Intersects(c.concave_hull, di.concave_hull) 
WHERE category = 'hexagon'

here, temp_da size is 57932 and temp_clusters is 34964 And, this part is quite fast as well:

SELECT d.concave_hull,"Income" 
        FROM temp_da d 
        JOIN temp_da_income i on d."DAUID" = i."DAUID"

Query plan:

"Gather  (cost=3078.30..595214897.33 rows=28033277 width=1555)"
"  Workers Planned: 2"
"  ->  Hash Join  (cost=2078.30..592410569.63 rows=11680532 width=1555)"
"        Hash Cond: (d.""DAUID"" = i.""DAUID"")"
"        ->  Nested Loop  (cost=0.28..2231655.66 rows=12575461 width=8101)"
"              ->  Parallel Seq Scan on temp_clusters c  (cost=0.00..12552.37 rows=14636 width=1539)"
"                    Filter: (category = 'hexagon'::text)"
"              ->  Index Scan using idx_temp_da_concave_hull on temp_da d  (cost=0.28..151.56 rows=6 width=6562)"
"                    Index Cond: (concave_hull && c.concave_hull)"
"                    Filter: st_intersects(c.concave_hull, concave_hull)"
"        ->  Hash  (cost=1405.36..1405.36 rows=53813 width=15)"
"              ->  Seq Scan on temp_da_income i  (cost=0.00..1405.36 rows=53813 width=15)"
"                    Filter: (""Income"" IS NOT NULL)"
2
  • please add the explain output for your query
    – Ian Turton
    Apr 7, 2023 at 9:37
  • 2
    "long time" isn't as descriptive as it could be, but it looks like you forgot to index temp_da_income on ("DAUID","Income")
    – Vince
    Apr 7, 2023 at 13:24

2 Answers 2

4

There seem to be two "expensive" operations in the explain output, which are easy to fix.

category = 'hexagon' (12552) and ""Income"" IS NOT NULL (1405)

So put an index on category and mark Income as Not NULL (unless it is nullable, in which case add an index with Nulls at the end).

Finally, most of your time is going on:

Hash Join  (cost=2078.30..592410569.63 rows=11680532 width=1555)" 

where Postgres does the final join, have you made DAUID a primary key on both tables?

3

try forcing a bounding box intersect before the actual geom intersect like this. if its a small number of features it likely wont have much of an impact but its many complex geometries it should help

SELECT 
c.id, 
ST_Area(di.concave_hull) - ST_Area(ST_Difference(di.concave_hull,c.concave_hull)) as internal_area,
(ST_Area(di.concave_hull) - ST_Area(ST_Difference(di.concave_hull,c.concave_hull))) * cast(di."Income" as float) as overall_weighted_area,
c.concave_hull
FROM temp_clusters c
JOIN
    (
        SELECT d.concave_hull,"Income" 
        FROM temp_da d 
        JOIN temp_da_income i on d."DAUID" = i."DAUID"
        WHERE "Income" is NOT NULL
    )di
ON c.cancave_hull && di.concave_hull AND ST_Intersects(c.concave_hull, di.concave_hull) 
WHERE category = 'hexagon'
3
  • 2
    ST_Intersects automatically includes a bounding box operator call, so this should not be necessary. (You can see the query plan includes an index Scan using &&.)
    – dr_jts
    Apr 8, 2023 at 3:03
  • 1
    I've actually had queries which failed to use the PostGIS index without an explicit && operation. The processing time difference was measured in days. Sometimes "should not" is not enough to protect performance.
    – Vince
    Apr 8, 2023 at 16:25
  • 1
    explicitly adding the bounding box has sped but many queries for me as well.
    – ziggy
    Apr 8, 2023 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.