Re: not exits slow compared to not in. (nested loops killing me) - Mailing list pgsql-performance

From mark
Subject Re: not exits slow compared to not in. (nested loops killing me)
Date
Msg-id 005701cc24a8$1cdf1770$569d4650$@com
Whole thread Raw
In response to Re: not exits slow compared to not in. (nested loops killing me)  (Craig Ringer <craig@postnewspapers.com.au>)
List pgsql-performance
> -----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


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: not exits slow compared to not in. (nested loops killing me)
Next
From: Didik Prasetyo
Date:
Subject: i want to ask monitory peformance memory postgresql with automatically