To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: 8.4 optimization regression? 
In-reply-to: <4E5331FF.3000900@catalyst.net.nz> 
References: <4E5331FF.3000900@catalyst.net.nz>
Comments: In-reply-to Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
	message dated "Tue, 23 Aug 2011 16:52:15 +1200"
Date: Tue, 23 Aug 2011 23:15:40 -0400
Message-ID: <15708.1314155740@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> I am in the progress of an 8.3 to 8.4 upgrade for a customer. I seem to 
> have stumbled upon what looks like a regression. The two databases 
> (8.3.14 and 8.4.8) have identical tuning parameters (where that makes 
> sense) and run on identical hardware. Both databases are regularly 
> vacuumed and analyzed (not by autovacuum), and performing an ANALYZE 
> does not change the plans shown below.

Hmmm ... this is structurally a pretty simple query, so I'm surprised
that 8.3 and 8.4 see it very much differently.  The relation-level
estimates and plan choices are very nearly the same; the only thing
that's changed much is the estimates of the join sizes, and there were
not that many changes in the join selectivity estimation for simple
inner joins.  I wonder whether you are seeing a bad side-effect of this
patch:

http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7f3eba30

That code would only be reached when one or both join columns lack MCV
lists in pg_stats; if you had analyzed, the only reason for that to be
the case is if the column is unique (or nearly so, in ANALYZE's opinion).

			regards, tom lane