Hi, Following up on the discussion below, I now have a patch.
The patch extends make_sort_input_target() with a conservative rule: defer additional non-sort targetlist expressions past Sort only when doing so does not require carrying any additional Vars/PlaceHolderVars through Sort. This way, Sort input width never increases. This still allows cases like repeat(i::text, ...) ORDER BY i to be projected above Sort, while avoiding the md5(widecol) counterexample mentioned earlier, since such expressions are not deferred when they would force a wide non-sort column through Sort. One limitation remains: this can't help queries like 'SELECT repeat(i::text, ...) FROM t ORDER BY othercols;' where the output expression depends on Vars that are not sort keys. In that case we still have to carry i through the Sort to be able to compute the final targetlist, so the patch cannot avoid inflating Sort's input width (and may still evaluate repeat() before Sort, depending on the existing projection placement rules). The existing volatile/SRF/expensive behavior is unchanged (expensive exprs are still postponed once a post-sort projection is needed). I also added regression coverage (including an md5(widecol)-style case). Some existing EXPLAIN outputs (e.g. join/groupingsets) now show a Result above Sort, which is expected from postponing additional non-sort outputs. Patch attached. Comments welcome. -- Best regards, Chengpeng Yan
v1-0001-planner-postpone-some-non-sort-output-expressions.patch
Description: v1-0001-planner-postpone-some-non-sort-output-expressions.patch
