Re: Weird index or sort behaviour

Greg Stark <gsstark(at)mit(dot)edu> writes:
> 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?

Absolutely not, but remember that what we're expecting the Materialize
to do is buffer only as far back as the last Mark, so that it's unlikely
ever to spill to disk. It might well be a win to do that rather than
re-fetching from the indexscan. The incremental win compared to not
having the materialize would be small compared to what it is for a sort,
but it could still be worthwhile I think. In particular, in Matthew's
example the sort is being estimated at significantly higher cost than
the indexscan, which presumably means that we are estimating there will
be a *lot* of re-fetches, else we wouldn't have rejected the indexscan
on the inside. Inserting a materialize would make the re-fetches
cheaper. I'm fairly sure that this plan structure would cost out
cheaper than the sort according to cost_mergejoin's cost model. As
noted in the comments therein, that cost model is a bit oversimplified,
so it might not be cheaper in reality ... but we ought to try it.

regards, tom lane