Re: Force a merge join?

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