Re: Encouraging multi-table join order

Tom Lane wrote:
> <SNIP>
> So it's estimating 5775 cost units per probe into eventactivity, which
> is pretty high --- it must think that a lot of rows will be retrieved by
> the index (way more than the 20 or so it thinks will get past the filter
> condition).

> What does the pg_stats entry for eventactivity.incidentid
> contain?
select * from pg_stats where tablename = 'eventactivity' and
attname='incidentid';
schemaname | tablename | attname | null_frac | avg_width |
n_distinct |
most_common_vals
|
most_common_freqs
|
histogram_bounds |
correlation
------------+---------------+------------+-----------+-----------+------------+-----------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+-------------
public | eventactivity | incidentid | 0 | 14 |
8157 |
{P043190299,P051560740,P052581036,P052830218,P053100679,P053190889,P060370845,P042070391,P042690319,P043290117}
|
{0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333}
|
{P022140319,P030471058,P033090308,P041961082,P042910689,P050311006,P051350254,P052261148,P053270945,P060240316,P061000287}
| 0.241737

> It might be worth increasing the statistics target for that
> column to try to get a better estimate.
>
How high should I set this? I read the default is 10, but I'm not sure
if doubling this would make a difference or if I should be doing a much
larger number. There's approx 45 million rows in the table, if that matters.

Thanks again,
Dan