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