To: Csaba Nagy <nagy@ecircle-ag.com>
cc: postgres performance list <pgsql-performance@postgresql.org>,
	Simon Riggs <simon@2ndquadrant.com>
Subject: Re: Searching for the cause of a bad plan 
In-reply-to: <1190894004.5430.46.camel@PCD12478> 
References: <1190369024.4661.173.camel@PCD12478>
	<6175.1190390913@sss.pgh.pa.us>
	<1190396352.4202.109.camel@ebony.site>
	<13644.1190397220@sss.pgh.pa.us>
	<1190399879.4202.127.camel@ebony.site>
	<27857.1190417434@sss.pgh.pa.us>
	<1190640429.4181.165.camel@ebony.site>
	<1190818357.5430.29.camel@PCD12478>
	<21582.1190820137@sss.pgh.pa.us>
	<1190894004.5430.46.camel@PCD12478>
Comments: In-reply-to Csaba Nagy <nagy@ecircle-ag.com>
	message dated "Thu, 27 Sep 2007 13:53:24 +0200"
Date: Thu, 27 Sep 2007 10:40:25 -0400
Message-ID: <29186.1190904025@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Csaba Nagy <nagy@ecircle-ag.com> writes:
> On Wed, 2007-09-26 at 11:22 -0400, Tom Lane wrote:
>> ... how
>> many values of "a" are there really, and what's the true distribution of
>> counts?  

> table_a has 23366 distinct values. Some statistics (using R):

>> summary(table_a_histogram)
>        a                 count        
>  Min.   : 70000857   Min.   :      1  
>  1st Qu.:700003628   1st Qu.:      9  
>  Median :700011044   Median :     22  
>  Mean   :622429573   Mean   :  17640  
>  3rd Qu.:700018020   3rd Qu.:    391  
>  Max.   :800003349   Max.   :3347707  

Ugh, classic long-tail distribution.  This is a really hard problem to
solve by sampling --- the sample will naturally be biased towards the
more common values, and so ANALYZE tends to conclude there are fewer
distinct values than there really are.  That means n_distinct in the
stats is too small, and that feeds directly into the misestimation of
the number of matching rows.

And yet there's another trap here: if the parameter you passed in
chanced to be one of the very common values, a plan that was optimized
for a small number of matches would perform terribly.

We've speculated about trying to deal with these types of situations
by switching plans on-the-fly at runtime, but that's just blue-sky
dreaming at the moment.  In the short run, if boosting the stats target
doesn't result in acceptable plans, there may be no real solution other
than to avoid parameterized queries on this column.

			regards, tom lane