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

[Paragon Corporation]

David,
 
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.  
http://www.postgresonline.com/journal/archives/149-Forcing-the-planners-hand
-with-set-enable_seqscan-off-WTF.html
 
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?
 
Thanks,
Leo and Regina
http://www.postgis.us
 


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Michael
Smedberg
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: 

SELECT *
FROM survey
WHERE NOT EXISTS 
(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:

SELECT *
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,
-david

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



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