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