From ken@scottshill.com Wed Feb  8 21:15:05 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 581069DCC67
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:15:05 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 55261-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:15:05 -0400 (AST)
X-Greylist: delayed 00:05:01.528835 by SQLgrey-
Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.192.82])
	by postgresql.org (Postfix) with ESMTP id AF18A9DCABB
	for <pgsql-sql@postgresql.org>; Wed,  8 Feb 2006 17:15:02 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc12) with SMTP
	id <20060208211002m120001dtie>; Wed, 8 Feb 2006 21:10:02 +0000
Subject: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="=-Y1cYpEHeuRYV0Vowj8Mt"
Organization: Scotts Hill Associates, Inc.
Date: Wed, 08 Feb 2006 13:10:23 -0800
Message-Id: <1139433024.2044.48.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.024 required=5 tests=[AWL=0.023,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.024
X-Spam-Level: 
X-Archive-Number: 200602/83
X-Sequence-Number: 24093


--=-Y1cYpEHeuRYV0Vowj8Mt
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

I need some help with a bit of SQL. I have two tables. I want to find
records in one table that don't match records in another table based on
a common column in the two tables. Both tables have a column named
'key100'. I was trying something like:

SELECT count(*)
FROM table1, table2
WHERE (table1.key100 != table2.key100);

But the query is very slow and I finally just cancel it. Any help is
very much appreciated.

-Ken

--=-Y1cYpEHeuRYV0Vowj8Mt
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
I need some help with a bit of SQL. I have two tables. I want to find records in one table that don't match records in another table based on a common column in the two tables. Both tables have a column named 'key100'. I was trying something like:<BR>
<BR>
SELECT count(*)<BR>
FROM table1, table2<BR>
WHERE (table1.key100 != table2.key100);<BR>
<BR>
But the query is very slow and I finally just cancel it. Any help is very much appreciated.<BR>
<BR>
-Ken
</BODY>
</HTML>

--=-Y1cYpEHeuRYV0Vowj8Mt--




From fbax@sympatico.ca Wed Feb  8 21:27:23 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5D84C9DCABB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:27:23 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 57830-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:27:23 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from tomts40-srv.bellnexxia.net (tomts40.bellnexxia.net
	[209.226.175.97])
	by postgresql.org (Postfix) with ESMTP id 2DE289DC815
	for <pgsql-sql@postgresql.org>; Wed,  8 Feb 2006 17:27:19 -0400 (AST)
Received: from acer.sympatico.ca ([70.50.209.247])
	by tomts40-srv.bellnexxia.net
	(InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id
	<20060208212720.NJFA5216.tomts40-srv.bellnexxia.net@acer.sympatico.ca>
	for <pgsql-sql@postgresql.org>; Wed, 8 Feb 2006 16:27:20 -0500
Message-Id: <5.2.1.1.0.20060208162115.023e7ec0@pop6.sympatico.ca>
X-Sender: b1wudu74@pop6.sympatico.ca
X-Mailer: QUALCOMM Windows Eudora Version 5.2.1
Date: Wed, 08 Feb 2006 16:27:04 -0500
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: Non Matching Records in Two Tables
In-Reply-To: <1139433024.2044.48.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.84 required=5 tests=[AWL=-0.600,
	DNS_FROM_RFC_POST=1.44]
X-Spam-Score: 0.84
X-Spam-Level: 
X-Archive-Number: 200602/84
X-Sequence-Number: 24094

At 04:10 PM 2/8/06, Ken Hill wrote:
>I need some help with a bit of SQL. I have two tables. I want to find 
>records in one table that don't match records in another table based on a 
>common column in the two tables. Both tables have a column named 'key100'. 
>I was trying something like:
>
>SELECT count(*)
>FROM table1, table2
>WHERE (table1.key100 != table2.key100);
>
>But the query is very slow and I finally just cancel it. Any help is very 
>much appreciated.


vacuum analyse table1;

vacuum analyse table2;

select count(*) from table1 full outer join table2 on 
table1.key100=table2.key100 where table1.key100 is null or table2.key100 is 
null;

If this is also slow, post output of "EXPLAIN ANALYSE SELECT ...." 




From schabi@logix-tt.com Wed Feb  8 21:31:41 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 4B5C09DCABB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:31:41 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 55549-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:31:42 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.186])
	by postgresql.org (Postfix) with ESMTP id CF2939DC815
	for <pgsql-sql@postgresql.org>; Wed,  8 Feb 2006 17:31:38 -0400 (AST)
Received: from [84.157.3.179] (helo=lunix.schabi.de)
	by mrelayeu.kundenserver.de (node=mrelayeu7) with ESMTP (Nemesis),
	id 0ML2Dk-1F6wuT29pb-0000j1; Wed, 08 Feb 2006 22:31:34 +0100
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by lunix.schabi.de (Postfix) with ESMTP id 00B364DF54;
	Wed,  8 Feb 2006 22:31:31 +0100 (CET)
Message-ID: <43EA6333.7050604@logix-tt.com>
Date: Wed, 08 Feb 2006 22:31:31 +0100
From: Markus Schaber <schabi@logix-tt.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051017)
X-Accept-Language: de-DE, de, en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: ken@scottshill.com
Subject: Re: Non Matching Records in Two Tables
References: <1139433024.2044.48.camel@localhost.localdomain>
In-Reply-To: <1139433024.2044.48.camel@localhost.localdomain>
X-Enigmail-Version: 0.93.0.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Provags-ID: kundenserver.de abuse@kundenserver.de
	login:a9554655f07a4e401310f2acfb43f5bf
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200602/85
X-Sequence-Number: 24095

Hi, Ken,

Ken Hill schrieb:
> I need some help with a bit of SQL. I have two tables. I want to find
> records in one table that don't match records in another table based on
> a common column in the two tables. Both tables have a column named
> 'key100'. I was trying something like:
> 
> SELECT count(*)
> FROM table1, table2
> WHERE (table1.key100 != table2.key100);
> 
> But the query is very slow and I finally just cancel it. Any help is
> very much appreciated.

Do you have indices on the key100 columns? Is autovacuum running, or do
you do analyze manually?

Can you send us the output from "EXPLAIN ANALYZE [your query]"?

Btw, I don't think this query will do what you wanted, it basically
creates a cross product, that means if your tables look like:

schabitest=# select * from table1;
 key100 | valuea | valueb
--------+--------+--------
      1 | foo    | bar
      2 | blah   | blubb
      3 | manga  | mungo

schabitest=# select * from table2;
 key100 | valuec | valued
