Re: Non Matching Records in Two Tables

On Tue, 2006-02-14 at 13:24 -0800, Ken Hill wrote:<br /><blockquote type="CITE"><font color="#000000">On Tue,
2006-02-14at 13:08 -0800, chester c young wrote: </font><blockquote type="CITE"><pre>
 
<font color="#000000">> Here is my query SQL:</font>
<font color="#000000">> </font>
<font color="#000000">> SELECT key100 FROM ncccr10</font>
<font color="#000000">> WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);</font>
<font color="#000000">> </font>
<font color="#000000">> It is is running after 30 minutes. Here is the query plan:</font>
<font color="#000000">></font>

<font color="#000000">I would try an outer join:</font>

<font color="#000000">select a.key100</font>
<font color="#000000">from ncccr10 a</font>
<font color="#000000">left join ncccr9 b on( key100 )</font>
<font color="#000000">where b.key100 is null;</font>

<font color="#000000">also (hate to be obvious) have you analyzed lately?</font>


<font color="#000000">__________________________________________________</font>
<font color="#000000">Do You Yahoo!?</font>
<font color="#000000">Tired of spam?  Yahoo! Mail has the best spam protection around </font>
<font color="#000000"><a href="http://mail.yahoo.com">http://mail.yahoo.com</a> </font>

<font color="#000000">---------------------------(end of broadcast)---------------------------</font>
<font color="#000000">TIP 1: if posting/reading through Usenet, please send an appropriate</font>
<font color="#000000">       subscribe-nomail command to <a
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>so that your</font>
 
<font color="#000000">       message can get through to the mailing list cleanly</font>
</pre></blockquote><font color="#000000">Yes, I did a VACUUM ANALYZE on the entire database before running this query.
Also,I get an error with your suggestion:</font><br /><br /><font color="#000000">csalgorithm=# SELECT a.key100 FROM
ncccr10a</font><br /><font color="#000000">csalgorithm-# LEFT JOIN ncccr9 b ON(a.key100)</font><br /><font
color="#000000">csalgorithm-#WHERE b.key100 IS Null;</font><br /><font color="#000000">ERROR:  argument of JOIN/ON must
betype boolean, not type character</font><br /><br /></blockquote> Well, this works:<br /><br /> SELECT *<br /> FROM
ncccr9a<br /> LEFT JOIN ncccr10 b USING( key100 )<br /> WHERE b.key100 is null;<br /><br /> It still seems slow. It
takesabout the same time to run as in MS Access. I thought PostgreSQL would be faster.