DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:cc:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:thread-index:content-language;
	b=gadjWjuTAwKnIgKN8sdAVdaclOllZi/+1p9VC0tU+6Zn8ezwP0Rhdt2dU9OohwoB+D
	C/IcXb3Cn97spHtN9jLYASqNtoHq56LKueum37ef9AiWGcU0GI1fqOMEgdfUFxSxuxhQ
	3dDnCZfa7owfEPmz3I44L1O2wkvnRlG/Zg0As=
From: "mark" <dvlhntr@gmail.com>
To: "'Craig Ringer'" <craig@postnewspapers.com.au>
Cc: <pgsql-performance@postgresql.org>
References: <BANLkTinjCKm3kC4GemetBmvOcwL5HBwqww@mail.gmail.com>
	<4DED5DB5.4030309@postnewspapers.com.au>
In-Reply-To: <4DED5DB5.4030309@postnewspapers.com.au>
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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Acwkno8HG0EkWLlWT6CCACcb6O1iZAAB6R/A
Content-Language: en-us


> -----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