Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 669731337B4A for ; Sun, 13 Feb 2011 22:33:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 44241-04 for ; Mon, 14 Feb 2011 02:33:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by mail.postgresql.org (Postfix) with ESMTP id D21151337B2F for ; Sun, 13 Feb 2011 22:33:43 -0400 (AST) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap0) with ESMTP (Nemesis) id 0MNO5B-1PiTfh3kUr-006uyf; Mon, 14 Feb 2011 03:33:41 +0100 Message-ID: <4D589483.3010901@2ndquadrant.com> Date: Sun, 13 Feb 2011 21:33:39 -0500 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Mark Rostron CC: "pgsql-performance@postgresql.org" Subject: Re: comparison of 8.3.10 to 8.3.14 reveals unexpected difference in explain plan References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020209040906020405090801" X-Provags-ID: V02:K0:yYTMFmzeo+zYbI9smjPXR3xg0gUumwqMDla3pRla0ig 1SvPE9gSTZeY2KZLtdx1xABnA/Tg/6TqbJCTwcLl9hpJS8WpsG 8yJupJ/MYdoAn278Lnh01fqDU1HRdlWKITWwlgGEBgoRVLjxgL gwGfwvUMKSv/4zqdNnws3s/Ql1u/4s+KULOy2c+vX6yNK6aH23 nMgv5OW5pxBH8f0vCErSbtP8llWeOa0+orOoFB7WJU= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201102/365 X-Sequence-Number: 42503 This is a multi-part message in MIME format. --------------020209040906020405090801 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------020209040906020405090801 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------020209040906020405090801--