Matthew Wakeling <matthew@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
