From: Csaba Nagy <nagy@ecircle-ag.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Searching for the cause of a bad plan
Date: Fri, 21 Sep 2007 13:29:26 +0200
Message-ID: <1190374166.4661.194.camel@PCD12478> (raw)
In-Reply-To: <1190372365.4202.14.camel@ebony.site>
References: <1190369024.4661.173.camel@PCD12478>
<1190372365.4202.14.camel@ebony.site>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagy@ecircle-ag.com, simon@2ndquadrant.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <1190374166.4661.194.camel@PCD12478>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox