| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Ben Chobot <bench(at)silentmedia(dot)com> |
| 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:26:28 |
| Message-ID: | 1376.1266254788@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ben Chobot | 2010-02-15 17:35:13 | Re: 8.1 -> 8.4 regression |
| Previous Message | Scott Marlowe | 2010-02-15 17:24:27 | Re: Almost infinite query -> Different Query Plan when changing where clause value |