--------+--------+--------
      1 | monday | euro
      2 | sunday | dollar
      4 | friday | pounds

Then your query will produce something like:
schabitest=# select * from table1, table2 WHERE (table1.key100 !=
table2.key100);
 key100 | valuea | valueb | key100 | valuec | valued
--------+--------+--------+--------+--------+--------
      1 | foo    | bar    |      2 | sunday | dollar
      1 | foo    | bar    |      4 | friday | pounds
      2 | blah   | blubb  |      1 | monday | euro
      2 | blah   | blubb  |      4 | friday | pounds
      3 | manga  | mungo  |      1 | monday | euro
      3 | manga  | mungo  |      2 | sunday | dollar
      3 | manga  | mungo  |      4 | friday | pounds

I suggest you would like to have all records from table1 that don't have
a corresponding record in table2:

schabitest=# select * from table1 where table1.key100 not in (select
key100 from table2);
 key100 | valuea | valueb
--------+--------+--------
      3 | manga  | mungo

HTH,
Markus




From ojacobson@osl.com Wed Feb  8 21:31:51 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 27DF09DCC7B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:31:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 59393-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 17:31:52 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from nelson.osl.com (216-21-133-120.ip.van.radiant.net
	[216.21.133.120])
	by postgresql.org (Postfix) with ESMTP id 8033E9DCC70
	for <pgsql-sql@postgresql.org>; Wed,  8 Feb 2006 17:31:47 -0400 (AST)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: base64
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Subject: Re: Non Matching Records in Two Tables
Date: Wed, 8 Feb 2006 13:31:48 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC20CD3C@nelson.osl.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] Non Matching Records in Two Tables
Thread-Index: AcYs9MTbQ+73e5ltR0akPu3sxaD2cwAAP/1g
From: "Owen Jacobson" <ojacobson@osl.com>
To: <ken@scottshill.com>,
	<pgsql-sql@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.106 required=5 tests=[AWL=0.106]
X-Spam-Score: 0.106
X-Spam-Level: 
X-Archive-Number: 200602/86
X-Sequence-Number: 24096

S2VuIEhpbGwgd3JvdGU6DQoNCj4gSSBuZWVkIHNvbWUgaGVscCB3aXRoIGEgYml0IG9mIFNRTC4g
SSBoYXZlIHR3byB0YWJsZXMuIEkgd2FudA0KPiB0byBmaW5kIHJlY29yZHMgaW4gb25lIHRhYmxl
IHRoYXQgZG9uJ3QgbWF0Y2ggcmVjb3JkcyBpbiBhbm90aGVyDQo+IHRhYmxlIGJhc2VkIG9uIGEg
Y29tbW9uIGNvbHVtbiBpbiB0aGUgdHdvIHRhYmxlcy4gQm90aCB0YWJsZXMNCj4gaGF2ZSBhIGNv
bHVtbiBuYW1lZCAna2V5MTAwJy4gSSB3YXMgdHJ5aW5nIHNvbWV0aGluZyBsaWtlOg0KPiANCj4g
U0VMRUNUIGNvdW50KCopDQo+IEZST00gdGFibGUxLCB0YWJsZTINCj4gV0hFUkUgKHRhYmxlMS5r
ZXkxMDAgIT0gdGFibGUyLmtleTEwMCk7DQo+IA0KPiBCdXQgdGhlIHF1ZXJ5IGlzIHZlcnkgc2xv
dyBhbmQgSSBmaW5hbGx5IGp1c3QgY2FuY2VsIGl0LiBBbnkgaGVscA0KPiBpcyB2ZXJ5IG11Y2gg
YXBwcmVjaWF0ZWQuDQoNClRoYXQncyBhIGNhcnRlc2lhbiBqb2luLCB0aGVyZSwgYW5kIGl0J2xs
IGJlIGh1Z2UgKG9uIHRoZSBvcmRlciBvZiBOKk0gcm93cywgd2hlcmUgTiBhbmQgTSBhcmUgdGhl
IG51bWJlciBvZiByb3dzIGluIHRoZSBmaXJzdCBhbmQgc2Vjb25kIHRhYmxlcyByZXNwZWN0aXZl
bHkpLg0KDQpJdCBzb3VuZHMgbGlrZSwgZnJvbSB5b3VyIGRlc2NyaXB0aW9uLCB5b3Ugd2FudCB0
byBmaW5kIHJvd3MgaW4gdGFibGUxIHRoYXQgZG9uJ3QgaGF2ZSBhIGNvcnJlc3BvbmRpbmcgcm93
IGluIHRhYmxlMi4gIFRoaXMgc2hvdWxkIHdvcms6DQoNClNFTEVDVCBjb3VudCgqKQ0KICBGUk9N
IHRhYmxlMSBMRUZUIEpPSU4gdGFibGUyIE9OIHRhYmxlMS5rZXkxMDAgPSB0YWJsZTIua2V5MTAw
DQogIFdIRVJFIHRhYmxlMi5rZXkxMDAgSVMgTlVMTDsNCg0KVGhpcyB3aWxsIHN0aWxsIGJlIGZh
aXJseSBzbG93IHVubGVzcyB0aGVyZSBhcmUgaW5kZXhlcyBvbiB0YWJsZTEua2V5MTAwIGFuZCB0
YWJsZTIua2V5MTAwLCBidXQgbm93aGVyZSBuZWFyIGFzIHNsb3cgYXMgdGhlIG9yaWdpbmFsIHF1
ZXJ5Lg0KDQpGcmFuayBCYXgncyBzb2x1dGlvbiB3aWxsIHdvcmsgaWYgd2hhdCB5b3Ugd2FudCBp
cyBhIGNvdW50IG9mIHJvd3MgaW4gdGFibGUxIHRoYXQgZG9uJ3QgaGF2ZSBhIGNvcnJlc3BvbmRp
bmcgcm93IGluIHRhYmxlMiBvciBpbiB0YWJsZTIgdGhhdCBkb24ndCBoYXZlIGEgY29ycmVzcG9u
ZGluZyByb3cgaW4gdGFibGUxOyBmb3IgdGhhdCBzcGVjaWZpYyByZXF1aXJlbWVudCB5b3UgbWF5
IGFjdHVhbGx5IGJlIGJldHRlciBvZmYgZG9pbmcgdHdvIHF1ZXJpZXMgKG9uZSBmb3IgZWFjaCB0
YWJsZSkgYW5kIGFkZGluZyB0aGUgcmVzdWx0cyB0b2dldGhlci4NCg0KLU93ZW4NCg==



