To: Chris Kratz <chris.kratz@vistashare.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Incorrect estimates on columns 
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>
Comments: In-reply-to Chris Kratz <chris.kratz@vistashare.com>
	message dated "Wed, 17 Oct 2007 15:43:40 -0400"
Date: Wed, 17 Oct 2007 20:23:39 -0400
Message-ID: <13440.1192667019@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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