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

> -----Original Message-----
> From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
> Sent: Monday, June 06, 2011 5:08 PM
> To: mark
> Cc: pgsql-performance(at)postgresql(dot)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