Re: Force a merge join?

Doug Fields <dfields-pg-general(at)pexicom(dot)com> writes:
> [ unanalyzed ]

> -> Index Scan using test_list_id_idx, test_list_id_idx,
> test_list_id_idx, test_list_id_idx, test_list_id_idx on test_list_entries
> a (cost=0.00..733.09 rows=176 width=72) (actual time=0.04..139.32
> rows=15859 loops=1)

> [ after analyze ]

> -> Index Scan using test_list_id_idx, test_list_id_idx,
> test_list_id_idx, test_list_id_idx, test_list_id_idx on test_list_entries
> a (cost=0.00..454.33 rows=84 width=72) (actual time=0.06..190.30
> rows=15859 loops=1)

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.

> select attname, null_frac, avg_width, n_distinct, most_common_freqs,
> correlation from pg_stats where tablename = 'test_list_entries';

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.

regards, tom lane