Re: 8.1 -> 8.4 regression

From: Tom Lane
Subject: Re: 8.1 -> 8.4 regression
Date: ,
Msg-id: 1376.1266254788@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: 8.1 -> 8.4 regression  (Ben Chobot)
Responses: Re: 8.1 -> 8.4 regression  (Ben Chobot)
List: pgsql-performance

Tree view

8.1 -> 8.4 regression  (Ben Chobot, )
 Re: 8.1 -> 8.4 regression  (Dennis Björklund, )
 Re: 8.1 -> 8.4 regression  ("Kevin Grittner", )
  Re: 8.1 -> 8.4 regression  (Ben Chobot, )
   Re: 8.1 -> 8.4 regression  (Tom Lane, )
    Re: 8.1 -> 8.4 regression  (Ben Chobot, )
     Re: 8.1 -> 8.4 regression  (Tom Lane, )

Ben Chobot <> 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


pgsql-performance by date:

From: "Rose Zhou"
Date:
Subject: Re: disk space usage unexpected
From: "Joshua D. Drake"
Date:
Subject: Re: disk space usage unexpected