Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 62F0F632B68 for ; Thu, 15 Jul 2010 14:17:39 -0300 (ADT) 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 93998-08 for ; Thu, 15 Jul 2010 17:17:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id 1CFAB632A86 for ; Thu, 15 Jul 2010 14:17:32 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id E4CD6348037; Thu, 15 Jul 2010 10:17:30 -0700 (PDT) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rrW6mJA1WN4S; Thu, 15 Jul 2010 10:17:25 -0700 (PDT) Received: from [75.142.153.107] (unknown [75.142.153.107]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id 25036348002; Thu, 15 Jul 2010 10:17:25 -0700 (PDT) Subject: Re: Identical query slower on 8.4 vs 8.3 From: "Joshua D. Drake" Reply-To: jd@commandprompt.com To: Patrick Donlin Cc: pgsql-performance@postgresql.org In-Reply-To: <1187615879.13268921279204882941.JavaMail.root@mm-mailstore02.merit.edu> References: <1187615879.13268921279204882941.JavaMail.root@mm-mailstore02.merit.edu> Content-Type: text/plain; charset="UTF-8" Organization: Command Prompt, Inc. Date: Thu, 15 Jul 2010 10:17:21 -0700 Message-ID: <1279214241.30528.42.camel@jd-desktop.unknown.charter.com> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/243 X-Sequence-Number: 39632 On Thu, 2010-07-15 at 10:41 -0400, Patrick Donlin wrote: > Results when running on the v8.3.7 server.... > Total query runtime: 32185 ms. > 700536 rows retrieved. > > Results when running on the v8.4.4 server.... > Total query runtime: 164227 ms. > 700536 rows retrieved. > > > Anyone have any ideas on where I should start looking to figure this > out? I didn't perform any special steps when moving to v8.4, I just > did a pg_dump from the 8.3 server and restored it on the new 8.4 > servers. Maybe that is where I made a mistake. Three immediate things come to mind: 1. One had relations in file or shared buffer cache, the other didn't 2. One is running ext4 versus ext3 and when you end up spilling to disk when you over run work_mem, the ext4 machine is faster, but without knowing which machine is which it is a bit tough to diagnose. 3. You didn't run ANALYZE on one of the machines Sincerely, Joshua D. Drake > > Thanks! > Patrick > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering