Received: from irbrugg.net.thinx.ch (metron.agrinet.ch [212.28.128.145]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA03931 for ; Mon, 26 Jul 1999 05:08:38 -0400 (EDT) (envelope-from Christian.Rudow@thinx.ch) Received: from thinx.ch (chris@acer.net.thinx.ch [192.168.0.33]) by irbrugg.net.thinx.ch (8.8.8/8.8.8) with ESMTP id IAA04640 for ; Mon, 26 Jul 1999 08:56:46 +0200 Sender: chris@thinx.ch Message-ID: <379C25B3.A86C31F8@thinx.ch> Date: Mon, 26 Jul 1999 11:09:07 +0200 From: Christian Rudow Organization: ThinX X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgreSQL SQL Subject: NOT IN clause performing badly Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a problem with NOT IN queries ------------------------------------ Has anyone experinced the same problem, or even found a solution to it ? -- simple NOT IN clause does not perform -- create a table with an integer primary key and any number and type of attributes. -- load approx. 10'000 records and vacuum; -- then select a subset of say 8000 id's into a seperate table. -- now try to find out the Id's of the remaining 2000 records -- I would use a NOT IN clause -- but this is what happens : drop table tempx1; select mytable.id into table tempx1 from mytable, othertable where mytable.id = othertable.id and othertable.someattribute = "Somevalue"; -- this will take only a few seconds -- on platform : PG6.4 SusE 6.0 PII/300mhz -- rdbms startup : su - postgres -c with parameters : -- "/sbin/startproc -l $LOGFILE $H -B 256 -i -o -F -D$DATADIR" -- select count(*) from tempx1 : now returns 8000 rows select id from mytable where id NOT IN ( select id from tempx1 ); -- this will take some 100 minutes at least !!! -- but it does finish ! no crash or hangup. -- NOTICE: QUERY PLAN: -- Seq Scan on mytable (cost=958.89 size=10000 width=4) -- SubPlan -- -> Seq Scan on tempx1 (cost=0.00 size=0 width=4) -- -- an index on mytbale(id) exists. Questions --------- Is this a known problem ? What is causing the problem ? Is it a problem of RDBMS parameters ? Is there an alternative to the NOT IN clause ? I'd appreciate any help in this matter. ThanX Chris -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Christian Rudow E-Mail: Christian.Rudow@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~