Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA28284 for ; Mon, 26 Jul 1999 07:11:59 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id EB22C4B57A; Mon, 26 Jul 1999 14:11:27 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA77E; Mon, 26 Jul 1999 14:14:44 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <379C25B3.A86C31F8@thinx.ch> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 26 Jul 1999 14:12:02 +0300 To: Christian Rudow , PostgreSQL SQL From: Herouth Maoz Subject: Re: [SQL] NOT IN clause performing badly At 12:09 +0300 on 26/07/1999, Christian Rudow wrote: > 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 ? NOT IN is an inefficient way of running such a query. I would suggest: - After creating tempx1, create an index on its id field - Use the following instead of NOT IN: select id from mytable where NOT EXISTS ( select * from tempx1 where tempx1.id = mytable.id ); What is the difference? In the NOT IN version, for each of the 10000 ids in mytable, it has to search through 8000 records to see whether the current mytable.id is there or not. This would give you 80000000 operations. Especially since there is no index on tempx1. The EXISTS query I gave uses the index on tempx1.id for each of the 10000 ids. Thus, you have 10000 * log 8000, more or less. But you have to count in the penalty of creating the index on tempx1.id in the first place. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma