[postgis-users] speeding up "not in" query

David Epstein davideps at umich.edu
Sat Jul 24 14:09:29 PDT 2010

I have one spatial table and one non-spatial table each with about 380k
rows. They can be joined by "parcelnum". However, there are some parcel
numbers in each table that do not match. I want the full rows of the
non-matching from each. I've made a standard (b-tree) index for
"parcelnum" in both tables and then ran this query to get non-matching
rows in a single direction:

FROM survey
WHERE survey.parcelnum NOT IN
(SELECT parcel_esri.parcelnum
FROM parcels_esri)

This query has already run for 35 minutes and is still running. I have a
laptop running Ubuntu 9.04 64bit with two P8700 2.53GHz CPUs and 3.8GB
of memory. Only 1 CPU and 1.3GB of memory is being used. Is there
anything I can do to speed up not-in (and join) queries?

thank you,

More information about the postgis-users mailing list