[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 a.gid,
    (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 mailing list