Re: not exits slow compared to not in. (nested loops killing me)

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: mark <dvlhntr(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: not exits slow compared to not in. (nested loops killing me)
Date: 2011-06-06 23:07:33
Message-ID: 4DED5DB5.4030309@postnewspapers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-06-07 00:09:40 Re: not exits slow compared to not in. (nested loops killing me)
Previous Message Tom Lane 2011-06-06 23:02:23 Re: 8.4/9.0 simple query performance regression