Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 16A961337B8A for ; Fri, 7 Jan 2011 00:25:54 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 42015-05 for ; Fri, 7 Jan 2011 04:25:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from hrndva-omtalb.mail.rr.com (hrndva-omtalb.mail.rr.com [71.74.56.123]) by mail.postgresql.org (Postfix) with ESMTP id B2FD51337B91 for ; Fri, 7 Jan 2011 00:25:46 -0400 (AST) X-Authority-Analysis: v=1.1 cv=3uSaImBeuprzHBlOOPjkqgu+7PcxSRW0m2Aphm9Zmck= c=1 sm=0 a=mNzQLtHBZTYA:10 a=E_FQspuQcigA:10 a=RAii6je2cdnAH6ATCOpc6g==:17 a=_Ixf_7qHAAAA:8 a=lOKZkmV5dv9iCSKdGqkA:9 a=T9H-O9CbOfWKmXAyJI0A:7 a=A8qvlg3K7bG2e2_A8JANEvw0ZwkA:4 a=o3X3QV6JOaEA:10 a=ryAo9hy1-OkA:10 a=6vsjEBoLwlAA:10 a=0tfO7MgyNwYA:10 a=RAii6je2cdnAH6ATCOpc6g==:117 X-Cloudmark-Score: 0 X-Originating-IP: 74.73.87.196 Received: from [74.73.87.196] ([74.73.87.196:58959] helo=[192.168.1.103]) by hrndva-oedge04.mail.rr.com (envelope-from ) (ecelerity 2.2.3.46 r()) with ESMTP id DA/77-04612-9C5962D4; Fri, 07 Jan 2011 04:25:46 +0000 Message-ID: <4D2695C9.10204@vmsinfo.com> Date: Thu, 06 Jan 2011 23:25:45 -0500 From: Mladen Gogala User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7 MIME-Version: 1.0 To: =?ISO-8859-7?Q?=C3=E9=F9=F1=E3=EF=F2_=C2=E1=EB=EA=E1=ED=E1=F2?= CC: "pgsql-performance@postgresql.org" Subject: Re: "SELECT .. WHERE NOT IN" query running for hours References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-7; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201101/34 X-Sequence-Number: 41821 On 1/6/2011 9:36 PM, Γιωργος Βαλκανας wrote: > > 1) Why is it taking *so* long for the first query (with the "NOT IN" ) > to do even the simple select? Because NOT IN has to execute the correlated subquery for every row and then check whether the requested value is in the result set, usually by doing sequential comparison. The NOT EXIST plan is also bad because there is no index but at least it can use very fast and efficient hash algorithm. Indexing the "hwdocid" column on the "Document" table or, ideally, making it a primary key, should provide an additional boost to your query. If you already do have an index, you may consider using enable_seqscan=false for this session, so that the "hwdocid" index will be used. It's a common wisdom that in the most cases NOT EXISTS will beat NOT IN. That is so all over the database world. I've seen that in Oracle applications, MS SQL applications and, of course MySQL applications. Optimizing queries is far from trivial. Μλαδεν Γογαλα -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com