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

On Fri, 2007-09-21 at 16:26 +0200, Csaba Nagy wrote:
> [snip]
> 
> Ok, I was not able to follow your explanation, it's too deep for me into
> what the planner does...

I'm thinking that this case is too narrow to do too much with, when I
think about how we might do what I proposed. OTOH this isn't the first
bad plan we've had because we used the index for ordering. There might
be some common link that we can improve upon.

> > Incidentally, the way out of this is to improve the stats by setting
> > stats target = 1000 on column a of ta. That will allow the optimizer to
> > have a better estimate of the tail of the distribution of a, which
> > should then be more sensibly reflected in the cost of the Index Scan.
> > That doesn't solve the actual problem, but should help in your case.
> 
> OK, I can confirm that. I set the statistics target for column "a" on
> table_a to 1000, analyzed, and got the plan below. The only downside is
> that analyze became quite expensive on table_a, it took 15 minutes and
> touched half of the pages... I will experiment with lower settings,
> maybe it will work with less than 1000 too.

Well, we know there are ways of optimizing ANALYZE.

ISTM we should be able to auto-select stats target based upon the shape
of the frequency distribution of the column values. We'd need to make
some calculations about the index cost model, but its probably worth it
for the future.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com