[postgis-users] Improving performance of query

[Bryan K R Manuel]

Kevin,

Thanks a lot for the help.  I was missing the reference from the outer table to the inner query, and per your suggestion I don't actually need the inner query.  Although the time improvement is marginal, it seems that the query planner is telling me that it is a bit more efficient.

I think the time improvement is only marginal because of the size of the trees dataset.  I ran your suggested code, and found that one parcel had 38 tree geometries on it, with several other parcels having more than 20 geometries.  As such, the geomunion is probably what is holding back the performance, but I don't know of anything I can do about that.  I did try the buffer(geom,0) trick, but it didn't end up giving me the correct results.

Do I need to manually create an index on the shapefiles that I have loaded.  In some examples I've seen they do it, whereas in others they do not.  I noticed that even though I haven't manually created an index, the query planner is telling me that it is doing index scans, so I assume that the polygons are automatically indexed?

Thanks again,

Bryan