Skip site navigation (1) Skip section navigation (2)

Re: 8.1 -> 8.4 regression

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-performance(at)postgresql(dot)org
Subject: Re: 8.1 -> 8.4 regression
Date: 2010-02-15 17:35:13
Message-ID: 5FDB08C1-3835-4B80-835D-B44F483C3227@silentmedia.com (view raw or flat)
Thread:
Lists: pgsql-performance
Awesome, that did the trick. Thanks Tom! So I understand better, why is my case not the normal, better case?

(I assume the long-term fix is post-9.0, right?)

On Feb 15, 2010, at 9:26 AM, Tom Lane wrote:

> Ben Chobot <bench(at)silentmedia(dot)com> writes:
>> On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
>>> Could you show the query, along with table definitions (including
>>> indexes)?
> 
>> Oh, yeah, I suppose that would help. :)
> 
>> http://wood.silentmedia.com/bench/query_and_definitions
> 
> It looks like the problem is that the EXISTS sub-query is getting
> converted into a join; which is usually a good thing but in this case it
> interferes with letting the users table not be scanned completely.
> The long-term fix for that is to support nestloop inner indexscans where
> the index key comes from more than one join level up, but making that
> happen isn't too easy.
> 
> In the meantime, I think you could defeat the "optimization" by
> inserting LIMIT 1 in the EXISTS sub-query.
> 
> 			regards, tom lane


In response to

Responses

pgsql-performance by date

Next:From: Rose ZhouDate: 2010-02-15 19:59:51
Subject: disk space usage unexpected
Previous:From: Tom LaneDate: 2010-02-15 17:26:28
Subject: Re: 8.1 -> 8.4 regression

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group