From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Roger Ging <rging@musicreports.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance change from 8.3.1 to later releases
Date: Tue, 20 Apr 2010 23:47:01 -0400
Message-ID: <14045.1271821621@sss.pgh.pa.us> (raw)
In-Reply-To: <x2gdcc563d11004201233yea1a2e46i9c97b3a9f6269dea@mail.gmail.com>
References: <4BCDF48C.6000409@musicreports.com>
<x2gdcc563d11004201233yea1a2e46i9c97b3a9f6269dea@mail.gmail.com>
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Tue, Apr 20, 2010 at 12:38 PM, Roger Ging <rging@musicreports.com> 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
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: tgl@sss.pgh.pa.us, scott.marlowe@gmail.com, rging@musicreports.com
Subject: Re: performance change from 8.3.1 to later releases
In-Reply-To: <14045.1271821621@sss.pgh.pa.us>
* 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