From ken@scottshill.com Wed Feb  8 22:18:24 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id D83F99DCC6E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 18:18:24 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 63726-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Feb 2006 18:18:26 -0400 (AST)
X-Greylist: delayed 00:05:02.177904 by SQLgrey-
Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [63.240.77.81])
	by postgresql.org (Postfix) with ESMTP id 4A9F99DCC18
	for <pgsql-sql@postgresql.org>; Wed,  8 Feb 2006 18:18:21 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (sccrmhc11) with SMTP
	id <2006020822132101100dis8fe>; Wed, 8 Feb 2006 22:13:21 +0000
Subject: Re: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Frank Bax <fbax@sympatico.ca>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <5.2.1.1.0.20060208162115.023e7ec0@pop6.sympatico.ca>
References: <5.2.1.1.0.20060208162115.023e7ec0@pop6.sympatico.ca>
Content-Type: multipart/alternative; boundary="=-+WegUPrsFTM2mrdN6g2i"
Organization: Scotts Hill Associates, Inc.
Date: Wed, 08 Feb 2006 14:13:42 -0800
Message-Id: <1139436823.11150.3.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.041 required=5 tests=[AWL=0.040,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.041
X-Spam-Level: 
X-Archive-Number: 200602/87
X-Sequence-Number: 24097


--=-+WegUPrsFTM2mrdN6g2i
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Wed, 2006-02-08 at 16:27 -0500, Frank Bax wrote:

> At 04:10 PM 2/8/06, Ken Hill wrote:
> >I need some help with a bit of SQL. I have two tables. I want to find 
> >records in one table that don't match records in another table based on a 
> >common column in the two tables. Both tables have a column named 'key100'. 
> >I was trying something like:
> >
> >SELECT count(*)
> >FROM table1, table2
> >WHERE (table1.key100 != table2.key100);
> >
> >But the query is very slow and I finally just cancel it. Any help is very 
> >much appreciated.
> 
> 
> vacuum analyse table1;
> 
> vacuum analyse table2;
> 
> select count(*) from table1 full outer join table2 on 
> table1.key100=table2.key100 where table1.key100 is null or table2.key100 is 
> null;
> 
> If this is also slow, post output of "EXPLAIN ANALYSE SELECT ...." 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly


OK. I added indexes on the two columns in the two tables:

CREATE INDEX key100_idex
	ON ncccr9 (key100);

CREATE INDEX key100_ncccr10_idex
	ON ncccr10 (key100);

Here is the analysis of the query:

csalgorithm=# EXPLAIN ANALYSE SELECT count(*)
csalgorithm-#   FROM ncccr9 LEFT JOIN ncccr10 ON ncccr9.key100 =
ncccr10.key100
csalgorithm-#   WHERE ncccr10.key100 IS NULL;
                                                               QUERY
PLAN 
-------------------------------------------------------------------------------- --------------------------------------------------------
 Aggregate  (cost=208337.59..208337.59 rows=1 width=0) (actual
time=255723.212.. 255723.214 rows=1 loops=1)
   ->  Hash Left Join  (cost=99523.55..207101.41 rows=494471 width=0)
(actual ti me=92326.635..255538.447 rows=38144 loops=1)
         Hash Cond: ("outer".key100 = "inner".key100)
         Filter: ("inner".key100 IS NULL)
         ->  Seq Scan on ncccr9  (cost=0.00..59360.71 rows=494471
width=104) (ac tual time=171.778..75099.734 rows=494471 loops=1)
         ->  Hash  (cost=88438.64..88438.64 rows=611564 width=104)
(actual time= 91962.956..91962.956 rows=0 loops=1)
               ->  Seq Scan on ncccr10  (cost=0.00..88438.64 rows=611564
width=1 04) (actual time=11.704..76519.323 rows=611564 loops=1)
 Total runtime: 255724.219 ms
(8 rows)

The result of 38,144 non-matching records seems too much:

csalgorithm=# SELECT count(*)
csalgorithm-#   FROM ncccr9 LEFT JOIN ncccr10 ON ncccr9.key100 =
ncccr10.key100
csalgorithm-#   WHERE ncccr10.key100 IS NULL;
 count
-------
 38144
(1 row)

Maybe I need to do a RIGHT JOIN to return the count of records in table
'ncccr10' that don't match records in 'ncccr9'? 

Thanks for your help. JOINS are fairly new to me.


--=-+WegUPrsFTM2mrdN6g2i
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Wed, 2006-02-08 at 16:27 -0500, Frank Bax wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">At 04:10 PM 2/8/06, Ken Hill wrote:</FONT>
<FONT COLOR="#000000">&gt;I need some help with a bit of SQL. I have two tables. I want to find </FONT>
<FONT COLOR="#000000">&gt;records in one table that don't match records in another table based on a </FONT>
<FONT COLOR="#000000">&gt;common column in the two tables. Both tables have a column named 'key100'. </FONT>
<FONT COLOR="#000000">&gt;I was trying something like:</FONT>
<FONT COLOR="#000000">&gt;</FONT>
<FONT COLOR="#000000">&gt;SELECT count(*)</FONT>
<FONT COLOR="#000000">&gt;FROM table1, table2</FONT>
<FONT COLOR="#000000">&gt;WHERE (table1.key100 != table2.key100);</FONT>
<FONT COLOR="#000000">&gt;</FONT>
<FONT COLOR="#000000">&gt;But the query is very slow and I finally just cancel it. Any help is very </FONT>
<FONT COLOR="#000000">&gt;much appreciated.</FONT>


<FONT COLOR="#000000">vacuum analyse table1;</FONT>

<FONT COLOR="#000000">vacuum analyse table2;</FONT>

<FONT COLOR="#000000">select count(*) from table1 full outer join table2 on </FONT>
<FONT COLOR="#000000">table1.key100=table2.key100 where table1.key100 is null or table2.key100 is </FONT>
<FONT COLOR="#000000">null;</FONT>

<FONT COLOR="#000000">If this is also slow, post output of &quot;EXPLAIN ANALYSE SELECT ....&quot; </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">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message can get through to the mailing list c</FONT>leanly
</PRE>
</BLOCKQUOTE>
<BR>
OK. I added indexes on the two columns in the two tables:<BR>
<BR>
CREATE INDEX key100_idex<BR>
	ON ncccr9 (key100);<BR>
<BR>
CREATE INDEX key100_ncccr10_idex<BR>
	ON ncccr10 (key100);<BR>
<BR>
Here is the analysis of the query:<BR>
<BR>
csalgorithm=# EXPLAIN ANALYSE SELECT count(*)<BR>
csalgorithm-#&nbsp;&nbsp; FROM ncccr9 LEFT JOIN ncccr10 ON ncccr9.key100 = ncccr10.key100<BR>
csalgorithm-#&nbsp;&nbsp; WHERE ncccr10.key100 IS NULL;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN <BR>
-------------------------------------------------------------------------------- --------------------------------------------------------<BR>
 Aggregate&nbsp; (cost=208337.59..208337.59 rows=1 width=0) (actual time=255723.212.. 255723.214 rows=1 loops=1)<BR>
&nbsp;&nbsp; -&gt;&nbsp; Hash Left Join&nbsp; (cost=99523.55..207101.41 rows=494471 width=0) (actual ti me=92326.635..255538.447 rows=38144 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Hash Cond: (&quot;outer&quot;.key100 = &quot;inner&quot;.key100)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (&quot;inner&quot;.key100 IS NULL)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on ncccr9&nbsp; (cost=0.00..59360.71 rows=494471 width=104) (ac tual time=171.778..75099.734 rows=494471 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Hash&nbsp; (cost=88438.64..88438.64 rows=611564 width=104) (actual time= 91962.956..91962.956 rows=0 loops=1)<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on ncccr10&nbsp; (cost=0.00..88438.64 rows=611564 width=1 04) (actual time=11.704..76519.323 rows=611564 loops=1)<BR>
 Total runtime: 255724.219 ms<BR>
(8 rows)<BR>
<BR>
The result of 38,144 non-matching records seems too much:<BR>
<BR>
csalgorithm=# SELECT count(*)<BR>
csalgorithm-#&nbsp;&nbsp; FROM ncccr9 LEFT JOIN ncccr10 ON ncccr9.key100 = ncccr10.key100<BR>
csalgorithm-#&nbsp;&nbsp; WHERE ncccr10.key100 IS NULL;<BR>
 count<BR>
-------<BR>
 38144<BR>
(1 row)<BR>
<BR>
Maybe I need to do a RIGHT JOIN to return the count of records in table 'ncccr10' that don't match records in 'ncccr9'? <BR>
<BR>
Thanks for your help. JOINS are fairly new to me.<BR>
<BR>
</BODY>
</HTML>

--=-+WegUPrsFTM2mrdN6g2i--




From patrick.jacquot@anpe.fr Thu Feb  9 09:00:03 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E228C9DCAA1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  9 Feb 2006 05:00:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 85986-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  9 Feb 2006 05:00:04 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp3.lc.anpe.fr (unknown [80.124.143.18])
	by postgresql.org (Postfix) with ESMTP id 9E07D9DC854
	for <pgsql-sql@postgresql.org>; Thu,  9 Feb 2006 05:00:01 -0400 (AST)
Received: from HOMERE03.anpe.fr (localhost.localdomain [127.0.0.1])
	by smtp3.lc.anpe.fr (antivirus) with ESMTP id D430973C91
	for <pgsql-sql@postgresql.org>; Thu,  9 Feb 2006 09:59:57 +0100 (CET)
Received: from [10.0.32.170] ([10.0.32.170]) by HOMERE03.anpe.fr with
	Microsoft SMTPSVC(6.0.3790.1830); Thu, 9 Feb 2006 09:59:36 +0100
Message-ID: <43EB048B.6050203@anpe.fr>
Date: Thu, 09 Feb 2006 09:59:55 +0100
From: Patrick JACQUOT <patrick.jacquot@anpe.fr>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Cc: pgsql-sql@postgresql.org
Subject: Re: Non Matching Records in Two Tables
References: <1139433024.2044.48.camel@localhost.localdomain>
In-Reply-To: <1139433024.2044.48.camel@localhost.localdomain>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 09 Feb 2006 08:59:36.0149 (UTC)
	FILETIME=[26B5C850:01C62D57]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.202 required=5 tests=[AWL=0.013,
	MISSING_HEADERS=0.189]
X-Spam-Score: 0.202
X-Spam-Level: 
X-Archive-Number: 200602/95
X-Sequence-Number: 24105

Ken Hill wrote:

> I need some help with a bit of SQL. I have two tables. I want to find 
> records in one table that don't match records in another table based 
> on a common column in the two tables. Both tables have a column named 
> 'key100'. I was trying something like:
>
> SELECT count(*)
> FROM table1, table2
> WHERE (table1.key100 != table2.key100);
>
> But the query is very slow and I finally just cancel it. Any help is 
> very much appreciated.
>
> -Ken 

Maybe you could use a NOT EXISTS subquery, as in
SELECT count(*) from table1
WHERE NOT EXISTS(SELECT count(*) from table2 WHERE table1.key100 
=table2.key100)
which gives you the number of records in table1 without corresponding 
records in table2.
That kind of query is quite fast, if there exists an index on table2.key100
hth
P.Jacquot



From bigsmoke@gmail.com Thu Feb  9 16:16:33 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id C42859DC87C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  9 Feb 2006 12:16:33 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 69825-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  9 Feb 2006 12:16:31 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id 746389DC81C
	for <pgsql-sql@postgresql.org>; Thu,  9 Feb 2006 12:16:29 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 23D103093D; Thu,  9 Feb 2006 17:16:28 +0100 (MET)
From: "BigSmoke" <bigsmoke@gmail.com>
X-Newsgroups: pgsql.sql
Subject: Re: Non Matching Records in Two Tables
Date: 9 Feb 2006 08:13:59 -0800
Organization: http://groups.google.com
Lines: 2
Message-ID: <1139501639.531245.320190@o13g2000cwo.googlegroups.com>
References: <1139433024.2044.48.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Complaints-To: groups-abuse@google.com
In-Reply-To: <1139433024.2044.48.camel@localhost.localdomain>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.8.0.1) Gecko/20060111
	Firefox/1.5.0.1,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: o13g2000cwo.googlegroups.com; posting-host=212.204.130.113;
	posting-account=0WN9Mw0AAAChM4mno36wWr-RrABvnbDI
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200602/101
X-Sequence-Number: 24111

You can use an EXCEPT clause.




From ken@scottshill.com Tue Feb  14 20:54:38 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 932E19DC816
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 16:54:38 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 63523-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 16:54:38 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [63.240.77.81])
	by postgresql.org (Postfix) with ESMTP id E881F9DCA4B
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 16:54:34 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (sccrmhc11) with SMTP
	id <20060214205429011000j06fe>; Tue, 14 Feb 2006 20:54:35 +0000
Subject: Re: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <43EA6333.7050604@logix-tt.com>
References: <1139433024.2044.48.camel@localhost.localdomain>
	<43EA6333.7050604@logix-tt.com>
Content-Type: multipart/alternative; boundary="=-ZND5REpWqHWLgeoFpCKD"
Organization: Scotts Hill Associates, Inc.
Date: Tue, 14 Feb 2006 12:55:07 -0800
Message-Id: <1139950507.3083.64.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.083 required=5 tests=[AWL=0.082,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.083
X-Spam-Level: 
X-Archive-Number: 200602/151
X-Sequence-Number: 24161


--=-ZND5REpWqHWLgeoFpCKD
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Wed, 2006-02-08 at 22:31 +0100, Markus Schaber wrote:

> Hi, Ken,
> 
> Ken Hill schrieb:
> > I need some help with a bit of SQL. I have two tables. I want to find
> > records in one table that don't match records in another table based on
> > a common column in the two tables. Both tables have a column named
> > 'key100'. I was trying something like:
> > 
> > SELECT count(*)
> > FROM table1, table2
> > WHERE (table1.key100 != table2.key100);
> > 
> > But the query is very slow and I finally just cancel it. Any help is
> > very much appreciated.
> 
> Do you have indices on the key100 columns? Is autovacuum running, or do
> you do analyze manually?
> 
> Can you send us the output from "EXPLAIN ANALYZE [your query]"?
> 
> Btw, I don't think this query will do what you wanted, it basically
> creates a cross product, that means if your tables look like:
> 
> schabitest=# select * from table1;
>  key100 | valuea | valueb
> --------+--------+--------
>       1 | foo    | bar
>       2 | blah   | blubb
>       3 | manga  | mungo
> 
> schabitest=# select * from table2;
>  key100 | valuec | valued
> --------+--------+--------
>       1 | monday | euro
>       2 | sunday | dollar
>       4 | friday | pounds
> 
> Then your query will produce something like:
> schabitest=# select * from table1, table2 WHERE (table1.key100 !=
> table2.key100);
>  key100 | valuea | valueb | key100 | valuec | valued
> --------+--------+--------+--------+--------+--------
>       1 | foo    | bar    |      2 | sunday | dollar
>       1 | foo    | bar    |      4 | friday | pounds
>       2 | blah   | blubb  |      1 | monday | euro
>       2 | blah   | blubb  |      4 | friday | pounds
>       3 | manga  | mungo  |      1 | monday | euro
>       3 | manga  | mungo  |      2 | sunday | dollar
>       3 | manga  | mungo  |      4 | friday | pounds
> 
> I suggest you would like to have all records from table1 that don't have
> a corresponding record in table2:
> 
> schabitest=# select * from table1 where table1.key100 not in (select
> key100 from table2);
>  key100 | valuea | valueb
> --------+--------+--------
>       3 | manga  | mungo
> 
> HTH,
> Markus
> 

