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 ; 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 ; 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 ; 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 Reply-To: ken@scottshill.com To: Markus Schaber 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 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--