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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
