Re: Weird index or sort behaviour

Matthew Wakeling <matthew(at)flymine(dot)org> writes:
> I'm seeing some interesting behaviour. I'm executing a query where I
> perform a merge join between two copies of the same table, completely
> symmetrically, and the two sides of the merge are sourced differently.

This is not as surprising as you think. A mergejoin is *not*
symmetrical between its two inputs: the inner side is subject to being
partially rewound and rescanned when the outer side is advanced to a new
row with the same merge key. This means there is a premium on cheap
rescan for the inner side that doesn't exist for the outer ... and a
sort node is cheaper to rescan than a generic indexscan. It's
impossible to tell from the data you provided whether the planner was
correct to pick a sort over an indexscan for the inner side, but the
fact that it did so is not prima facie evidence of a bug. You could
force choice of the other plan via enable_sort = off and then compare
estimated and actual runtimes to see if the planner got it right.

regards, tom lane