Re: performance change from 8.3.1 to later releases

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Tue, Apr 20, 2010 at 12:38 PM, Roger Ging <rging(at)musicreports(dot)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