Re: 8.4 optimization regression?

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