[postgis-users] Improving performance of query
Bryan K R Manuel
manue9fe at erau.edu
Thu May 15 22:48:50 PDT 2008
I have been working on improving the performance of the following query:
insert into tptemp (id, the_geom)
(select geomunion(b.the_geom) from parcels a, trees b
where overlaps(a.the_geom, b.the_geom)
from parcels a group by gid;
I have tried changing the where clause to ST_Distance(a.the_geom, b.the_geom) = 0, which gave me slightly different results and a very slightly better query time.
Looking through the PostGIS Reference, ST_Distance()=0, ST_Intersects(), ST_Touches() and ST_Overlaps() all appear to be able to work in the where clause. Will any of these work faster or better? My results have been mixed.
I also see that ST_Distance() does not use indexes, whereas the other do use indexes - is it better to use indexes or not to use indexes for larger spatial queries?
More information about the postgis-users