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.
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Ben Madin Sent: Tuesday, June 07, 2011 12:17 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Selecting Unique polygons with st_intersects() 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) _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
