<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16588"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>David,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>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. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgresonline.com/journal/archives/149-Forcing-the-planners-hand-with-set-enable_seqscan-off-WTF.html">http://www.postgresonline.com/journal/archives/149-Forcing-the-planners-hand-with-set-enable_seqscan-off-WTF.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>But couldn't figure out under what circumstances this
happens. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>IN optimizations have changed a lot over various versions
of PostgreSQL. Which version are you using?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial>Leo and Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=234070704-25072010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><FONT color=#0000ff size=2
face=Arial></FONT><FONT color=#0000ff size=2 face=Arial></FONT><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Michael Smedberg<BR><B>Sent:</B> Saturday, July 24, 2010 10:06
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
speeding up "not in" query<BR></FONT><BR></DIV>
<DIV></DIV>You might want to try a "not exists" constraint, like this:
<DIV><BR></DIV>
<DIV>
<DIV>SELECT *</DIV>
<DIV>FROM survey</DIV>
<DIV>WHERE NOT EXISTS </DIV>
<DIV>(SELECT parcel_esri.parcelnum</DIV>
<DIV>FROM parcels_esri WHERE parcel_esri.parcelnum = survey.parcelnum)</DIV><BR>
<DIV class=gmail_quote>On Sat, Jul 24, 2010 at 2:09 PM, David Epstein <SPAN
dir=ltr><<A
href="mailto:davideps@umich.edu">davideps@umich.edu</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>I have one spatial table and one non-spatial table each with
about 380k<BR>rows. They can be joined by "parcelnum". However, there are some
parcel<BR>numbers in each table that do not match. I want the full rows of
the<BR>non-matching from each. I've made a standard (b-tree) index
for<BR>"parcelnum" in both tables and then ran this query to get
non-matching<BR>rows in a single direction:<BR><BR>SELECT *<BR>FROM
survey<BR>WHERE survey.parcelnum NOT IN<BR>(SELECT
parcel_esri.parcelnum<BR>FROM parcels_esri)<BR><BR>This query has already run
for 35 minutes and is still running. I have a<BR>laptop running Ubuntu 9.04
64bit with two P8700 2.53GHz CPUs and 3.8GB<BR>of memory. Only 1 CPU and 1.3GB
of memory is being used. Is there<BR>anything I can do to speed up not-in (and
join) queries?<BR><BR>thank
you,<BR>-david<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>