Subject: Re: Searching for the cause of a bad plan
From: Csaba Nagy <nagy@ecircle-ag.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: <1190372365.4202.14.camel@ebony.site>
References: <1190369024.4661.173.camel@PCD12478>
	<1190372365.4202.14.camel@ebony.site>
Content-Type: text/plain
Date: Fri, 21 Sep 2007 13:29:26 +0200
Message-Id: <1190374166.4661.194.camel@PCD12478>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2007-09-21 at 11:59 +0100, Simon Riggs wrote:
> Please re-run everything on clean tables without frigging the stats. We
> need to be able to trust what is happening is normal.

I did, the plan fiddling happened after getting the plans after a fresh
analyze, and I did run the plan again with fresh analyze just before
sending the mail and the plan was the same. In fact I spent almost 2
days playing with the query which is triggering this behavior, until I
tracked it down to this join. Thing is that we have many queries which
rely on this join, so it is fairly important that we understand what
happens there.

> Plan2 sees that b1 is wider, which will require more heap blocks to be
> retrieved. It also sees b1 is less correlated than b2, so again will
> require more database blocks to retrieve. Try increasing
> effective_cache_size.

effective_cach_size is set to ~2.7G, the box has 4G memory. I increased
it now to 3,5G but it makes no difference. I increased it further to 4G,
no difference again.

> Can you plans with/without LIMIT and with/without cursor, for both b1
> and b2?

The limit is unfortunately absolutely needed part of the query, it makes
no sense to try without. If it would be acceptable to do it without the
limit, then it is entirely possible that the plan I get now would be
indeed better... but it is not acceptable.

Thanks,
Csaba.