At 06:31 PM 5/18/2002, Tom Lane wrote:
[Analysis omitted]
>The major problem clearly is the horribly bad estimate on the
>selectivity of the clause
> WHERE (a.list_id=148 OR a.list_id=146 OR a.list_id=145 OR
> a.list_id=147 OR a.list_id=144)
>This is showing that the planner estimated 84 matching rows (vs. 176
>with no stats!) whereas it was really 15859.
I find that this is actually fairly typical, where the row estimates and
actual rows are off by orders of magnitudes. Some info on this table:
pexitest=# select count(distinct list_id) from test_list_entries;
select count(*) from test_list_ count
-------
308
(1 row)
pexitest=# select count(*) from test_list_entries;
count
--------
800576
(1 row)
Indicating that the safest assumption based upon no information is that
each list_id has about 2600 records associated with it.
>Could we see the whole pg_stats row for list_id? In particular I was
>wondering if any of the list_id values being selected for appear in
>most_common_vals.
Absolutely:
select * from pg_stats where tablename = 'test_list_entries' and attname =
'list_id';
tablename | attname | null_frac | avg_width | n_distinct
| most_common_vals |
most_common_freqs |
histogram_bounds | correlation
-------------------+---------+-----------+-----------+------------+------------------------------------------+--------------------------------------------------------------------------------------------------+---------------------------------------------+-------------
test_list_entries | list_id | 0 | 4 | 189 |
{38,192,369,330,332,501,229,493,319,424} |
{0.389667,0.123667,0.0156667,0.013,0.00933333,0.00933333,0.009,0.00866667,0.00833333,0.00833333}
| {5,138,154,224,296,315,342,371,439,460,505} | 0.839262
(1 row)
Many thanks,
Doug