Re: Query Optimizer makes a poor choice

2011/11/29 Tyler Hains <thains@profitpointinc.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)


...
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.



Filip