Here is my query SQL:

SELECT key100 FROM ncccr10
WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);

It is is running after 30 minutes. Here is the query plan:

                               QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on ncccr10  (cost=0.00..20417160510.08 rows=305782 width=104)
   Filter: (NOT (subplan))
   SubPlan
     ->  Seq Scan on ncccr9  (cost=0.00..65533.71 rows=494471 width=104)
(4 rows)

Any ideas why it is so slow?

--=-ZND5REpWqHWLgeoFpCKD
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Wed, 2006-02-08 at 22:31 +0100, Markus Schaber wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">Hi, Ken,</FONT>

<FONT COLOR="#000000">Ken Hill schrieb:</FONT>
<FONT COLOR="#000000">&gt; I need some help with a bit of SQL. I have two tables. I want to find</FONT>
<FONT COLOR="#000000">&gt; records in one table that don't match records in another table based on</FONT>
<FONT COLOR="#000000">&gt; a common column in the two tables. Both tables have a column named</FONT>
<FONT COLOR="#000000">&gt; 'key100'. I was trying something like:</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; SELECT count(*)</FONT>
<FONT COLOR="#000000">&gt; FROM table1, table2</FONT>
<FONT COLOR="#000000">&gt; WHERE (table1.key100 != table2.key100);</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; But the query is very slow and I finally just cancel it. Any help is</FONT>
<FONT COLOR="#000000">&gt; very much appreciated.</FONT>

