From: Dan Harris <fbsd@drivefaster.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Encouraging multi-table join order
Date: Tue, 11 Apr 2006 14:59:22 -0600
Message-ID: <443C18AA.3000500@drivefaster.net> (raw)
In-Reply-To: <2406.1144717297@sss.pgh.pa.us>
References: <443AAC26.4070802@drivefaster.net>
<1543.1144710756@sss.pgh.pa.us>
<443AEF9B.3000909@drivefaster.net>
<2406.1144717297@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fbsd@drivefaster.net, tgl@sss.pgh.pa.us
Subject: Re: Encouraging multi-table join order
In-Reply-To: <443C18AA.3000500@drivefaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox