Hi David, you can use "except" instead of "not in", the query should be changed to:
SELECT survey.parcelnum FROM survey *except* (SELECT parcel_esri.parcelnum FROM parcels_esri) I think the query above will be faster than the old one. After you had survey.parcelnum, you can do something with these. Cheers. On Sun, Jul 25, 2010 at 4:09 AM, 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 > -- *Liem Nguyen (DBA) AXON ACTIVE VIETNAM* *www.axonactive.vn*** 10th Floor, Hai Au Building, 39B Truong Son, Ward 4, Tan Binh District, Ho Chi Minh City, Vietnam T +84 8 629 738 59, F +84 8 381 134 89, M +84 168 994 8897 *8897 +10° 48' 32.72", +106° 39' 51.58"*
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
