From: Greg Smith <greg@2ndquadrant.com>
To: Mark Rostron <mrostron@ql2.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan
Date: Sun, 13 Feb 2011 21:33:39 -0500
Message-ID: <4D589483.3010901@2ndquadrant.com> (raw)
In-Reply-To: <FD020D3E50E7FA479567872E5F5F31E3046520C463@ex01.corp.ql2.com>
References: <FD020D3E50E7FA479567872E5F5F31E3046520C463@ex01.corp.ql2.com>
Mark Rostron wrote:
>
> Was there any major optimizer change between 8.3.10 to 8.3.14?
>
> I'm getting a difference in explain plans that I need to account for.
>
There were some major changes in terms of how hashing is used for some
types of query plans. And one of the database parameters,
default_statistics_target, increased from 10 to 100 between those two
versions. You can check what setting you have on each by doing:
show default_statistics_target;
From within psql. It's possible the 8.3 optimizer is just getting
lucky running without many statistics, and collecting more of them is
making things worse. It's also possible you're running into a situation
where one of the new hash approaches in 8.4 just isn't working out well
for you.
It would be easier to suggest what might be wrong if you included
"EXPLAIN ANALYZE" output instead of just EXPLAIN. It's not obvious
whether 8.3 or 8.4 is estimating things better.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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: greg@2ndquadrant.com, mrostron@ql2.com
Subject: Re: comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan
In-Reply-To: <4D589483.3010901@2ndquadrant.com>
* 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