Re: query planner not using the correct index - Mailing list pgsql-performance

On Wed, Aug 6, 2008 at 10:24 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> OK, that's interesting. There are ways to examine Pg's statistics on
> columns, get an idea of which stats might be less than accurate, etc,
> but I'm not really familiar enough with it all to give you any useful
> advice on the details. I can make one suggestion in the vein of shotgun
> throubleshooting, though:
>
> Try altering the statistics targets on the tables of interest, or tweak
> the default_statistics_target, then rerun VACUUM ANALYZE and re-test.
> Maybe start with a stats target of 100 and see what happens.
>
> --
> Craig Ringer

I tried 100, 500, and 1000 for default_statistics_target. I think
below is the right query to examine the stats. None of the levels of
default_statistics_target I tried changed the query planners behavior.

It seems obvious that the stats on attr1 at the current level are
inaccurate as there are over 100,000 unique enteries in the table. But
even tweaking them to be more accurate doesn't seem to add any
benefit.

default_statistics_target = 10

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foos' AND attname='attr1';
 null_frac | n_distinct | most_common_vals | most_common_freqs
-----------+------------+------------------+-------------------
         0 |       1789 | {""}             | {0.625667}

default_statistics_target = 100

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
  null_frac  | n_distinct | most_common_vals | most_common_freqs
-------------+------------+------------------+-------------------
 0.000266667 |      17429 | {""}             | {0.6223}

default_statistics_target = 500

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
  null_frac  | n_distinct | most_common_vals | most_common_freqs
-------------+------------+------------------+-------------------
 0.000293333 |   -0.17954 | {""}             | {0.62158}

default_statistics_target = 1000

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
  null_frac  | n_distinct | most_common_vals | most_common_freqs
-------------+------------+------------------+-------------------
 0.000293333 |  -0.304907 | {""}             | {0.621043}