<FONT COLOR="#000000">Do you have indices on the key100 columns? Is autovacuum running, or do</FONT>
<FONT COLOR="#000000">you do analyze manually?</FONT>

<FONT COLOR="#000000">Can you send us the output from &quot;EXPLAIN ANALYZE [your query]&quot;?</FONT>

<FONT COLOR="#000000">Btw, I don't think this query will do what you wanted, it basically</FONT>
<FONT COLOR="#000000">creates a cross product, that means if your tables look like:</FONT>

<FONT COLOR="#000000">schabitest=# select * from table1;</FONT>
<FONT COLOR="#000000"> key100 | valuea | valueb</FONT>
<FONT COLOR="#000000">--------+--------+--------</FONT>
<FONT COLOR="#000000">      1 | foo    | bar</FONT>
<FONT COLOR="#000000">      2 | blah   | blubb</FONT>
<FONT COLOR="#000000">      3 | manga  | mungo</FONT>

<FONT COLOR="#000000">schabitest=# select * from table2;</FONT>
<FONT COLOR="#000000"> key100 | valuec | valued</FONT>
<FONT COLOR="#000000">--------+--------+--------</FONT>
<FONT COLOR="#000000">      1 | monday | euro</FONT>
<FONT COLOR="#000000">      2 | sunday | dollar</FONT>
<FONT COLOR="#000000">      4 | friday | pounds</FONT>

<FONT COLOR="#000000">Then your query will produce something like:</FONT>
<FONT COLOR="#000000">schabitest=# select * from table1, table2 WHERE (table1.key100 !=</FONT>
<FONT COLOR="#000000">table2.key100);</FONT>
<FONT COLOR="#000000"> key100 | valuea | valueb | key100 | valuec | valued</FONT>
<FONT COLOR="#000000">--------+--------+--------+--------+--------+--------</FONT>
<FONT COLOR="#000000">      1 | foo    | bar    |      2 | sunday | dollar</FONT>
<FONT COLOR="#000000">      1 | foo    | bar    |      4 | friday | pounds</FONT>
<FONT COLOR="#000000">      2 | blah   | blubb  |      1 | monday | euro</FONT>
<FONT COLOR="#000000">      2 | blah   | blubb  |      4 | friday | pounds</FONT>
<FONT COLOR="#000000">      3 | manga  | mungo  |      1 | monday | euro</FONT>
<FONT COLOR="#000000">      3 | manga  | mungo  |      2 | sunday | dollar</FONT>
<FONT COLOR="#000000">      3 | manga  | mungo  |      4 | friday | pounds</FONT>

