To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: 8.4 optimization regression? 
In-reply-to: <4E601B2F.30107@catalyst.net.nz> 
References: <4E5331FF.3000900@catalyst.net.nz>
 <15708.1314155740@sss.pgh.pa.us>
	<4E5DCFA4.5090806@catalyst.net.nz> <5156.1314829311@sss.pgh.pa.us>
	<26940.1314918836@sss.pgh.pa.us> <4E6012E2.2000004@catalyst.net.nz>
	<4E601B2F.30107@catalyst.net.nz>
Comments: In-reply-to Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
	message dated "Fri, 02 Sep 2011 11:54:23 +1200"
Date: Sun, 04 Sep 2011 18:14:59 -0400
Message-ID: <8731.1315174499@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> While this is still fresh in your mind, a couple of additional anti join 
> queries are still managing to sneak past estimation:

Yeah, those are estimating that all the outer rows have join partners,
because there are more distinct values in the sub-select than there are
in the outer relation.  AFAICS there are not any errors in the
statistics, it's just that the estimation rule falls down here.

If you've heard of a better estimator for semijoin/antijoin selectivity,
I'm all ears.  The best idea I have at the moment is to put an arbitrary
upper limit on the estimated selectivity, but that would be, well,
arbitrary.

			regards, tom lane