Re: too complex query plan for not exists query and multicolumn indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, Corin <wakathane(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: too complex query plan for not exists query and multicolumn indexes
Date: 2010-03-22 12:40:12
Message-ID: 16237.1269261612@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Matthew Wakeling <matthew(at)flymine(dot)org> writes:
> On Fri, 19 Mar 2010, Stephen Frost wrote:
>> ...it has to go to an external on-disk sort (see later on, and how to
>> fix that).

> This was covered on this list a few months ago, in
> http://archives.postgresql.org/pgsql-performance/2009-08/msg00184.php and
> http://archives.postgresql.org/pgsql-performance/2009-08/msg00189.php

> There seemed to be some consensus that allowing a materialise in front of
> an index scan might have been a good change. Was there any movement on
> this front?

Yes, 9.0 will consider plans like

Merge Join (cost=0.00..14328.70 rows=1000000 width=488)
Merge Cond: (a.four = b.hundred)
-> Index Scan using fouri on tenk1 a (cost=0.00..1635.62 rows=10000 width=244)
-> Materialize (cost=0.00..1727.16 rows=10000 width=244)
-> Index Scan using tenk1_hundred on tenk1 b (cost=0.00..1702.16 rows
=10000 width=244)

Some experimentation shows that it won't insert the materialize unless
quite a bit of re-fetching is predicted (ie neither side of the join is
unique). We might need to tweak the cost parameters once we get some
field experience with it.

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Wakeling 2010-03-22 13:29:49 Re: GiST index performance
Previous Message Matthew Wakeling 2010-03-22 11:48:44 Re: too complex query plan for not exists query and multicolumn indexes