On Tue, 3 Mar 2009, Aaron Guyon wrote:
On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Are you sure you are comparing apples to apples here? Same configure
options for the builds, same parameter values in postgresql.conf, both
databases ANALYZEd, etc? And are they running on the same hardware?
Thank you for looking at this Tom. Yes, we have made sure we are comparing
apples to apples here. The postgresql.confs are identical, as are the
configure flags:
--disable-debug --enable-shared --enable-thread-safety --with-perl
--with-pam --without-docdir --without-tcl --without-python --without-krb5
--without-ldap --without-bonjour --enable-integer-datetimes
--prefix=/opt/postgresql
However, the db was not analyzed. I'll attached the new explain analyze of
the queries with the db analyzed, but 8.2 still beats 8.3.
The tests are both being run on the same machine, a Quad-core AMD Opteron
Processor 2212
(each with 1024 KB cache) and 4GB of RAM.
I find it telling that the query plan differs so much between postgres 8.2.
and
8.3. For example, why does the 8.3. planner choose to perform so many seq
scans? I know seq scans are faster than index scans for small tables, but
these tables have 60K+ rows... surely an index scan would have been a better
choice here? If you look at the 8.2. query plan, it is very clean in
comparison, index scans all the way through. I can't help but think the 8.3
planner is simply failing to make the right choices in our case. Another
question would be, why are there so many hash joins in the 8.3 plan now?
All
our indexes are btrees...
Any light that can be shed on what going on with the 8.3. planner would be
much
appreciated. Thanks in advance.
if you haven't done a vaccum analyse on either installation then postgres'
idea of what sort of data is in the database is unpredictable, and as a
result it's not surprising that the two systems guess differently about
what sort of plan is going to be most efficiant.
try doing vaccum analyse on both databases and see what the results are.
David Lang
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
