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

[nguyen liem]

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 <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
>



-- 
*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"*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100726/432ad833/attachment.html>