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