I wrote:
> However, even given that, it's odd that it prefers a plan with two sorts
> to a plan with one materialize. Poking around in costsize.c, I think
> that the reason for this is that the rescan cost of a sort is estimated
> at cpu_operator_cost per tuple, whereas rescanning a materialize node is
> being estimated at cpu_tuple_cost per tuple. For a plan where rescan
> cost is the dominant factor, that matters. We probably ought to make
> those two estimates the same. Since neither plan node type does any
> projection or qual checking, the lower number is probably the better
> choice.
I've done that in HEAD. I'm loath to touch it in the back branches,
though, because the logic in that area now is quite different from what
it was in 8.4 and earlier. As I said before, I think this isn't too
important in cases where you're not forcing a mergejoin, so it seems
better to not risk destabilizing plans in released branches.
regards, tom lane