Re: Weird index or sort behaviour

On Tue, Aug 18, 2009 at 5:57 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Hmmm ... actually, after looking at the code, I notice that we only
> consider adding a Materialize node to buffer an inner input that is a
> Sort node.  The idea was suggested by Greg Stark, if memory serves.
> I wonder now if it'd be worthwhile to generalize that to consider
> adding a Materialize above *any* inner mergejoin input.

If my recollection is right the reason we put the materialize above
the sort node has to do with Simon's deferred final merge pass
optimization. The materialize was a way to lazily build the final
merge as we do the merge but still have the ability to rewind.

I would be more curious in the poster's situation to turn off
enable_seqscan, enable_sort, and/or enable_nestloop see how the index
scan merge join plan runs. rewinding an index scan is more expensive
than rewinding a materialize node but would it really be so much
expensive that it's worth copying the entire table into temporary
space?

--
greg
http://mit.edu/~gsstark/resume.pdf