Re: Query Optimizer makes a poor choice

On 29.11.2011 23:06, Filip Rembiałkowski wrote:
> 2011/11/29 Tyler Hains <thains(at)profitpointinc(dot)com>:
>
>
>> I haven't had a chance to experiment with the SET STATISTICS, but that
>> got me going on something interesting...
>>
>> Do these statistics look right?
>>
>> # SELECT attname, n_distinct, most_common_vals, histogram_bounds FROM
>> pg_stats WHERE tablename = 'cards';
>>
> ...
>> "card_set_id" 905
>> "{5201,3203,3169,5679,5143,5204,5655,4322,5236,4513}"
>> "{4,3080,3896,4349,4701,5179,5445,5706,6003,6361,6784}"
>
> This looks promising, because n_distinct is low enough that you can
> cover almost all values with statistics.
> raise the statistics and ANALYZE. should help.
> (NOTE NOTE NOTE: assuming that the distribution is even)

Estimating ndistinct is very tricky, there are well known fail cases
(skewed distributions etc.)

> ...
> but one thing we see for sure is that you have not tuned your
> PostgreSQL instance :-)
> I would recommend pgtune, -> pgfoundry.org/projects/pgtune/
> it covers most important stuff, *including* default_statistics_target.

How do we see that? The only thing you can derive from the above info is
that he's probably running 8.3 (or older), because the number of MVC is
10 and newer releases use 100 by default.

But the statistics target is modified rather rarely, only when it's
actually needed - the default is usually enough and increasing it just
adds overhead to planning.

And pgtune can't reliably suggest a good value, because it's very
dependent on the data. It can merely recommend some reasonable values
(and it recommends 10 for most workloads anyway, except for DWH and
mixed). Don't touch default_statistics_target unless you're sure it
helps and set it only for those columns that need it.

Tomas