<FONT COLOR="#000000">I suggest you would like to have all records from table1 that don't have</FONT>
<FONT COLOR="#000000">a corresponding record in table2:</FONT>

<FONT COLOR="#000000">schabitest=# select * from table1 where table1.key100 not in (select</FONT>
<FONT COLOR="#000000">key100 from table2);</FONT>
<FONT COLOR="#000000"> key100 | valuea | valueb</FONT>
<FONT COLOR="#000000">--------+--------+--------</FONT>
<FONT COLOR="#000000">      3 | manga  | mungo</FONT>

<FONT COLOR="#000000">HTH,</FONT>
<FONT COLOR="#000000">Markus</FONT>

</PRE>
</BLOCKQUOTE>
Here is my query SQL:<BR>
<BR>
SELECT key100 FROM ncccr10<BR>
WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);<BR>
<BR>
It is is running after 30 minutes. Here is the query plan:<BR>
<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN<BR>
-------------------------------------------------------------------------<BR>
 Seq Scan on ncccr10&nbsp; (cost=0.00..20417160510.08 rows=305782 width=104)<BR>
&nbsp;&nbsp; Filter: (NOT (subplan))<BR>
&nbsp;&nbsp; SubPlan<BR>
&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on ncccr9&nbsp; (cost=0.00..65533.71 rows=494471 width=104)<BR>
(4 rows)<BR>
<BR>
Any ideas why it is so slow?
</BODY>
</HTML>

--=-ZND5REpWqHWLgeoFpCKD--




From tgl@sss.pgh.pa.us Tue Feb  14 21:07:32 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 9F0A59DC81F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:07:32 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 64045-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:07:33 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 543039DC804
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 17:07:29 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1EL7TJ4022659;
	Tue, 14 Feb 2006 16:07:29 -0500 (EST)
To: ken@scottshill.com
cc: Markus Schaber <schabi@logix-tt.com>, pgsql-sql@postgresql.org
Subject: Re: Non Matching Records in Two Tables 
In-reply-to: <1139950507.3083.64.camel@localhost.localdomain> 
References: <1139433024.2044.48.camel@localhost.localdomain>
	<43EA6333.7050604@logix-tt.com>
	<1139950507.3083.64.camel@localhost.localdomain>
Comments: In-reply-to Ken Hill <ken@scottshill.com>
	message dated "Tue, 14 Feb 2006 12:55:07 -0800"
Date: Tue, 14 Feb 2006 16:07:29 -0500
Message-ID: <22658.1139951249@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109]
X-Spam-Score: 0.109
X-Spam-Level: 
X-Archive-Number: 200602/152
X-Sequence-Number: 24162

Ken Hill <ken@scottshill.com> writes:
>  Seq Scan on ncccr10  (cost=0.00..20417160510.08 rows=305782 width=104)
>    Filter: (NOT (subplan))
>    SubPlan
>      ->  Seq Scan on ncccr9  (cost=0.00..65533.71 rows=494471 width=104)
> (4 rows)

> Any ideas why it is so slow?

"NOT (subplan)" is horrendous (and the system knows it, note the huge
cost estimate).  Try increasing work_mem enough so you get a hashed
subplan instead.

			regards, tom lane



From chestercyoung@yahoo.com Tue Feb  14 21:08:32 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id BE15D9DC816
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:08:32 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 63074-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:08:33 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from web54302.mail.yahoo.com (web54302.mail.yahoo.com
	[206.190.49.112])
	by postgresql.org (Postfix) with SMTP id 3F2979DC804
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 17:08:28 -0400 (AST)
Received: (qmail 79697 invoked by uid 60001); 14 Feb 2006 21:08:29 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=y6gylixj6llCoO33hPrH+orNyqUN1FUW0tLDcwSnXHEKSr07na/7AkEwGB76hR1UbNNqJ5O0gBhyyreeOEnDbzy0N9fAjIN9tuf/+crpiEQFzOhL+HfdbQZUXi8xCleLCoEaoJWH84EMHCabqpVJ0oHx3biRK45phaeTGF3ezfc=
	;
Message-ID: <20060214210829.79695.qmail@web54302.mail.yahoo.com>
Received: from [68.54.4.83] by web54302.mail.yahoo.com via HTTP;
	Tue, 14 Feb 2006 13:08:29 PST
Date: Tue, 14 Feb 2006 13:08:29 -0800 (PST)
From: chester c young <chestercyoung@yahoo.com>
Subject: Re: Non Matching Records in Two Tables
To: ken@scottshill.com, pgsql-sql@postgresql.org
In-Reply-To: <1139950507.3083.64.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.479
X-Spam-Level: 
X-Archive-Number: 200602/153
X-Sequence-Number: 24163


> Here is my query SQL:
> 
> SELECT key100 FROM ncccr10
> WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);
> 
> It is is running after 30 minutes. Here is the query plan:
>

I would try an outer join:

select a.key100
from ncccr10 a
left join ncccr9 b on( key100 )
where b.key100 is null;

also (hate to be obvious) have you analyzed lately?


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 



From ken@scottshill.com Tue Feb  14 21:24:18 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 679889DC816
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:24:18 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 70134-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:24:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [216.148.227.153])
	by postgresql.org (Postfix) with ESMTP id 10B489DC859
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 17:24:15 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc13) with SMTP
	id <20060214212417m1300fio7ae>; Tue, 14 Feb 2006 21:24:17 +0000
Subject: Re: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20060214210829.79695.qmail@web54302.mail.yahoo.com>
References: <20060214210829.79695.qmail@web54302.mail.yahoo.com>
Content-Type: multipart/alternative; boundary="=-6wbF/50cwR65Sh0tTIfZ"
Organization: Scotts Hill Associates, Inc.
Date: Tue, 14 Feb 2006 13:24:54 -0800
Message-Id: <1139952295.3083.67.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.078 required=5 tests=[AWL=0.077,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.078
X-Spam-Level: 
X-Archive-Number: 200602/155
X-Sequence-Number: 24165


--=-6wbF/50cwR65Sh0tTIfZ
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote:

> > Here is my query SQL:
> > 
> > SELECT key100 FROM ncccr10
> > WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);
> > 
> > It is is running after 30 minutes. Here is the query plan:
> >
> 
> I would try an outer join:
> 
> select a.key100
> from ncccr10 a
> left join ncccr9 b on( key100 )
> where b.key100 is null;
> 
> also (hate to be obvious) have you analyzed lately?
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

