Re: too complex query plan for not exists query and multicolumn indexes - Mailing list pgsql-performance

From Tom Lane
Subject Re: too complex query plan for not exists query and multicolumn indexes
Date
Msg-id 16237.1269261612@sss.pgh.pa.us
Whole thread Raw
In response to Re: too complex query plan for not exists query and multicolumn indexes  (Matthew Wakeling <matthew@flymine.org>)
List pgsql-performance
Matthew Wakeling <matthew@flymine.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

pgsql-performance by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: too complex query plan for not exists query and multicolumn indexes
Next
From: Matthew Wakeling
Date:
Subject: Re: GiST index performance