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

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