Yes, I did a VACUUM ANALYZE on the entire database before running this
query. Also, I get an error with your suggestion:

csalgorithm=# SELECT a.key100 FROM ncccr10 a
csalgorithm-# LEFT JOIN ncccr9 b ON(a.key100)
csalgorithm-# WHERE b.key100 IS Null;
ERROR:  argument of JOIN/ON must be type boolean, not type character


--=-6wbF/50cwR65Sh0tTIfZ
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">&gt; Here is my query SQL:</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; SELECT key100 FROM ncccr10</FONT>
<FONT COLOR="#000000">&gt; WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; It is is running after 30 minutes. Here is the query plan:</FONT>
<FONT COLOR="#000000">&gt;</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">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message can get through to the mailing list c</FONT>leanly
</PRE>
</BLOCKQUOTE>
Yes, I did a VACUUM ANALYZE on the entire database before running this query. Also, I get an error with your suggestion:<BR>
<BR>
csalgorithm=# SELECT a.key100 FROM ncccr10 a<BR>
csalgorithm-# LEFT JOIN ncccr9 b ON(a.key100)<BR>
csalgorithm-# WHERE b.key100 IS Null;<BR>
ERROR:&nbsp; argument of JOIN/ON must be type boolean, not type character<BR>
<BR>
</BODY>
</HTML>

--=-6wbF/50cwR65Sh0tTIfZ--




From ken@scottshill.com Tue Feb  14 21:25:05 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 5DA469DCA08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:25:05 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 70731-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 17:25:06 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.192.83])
	by postgresql.org (Postfix) with ESMTP id 0AB069DC9AC
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 17:25:02 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc13) with SMTP
	id <20060214212458m1300fifb5e>; Tue, 14 Feb 2006 21:25:04 +0000
Subject: Re: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Markus Schaber <schabi@logix-tt.com>, pgsql-sql@postgresql.org
In-Reply-To: <22658.1139951249@sss.pgh.pa.us>
References: <1139433024.2044.48.camel@localhost.localdomain>
	<43EA6333.7050604@logix-tt.com>
	<1139950507.3083.64.camel@localhost.localdomain>
	<22658.1139951249@sss.pgh.pa.us>
Content-Type: multipart/alternative; boundary="=-zbwszlVC6qIttb9BFoGE"
Organization: Scotts Hill Associates, Inc.
Date: Tue, 14 Feb 2006 13:25:36 -0800
Message-Id: <1139952337.3083.68.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.081 required=5 tests=[AWL=0.080,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.081
X-Spam-Level: 
X-Archive-Number: 200602/156
X-Sequence-Number: 24166


--=-zbwszlVC6qIttb9BFoGE
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2006-02-14 at 16:07 -0500, Tom Lane wrote:

> Ken Hill <ken@scottshill.com> writes:
> >  Seq Scan on ncccr10  (cost=0.00..20417160510.08 rows=305782 width=104)
> >    Filter: (NOT (subplan))
> >    SubPlan
> >      ->  Seq Scan on ncccr9  (cost=0.00..65533.71 rows=494471 width=104)
> > (4 rows)
> 
> > Any ideas why it is so slow?
> 
> "NOT (subplan)" is horrendous (and the system knows it, note the huge
> cost estimate).  Try increasing work_mem enough so you get a hashed
> subplan instead.
> 
> 			regards, tom lane


How do I ncrease work_mem?

--=-zbwszlVC6qIttb9BFoGE
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Tue, 2006-02-14 at 16:07 -0500, Tom Lane wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">Ken Hill &lt;<A HREF="mailto:ken@scottshill.com">ken@scottshill.com</A>&gt; writes:</FONT>
<FONT COLOR="#000000">&gt;  Seq Scan on ncccr10  (cost=0.00..20417160510.08 rows=305782 width=104)</FONT>
<FONT COLOR="#000000">&gt;    Filter: (NOT (subplan))</FONT>
<FONT COLOR="#000000">&gt;    SubPlan</FONT>
<FONT COLOR="#000000">&gt;      -&gt;  Seq Scan on ncccr9  (cost=0.00..65533.71 rows=494471 width=104)</FONT>
<FONT COLOR="#000000">&gt; (4 rows)</FONT>

<FONT COLOR="#000000">&gt; Any ideas why it is so slow?</FONT>

<FONT COLOR="#000000">&quot;NOT (subplan)&quot; is horrendous (and the system knows it, note the huge</FONT>
<FONT COLOR="#000000">cost estimate).  Try increasing work_mem enough so you get a hashed</FONT>
<FONT COLOR="#000000">subplan instead.</FONT>

<FONT COLOR="#000000">			regards, tom lane</FONT>
</PRE>
</BLOCKQUOTE>
<BR>
How do I ncrease work_mem?
</BODY>
</HTML>

--=-zbwszlVC6qIttb9BFoGE--




From bryce1@obviously.com Wed Feb  15 06:58:45 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id DA9729DC98B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Feb 2006 02:58:45 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36921-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Feb 2006 02:58:45 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from outbound.internet-mail-service.net
	(outbound6.internet-mail-service.net [216.240.47.197])
	by postgresql.org (Postfix) with ESMTP id EBA779DC8DF
	for <pgsql-sql@postgresql.org>; Wed, 15 Feb 2006 02:58:43 -0400 (AST)
Received: from idiom.com (idiom.com [216.240.32.1])
	by outbound.internet-mail-service.net (Postfix) with ESMTP id
	02E46240FD8
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 22:58:44 -0800 (PST)
Received: from [192.168.1.109] (adsl-69-107-112-198.dsl.pltn13.pacbell.net
	[69.107.112.198])
	by idiom.com (8.12.11/8.12.11) with ESMTP id k1F6whkU012950
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 22:58:43 -0800 (PST)
	(envelope-from bryce1@obviously.com)
Message-ID: <43F26226.1050203@obviously.com>
Date: Tue, 14 Feb 2006 15:05:10 -0800
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Non Matching Records in Two Tables
References: <20060214210829.79695.qmail@web54302.mail.yahoo.com>
	<1139952295.3083.67.camel@localhost.localdomain>
In-Reply-To: <1139952295.3083.67.camel@localhost.localdomain>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.487 required=5 tests=[AWL=-0.259,
	DATE_IN_PAST_06_12=0.746]
X-Spam-Score: 0.487
X-Spam-Level: 
X-Archive-Number: 200602/163
X-Sequence-Number: 24173

Ken Hill wrote:
>> also (hate to be obvious) have you analyzed lately?
>>     
I'd say that's fair game, not obvious.  Vacuum/Analyze is ar so aparent
to a person moving
to Postgres from other DB's.




From ken@scottshill.com Wed Feb  15 01:46:27 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 51A419DC81F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 21:46:27 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 21125-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 21:46:30 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.152])
	by postgresql.org (Postfix) with ESMTP id 14D269DC80F
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 21:46:24 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc12) with SMTP
	id <20060215014628m1200ql651e>; Wed, 15 Feb 2006 01:46:28 +0000
