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

Paragon Corporation lr at pcorp.us
Sat Jul 24 21:12:13 PDT 2010

What does your query plan look like?  Do you have an indeed in place for the
parcelnum columns and are they the same data type.
You may want to verify the index is being used by looking at the query plan.
We have run into similar issues which we documented here.  
But couldn't figure out under what circumstances this happens.  
IN optimizations have changed a lot over various versions of PostgreSQL.
Which version are you using?
Leo and Regina


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Michael
Sent: Saturday, July 24, 2010 10:06 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] speeding up "not in" query

You might want to try a "not exists" constraint, like this: 

FROM survey
(SELECT parcel_esri.parcelnum
FROM parcels_esri WHERE parcel_esri.parcelnum = survey.parcelnum)

On Sat, Jul 24, 2010 at 2:09 PM, David Epstein <davideps at umich.edu> wrote:

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,

postgis-users mailing list
postgis-users at postgis.refractions.net

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100725/757a124a/attachment.html>

More information about the postgis-users mailing list