Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8EAE31337B39 for ; Sat, 6 Nov 2010 12:59:56 -0300 (ADT) 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 08869-10 for ; Sat, 6 Nov 2010 15:59:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 5FC3713364DE for ; Sat, 6 Nov 2010 12:59:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oA6FxmAc020025; Sat, 6 Nov 2010 11:59:48 -0400 (EDT) To: "Jonathan Hoover" cc: "pgsql-admin@postgresql.org" Subject: Re: Interesting Query Performance Question In-reply-to: <68C107706843A54BA7D9CAEF123ADF05010346CAE6@SP2-EX07VS03.ds.corp.yahoo.com> References: <68C107706843A54BA7D9CAEF123ADF05010346CAE6@SP2-EX07VS03.ds.corp.yahoo.com> Comments: In-reply-to "Jonathan Hoover" message dated "Sat, 06 Nov 2010 08:01:18 -0700" Date: Sat, 06 Nov 2010 11:59:48 -0400 Message-ID: <20024.1289059188@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/55 X-Sequence-Number: 36021 "Jonathan Hoover" writes: > [ poor performance with NOT IN ] > Query E then is apparently the way to go, but shouldn't there be a way > to get the query planner to take these steps itself? If A had ever > finished, I'd sure like to have seen an EXPLAIN ANALYZE on it. Well, just an EXPLAIN would have told you what the plan was like. What I suspect was happening was that your manipulations of the query altered the planner's estimate of the number of rows in the NOT IN's subquery, causing it to pick (or not) a hash-table-based implementation of NOT IN. The hashed approach is a lot faster but requires the subquery's output to fit in work_mem. In general, NOT IN is hard to optimize because of its weird behavior for NULLs. I'd suggest looking into converting the query to use an EXISTS instead. regards, tom lane