Subject: Re: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1139952295.3083.67.camel@localhost.localdomain>
References: <20060214210829.79695.qmail@web54302.mail.yahoo.com>
	<1139952295.3083.67.camel@localhost.localdomain>
Content-Type: multipart/alternative; boundary="=-+NCWi5YNN8eSN6ZCxYi2"
Organization: Scotts Hill Associates, Inc.
Date: Tue, 14 Feb 2006 17:47:02 -0800
Message-Id: <1139968023.25060.0.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.086 required=5 tests=[AWL=0.085,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.086
X-Spam-Level: 
X-Archive-Number: 200602/162
X-Sequence-Number: 24172


--=-+NCWi5YNN8eSN6ZCxYi2
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2006-02-14 at 13:24 -0800, Ken Hill wrote:

> On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote: 
> 
> > > Here is my query SQL:
> > > 
> > > SELECT key100 FROM ncccr10
> > > WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);
> > > 
> > > It is is running after 30 minutes. Here is the query plan:
> > >
> > 
> > I would try an outer join:
> > 
> > select a.key100
> > from ncccr10 a
> > left join ncccr9 b on( key100 )
> > where b.key100 is null;
> > 
> > also (hate to be obvious) have you analyzed lately?
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around 
> > http://mail.yahoo.com 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
> 
> Yes, I did a VACUUM ANALYZE on the entire database before running this
> query. Also, I get an error with your suggestion:
> 
> csalgorithm=# SELECT a.key100 FROM ncccr10 a
> csalgorithm-# LEFT JOIN ncccr9 b ON(a.key100)
> csalgorithm-# WHERE b.key100 IS Null;
> ERROR:  argument of JOIN/ON must be type boolean, not type character
> 

Well, this works:

SELECT *
FROM ncccr9 a
LEFT JOIN ncccr10 b USING( key100 )
WHERE b.key100 is null;

It still seems slow. It takes about the same time to run as in MS
Access. I thought PostgreSQL would be faster.

--=-+NCWi5YNN8eSN6ZCxYi2
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Tue, 2006-02-14 at 13:24 -0800, Ken Hill wrote:<BR>
<BLOCKQUOTE TYPE=CITE>
    <FONT COLOR="#000000">On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote: </FONT>
    <BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">&gt; Here is my query SQL:</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; SELECT key100 FROM ncccr10</FONT>
<FONT COLOR="#000000">&gt; WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; It is is running after 30 minutes. Here is the query plan:</FONT>
<FONT COLOR="#000000">&gt;</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">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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 ncccr10 a</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:&nbsp; argument of JOIN/ON must be type boolean, not type character</FONT><BR>
    <BR>
</BLOCKQUOTE>
Well, this works:<BR>
<BR>
SELECT *<BR>
FROM ncccr9 a<BR>
LEFT JOIN ncccr10 b USING( key100 )<BR>
WHERE b.key100 is null;<BR>
<BR>
It still seems slow. It takes about the same time to run as in MS Access. I thought PostgreSQL would be faster.
</BODY>
</HTML>

--=-+NCWi5YNN8eSN6ZCxYi2--




From ken@scottshill.com Wed Feb  15 15:45:39 2006
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 401679DCA0F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Feb 2006 11:45:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 48917-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Feb 2006 11:45:43 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.192.83])
	by postgresql.org (Postfix) with ESMTP id B03059DC827
	for <pgsql-sql@postgresql.org>; Wed, 15 Feb 2006 11:45:36 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc13) with SMTP
	id <20060215154540m1300fkqrqe>; Wed, 15 Feb 2006 15:45:40 +0000
Subject: Re: Non Matching Records in Two Tables
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <43F26226.1050203@obviously.com>
References: <20060214210829.79695.qmail@web54302.mail.yahoo.com>
	<1139952295.3083.67.camel@localhost.localdomain>
	<43F26226.1050203@obviously.com>
Content-Type: multipart/alternative; boundary="=-MrU0WLvKqU2luFYcKi9H"
Organization: Scotts Hill Associates, Inc.
Date: Wed, 15 Feb 2006 07:45:41 -0800
Message-Id: <1140018341.3870.3.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.088 required=5 tests=[AWL=0.087,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.088
X-Spam-Level: 
X-Archive-Number: 200602/164
X-Sequence-Number: 24174


--=-MrU0WLvKqU2luFYcKi9H
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Tue, 2006-02-14 at 15:05 -0800, Bryce Nesbitt wrote:

> Ken Hill wrote:
> >> also (hate to be obvious) have you analyzed lately?
> >>     
> I'd say that's fair game, not obvious.  Vacuum/Analyze is ar so aparent
> to a person moving
> to Postgres from other DB's.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org

In my O'Reily "Practical PostgreSQL" book it recommends vacuuming a
production database on a daily basis. I liken this to MS Access'
"compact/repair" procedure.

--=-MrU0WLvKqU2luFYcKi9H
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Tue, 2006-02-14 at 15:05 -0800, Bryce Nesbitt wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">Ken Hill wrote:</FONT>
<FONT COLOR="#000000">&gt;&gt; also (hate to be obvious) have you analyzed lately?</FONT>
<FONT COLOR="#000000">&gt;&gt;     </FONT>
<FONT COLOR="#000000">I'd say that's fair game, not obvious.  Vacuum/Analyze is ar so aparent</FONT>
<FONT COLOR="#000000">to a person moving</FONT>
<FONT COLOR="#000000">to Postgres from other DB's.</FONT>


<FONT COLOR="#000000">---------------------------(end of broadcast)---------------------------</FONT>
<FONT COLOR="#000000">TIP 4: Have you searched our list archives?</FONT>

<FONT COLOR="#000000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <A HREF="http://archives.postgresql.org">http://archives.</FONT>postgresql.org</A>
</PRE>
</BLOCKQUOTE>
In my O'Reily &quot;Practical PostgreSQL&quot; book it recommends vacuuming a production database on a daily basis. I liken this to MS Access' &quot;compact/repair&quot; procedure.
</BODY>
</HTML>

--=-MrU0WLvKqU2luFYcKi9H--