To: Matthew Wakeling <matthew@flymine.org>
cc: Stephen Frost <sfrost@snowman.net>, Corin <wakathane@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: too complex query plan for not exists query and multicolumn
	indexes
In-reply-to: <alpine.DEB.2.00.1003221100080.9798@aragorn.flymine.org> 
References: <4BA36D7B.6010506@gmail.com>
	<20100319182750.GQ21875@tamriel.snowman.net>
	<alpine.DEB.2.00.1003221100080.9798@aragorn.flymine.org>
Comments: In-reply-to Matthew Wakeling <matthew@flymine.org>
	message dated "Mon, 22 Mar 2010 11:48:44 -0000"
Date: Mon, 22 Mar 2010 08:40:12 -0400
Message-ID: <16237.1269261612@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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