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: [email protected] [mailto:[email protected]] 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 <[email protected]> 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
