From: Craig Ringer <craig@postnewspapers.com.au>
To: mark <dvlhntr@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: not exits slow compared to not in. (nested loops killing me)
Date: Tue, 07 Jun 2011 07:07:33 +0800
Message-ID: <4DED5DB5.4030309@postnewspapers.com.au> (raw)
In-Reply-To: <BANLkTinjCKm3kC4GemetBmvOcwL5HBwqww@mail.gmail.com>
References: <BANLkTinjCKm3kC4GemetBmvOcwL5HBwqww@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, dvlhntr@gmail.com
Subject: Re: not exits slow compared to not in. (nested loops killing me)
In-Reply-To: <4DED5DB5.4030309@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox