From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Kratz <chris.kratz@vistashare.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Incorrect estimates on columns
Date: Wed, 17 Oct 2007 20:23:39 -0400
Message-ID: <13440.1192667019@sss.pgh.pa.us> (raw)
In-Reply-To: <200710171543.40840.chris.kratz@vistashare.com>
References: <200710171334.24526.chris.kratz@vistashare.com>
<9262.1192646974@sss.pgh.pa.us>
<200710171543.40840.chris.kratz@vistashare.com>
Chris Kratz <chris.kratz@vistashare.com> writes:
> On Wednesday 17 October 2007 14:49, Tom Lane wrote:
>> Evidently it's not realizing that every row of par will have a join
>> partner, but why not? I suppose a.activityid is unique, and in most
>> cases that I've seen the code seems to get that case right.
>>
>> Would you show us the pg_stats rows for par.activity and a.activityid?
> Here are the pg_stats rows for par.activity and a.activityid.
Hmm, nothing out of the ordinary there.
I poked at this a bit and realized that what seems to be happening is
that the a.programid = 171 condition is reducing the selectivity
estimate --- that is, it knows that that will filter out X percent of
the activity rows, and it assumes that *the size of the join result will
be reduced by that same percentage*, since join partners would then be
missing for some of the par rows. The fact that the join result doesn't
actually decrease in size at all suggests that there's some hidden
correlation between the programid condition and the condition on
par.provider_lfm. Is that true? Maybe you could eliminate one of the
two conditions from the query?
Since PG doesn't have any cross-table (or even cross-column) statistics
it's not currently possible for the optimizer to deal very well with
hidden correlations like this ...
regards, tom lane
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: tgl@sss.pgh.pa.us, chris.kratz@vistashare.com
Subject: Re: Incorrect estimates on columns
In-Reply-To: <13440.1192667019@sss.pgh.pa.us>
* 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