[postgis-users] Selecting Unique polygons with st_intersects()

[Ben Madin]

Steve, 

for fun... why not!

It actually took longer. I'm very bad at understanding query plans, but it looks to me like the planner thought it would be quicker on less rows, but it actually took much longer...

any thoughts would be appreciated - I've attached the queries and output below. FWIW there are indexes on both gid fields (primary keys) and both geometry fields (GIST)

Ben





On 07/06/2011, at 11:12 AM, Stephen Woodbridge wrote:

> I know the newer functions are supposed to be index aware, but for grins, try changing the ON clause to
> 
> ON q.the_geom && l.gda_geom and st_distance(q.the_geom, l.gda_geom)=0.0
> AND l.gid ...
> 
> Objects that intersect have to have a distance of 0.0 and the distance function may have some faster algorithms than intersect.
> 
> -Steve


EXPLAIN ANALYZE SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as shire, propname as name, pic, round((st_area(st_transform(q.the_geom, 32754))/10000)::numeric,2) as ha 
FROM lga l 
JOIN qldproperties q 
ON st_intersects(q.the_geom, l.gda_geom) 
AND l.gid IN (245,247,252,254,258,259,275,279,289,297)
ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC;

                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=312868.52..312871.53 rows=602 width=405051) (actual time=19483.631..19484.592 rows=2673 loops=1)
   ->  Sort  (cost=312868.52..312870.03 rows=602 width=405051) (actual time=19483.629..19483.828 rows=2861 loops=1)
         Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
         Sort Method:  quicksort  Memory: 432kB
         ->  Nested Loop  (cost=0.00..276.73 rows=602 width=405051) (actual time=9.762..19446.773 rows=2861 loops=1)
               Join Filter: _st_intersects(q.the_geom, l.gda_geom)
               ->  Seq Scan on lga l  (cost=0.00..31.76 rows=10 width=400338) (actual time=0.124..0.262 rows=10 loops=1)
                     Filter: (gid = ANY ('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
               ->  Index Scan using qldproperties_the_geom_gist on qldproperties q  (cost=0.00..8.28 rows=1 width=4713) (actual time=0.036..1.680 rows=359 loops=10)
                     Index Cond: (q.the_geom && l.gda_geom)
 Total runtime: 19489.877 ms



EXPLAIN ANALYZE SELECT DISTINCT ON (pic) 'QLD'::text as state, lga_name09 as shire, propname as name, pic, round((st_area(st_transform(q.the_geom, 32754))/10000)::numeric,2) as ha 
FROM lga l 
JOIN qldproperties q 
ON q.the_geom && l.gda_geom and st_distance(q.the_geom, l.gda_geom)=0.0
AND l.gid IN (245,247,252,254,258,259,275,279,289,297)
ORDER BY pic, st_area(st_intersection(q.the_geom, l.gda_geom)) DESC;

                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=1680.75..1680.80 rows=9 width=405051) (actual time=56247.410..56248.288 rows=2673 loops=1)
   ->  Sort  (cost=1680.75..1680.77 rows=9 width=405051) (actual time=56247.408..56247.586 rows=2861 loops=1)
         Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
         Sort Method:  quicksort  Memory: 432kB
         ->  Nested Loop  (cost=0.00..119.61 rows=9 width=405051) (actual time=33.922..56211.975 rows=2861 loops=1)
               Join Filter: (st_distance(q.the_geom, l.gda_geom) = 0::double precision)
               ->  Seq Scan on lga l  (cost=0.00..31.76 rows=10 width=400338) (actual time=0.050..0.212 rows=10 loops=1)
                     Filter: (gid = ANY ('{245,247,252,254,258,259,275,279,289,297}'::integer[]))
               ->  Index Scan using qldproperties_the_geom_gist on qldproperties q  (cost=0.00..8.28 rows=1 width=4713) (actual time=0.034..1.639 rows=359 loops=10)
                     Index Cond: (q.the_geom && l.gda_geom)
 Total runtime: 56248.683 ms
(11 rows)