Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id CCED36324B8 for ; Wed, 21 Apr 2010 00:47:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 86955-04-4 for ; Wed, 21 Apr 2010 03:47:13 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 6B92A6338A4 for ; Wed, 21 Apr 2010 00:47:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o3L3l1f0014046; Tue, 20 Apr 2010 23:47:01 -0400 (EDT) To: Scott Marlowe cc: Roger Ging , pgsql-performance@postgresql.org Subject: Re: performance change from 8.3.1 to later releases In-reply-to: References: <4BCDF48C.6000409@musicreports.com> Comments: In-reply-to Scott Marlowe message dated "Tue, 20 Apr 2010 13:33:32 -0600" Date: Tue, 20 Apr 2010 23:47:01 -0400 Message-ID: <14045.1271821621@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.109 tagged_above=-10 required=5 tests=AWL=-0.199, BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/270 X-Sequence-Number: 38520 Scott Marlowe writes: > On Tue, Apr 20, 2010 at 12:38 PM, Roger Ging wrote: >> I have access to servers running 8.3.1, 8.3.8, 8.4.2 and 8.4.3.  I have >> noticed that on the 8.4.* versions, a lot of our code is either taking much >> longer to complete, or never completing.  I think I have isolated the >> problem to queries using in(), not in() or not exists().  I've put together >> a test case with one particular query that demonstrates the problem. > We get a Seq Scan with a huge cost, and no hash agg or quick sort. Is > the work_mem the same or similar? It looks to me like it's not. The 8.4 plan is showing sorts spilling to disk for amounts of data that the 8.3 plan is perfectly willing to hold in memory. I'm also wondering if the 8.4 server is on comparable hardware, because it seems to be only about half as fast for the plain seqscan steps, which surely ought to be no worse than before. regards, tom lane