From: mark <dvlhntr@gmail.com>
To: 'Craig Ringer' <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: not exits slow compared to not in. (nested loops killing me)
Date: Mon, 6 Jun 2011 18:16:12 -0600
Message-ID: <005701cc24a8$1cdf1770$569d4650$@com> (raw)
In-Reply-To: <4DED5DB5.4030309@postnewspapers.com.au>
References: <BANLkTinjCKm3kC4GemetBmvOcwL5HBwqww@mail.gmail.com>
<4DED5DB5.4030309@postnewspapers.com.au>
> -----Original Message-----
> From: Craig Ringer [mailto:craig@postnewspapers.com.au]
> Sent: Monday, June 06, 2011 5:08 PM
> To: mark
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] not exits slow compared to not in. (nested loops
> killing me)
>
> 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.
I am starting to think the planner might be confused in 9.0.2. I got a
reasonable query time, given resource constraints, on a very small VM on my
laptop running 9.0.4.
I am going to work on getting the vm I was using to test this with up to
9.0.4 and test again.
There is a note in the 9.0.4 release notes
" Improve planner's handling of semi-join and anti-join cases (Tom Lane)"
Not sure that is the reason I got a much better outcome with a much smaller
vm. But once I do some more testing I will report back.
>
> 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?
Will try that as well. Currently the default stat target is 100. Will try at
250, and 500 and report back.
>
> Is autovacuum being allowed to do its work and regularly ANALYZE the
> database? Does an explicit 'ANALYZE' help?
Auto vac is running, I have explicitly vacuum & analyzed the whole db. That
didn't change anything.
>
> --
> 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: dvlhntr@gmail.com, craig@postnewspapers.com.au
Subject: Re: not exits slow compared to not in. (nested loops killing me)
In-Reply-To: <005701cc24a8$1cdf1770$569d4650$@com>
* 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