> -----Original Message----- > From: Craig Ringer [mailto:craig@xxxxxxxxxxxxxxxxxxxxx] > Sent: Monday, June 06, 2011 5:08 PM > To: mark > Cc: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
