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

[Ben Madin]

Hi Regina,

On 07/06/2011, at 1:54 PM, Paragon Corporation wrote:

> What about ST_Dwithin(q.the_geom, l.gda_geom,0.00001)
> 
> && and ST_Distance actually doesn't have as much short-circuiting as
> ST_Dwithin since it has to compute the distance for && matches  --
> ST_Dwithin in addition to having && built in kicks out once it has concluded
> the within distance criteria has been achieved. 

Vey similar - looking at the Join filter it appears to be the same issue where the planner is overly optimistic about the time it will take.



                                                                            QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=117.57..117.58 rows=1 width=405051) (actual time=56098.368..56099.248 rows=2679 loops=1)
   ->  Sort  (cost=117.57..117.58 rows=1 width=405051) (actual time=56098.367..56098.540 rows=2870 loops=1)
         Sort Key: q.pic, (st_area(st_intersection(q.the_geom, l.gda_geom)))
         Sort Method:  quicksort  Memory: 433kB
         ->  Nested Loop  (cost=0.00..117.56 rows=1 width=405051) (actual time=38.165..56061.873 rows=2870 loops=1)
               Join Filter: ((l.gda_geom && st_expand(q.the_geom, 1e-05::double precision)) AND _st_dwithin(q.the_geom, l.gda_geom, 1e-05::double precision))
               ->  Seq Scan on lga l  (cost=0.00..31.76 rows=10 width=400338) (actual time=0.110..0.255 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.676 rows=359 loops=10)
                     Index Cond: (q.the_geom && st_expand(l.gda_geom, 1e-05::double precision))
 Total runtime: 56099.663 ms
(11 rows)


cheers

Ben