Re: Performance regression 8.3.8 -> 8.4.1 with NOT EXISTS

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: wiktor(dot)wodecki(at)net-m(dot)de
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance regression 8.3.8 -> 8.4.1 with NOT EXISTS
Date: 2009-11-17 16:30:14
Message-ID: 29752.1258475414@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Wiktor Wodecki <wiktor(dot)wodecki(at)net-m(dot)de> writes:
> As you can see the 8.4 run is 16 times slower. It was even worse before
> we added the index idx_b_b_date which we didn't have initially.
> Is there anything we can do about this issue? Do you need more information?

You could prevent flattening of the EXISTS subquery by adding an OFFSET
0 or some such to it. A real fix involves being able to handle nestloop
indexscans where the parameter comes from more than one join level up;
there's been some discussion about that but it's not going to happen
in 8.4.x.

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2009-11-17 16:36:26 Re: SSD + RAID
Previous Message Craig James 2009-11-17 15:51:40 Re: Is Diskeeper Automatic Mode safe?