Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id B121BB5F89F for ; Mon, 6 Jun 2011 20:07:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 39818-04 for ; Mon, 6 Jun 2011 23:07:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [173.230.139.50]) by mail.postgresql.org (Postfix) with ESMTP id 22D0DB5DD17 for ; Mon, 6 Jun 2011 20:07:49 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by www.postnewspapers.com.au (Postfix) with ESMTP id D2C403E; Mon, 6 Jun 2011 19:07:48 -0400 (EDT) X-Virus-Scanned: amavisd-new at postnewspapers.com.au.com Received: from www.postnewspapers.com.au ([127.0.0.1]) by localhost (www.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id estfCWXGIKmm; Mon, 6 Jun 2011 19:07:48 -0400 (EDT) Received: from mail.postnewspapers.com.au (ppp171-128.static.internode.on.net [150.101.171.128]) by www.postnewspapers.com.au (Postfix) with ESMTP id CDF6F2D; Mon, 6 Jun 2011 19:07:47 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 99C1EAE4F4; Tue, 7 Jun 2011 07:07:46 +0800 (WST) X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au Received: from mail.postnewspapers.com.au ([127.0.0.1]) by localhost (mail.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id DoHruNvdk5kH; Tue, 7 Jun 2011 07:07:40 +0800 (WST) Received: from [10.1.1.5] (203-59-82-2.dyn.iinet.net.au [203.59.82.2]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "Craig Ringer", Issuer "POST Certificate Authority" (verified OK)) by mail.postnewspapers.com.au (Postfix) with ESMTPSA id 28883AE364; Tue, 7 Jun 2011 07:07:39 +0800 (WST) Message-ID: <4DED5DB5.4030309@postnewspapers.com.au> Date: Tue, 07 Jun 2011 07:07:33 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.17) Gecko/20110428 Fedora/3.1.10-1.fc15 Thunderbird/3.1.10 MIME-Version: 1.0 To: mark CC: pgsql-performance@postgresql.org Subject: Re: not exits slow compared to not in. (nested loops killing me) References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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: 201106/40 X-Sequence-Number: 43973 On 06/07/2011 04:38 AM, mark wrote: > NOT EXISTS (with 64MB of work_mem) > http://explain.depesz.com/s/EuX Hash Anti Join (cost=443572.19..790776.84 rows=1 width=1560) (actual time=16337.711..50358.487 rows=2196299 loops=1) Note the estimated vs actual rows. Either your stats are completely ridiculous, or the planner is confused. What are your stats target levels? Have you tried increasing the stats levels on the table(s) or at least column(s) affected? Or tweaking default_statistics_target if you want to use a bigger hammer? Is autovacuum being allowed to do its work and regularly ANALYZE the database? Does an explicit 'ANALYZE' help? -- Craig Ringer