From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: 8.4 optimization regression?
Date: Tue, 23 Aug 2011 23:15:40 -0400
Message-ID: <15708.1314155740@sss.pgh.pa.us> (raw)
In-Reply-To: <4E5331FF.3000900@catalyst.net.nz>
References: <4E5331FF.3000900@catalyst.net.nz>
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
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, mark.kirkwood@catalyst.net.nz
Subject: Re: 8.4 optimization regression?
In-Reply-To: <15708.1314155740@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