From: | Doug Fields <dfields-pg-general(at)pexicom(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Ian Harding" <ianh(at)tpchd(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Force a merge join? |
Date: | 2002-05-18 22:50:00 |
Message-ID: | 5.1.0.14.2.20020518184542.01f391b0@mail.pexicom.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-05-18 22:51:41 | Re: how to get id of last insert on a serial type? |
Previous Message | Tom Lane | 2002-05-18 22:31:14 | Re: Force a merge join? |