From: Tom Lane <tgl@sss.pgh.pa.us>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Cc: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: Searching for the cause of a bad plan
Date: Thu, 27 Sep 2007 10:40:25 -0400
Message-ID: <29186.1190904025@sss.pgh.pa.us> (raw)
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>
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
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: tgl@sss.pgh.pa.us, nagy@ecircle-ag.com, simon@2ndquadrant.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <29186.1190904025@sss.pgh.pa.us>
* 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