Enforce Sorted paths on the scan from the query_pathkeys prefix

[danolivo]

Details

Branch: https://github.com/danolivo/pgdev/tree/enforce-presorted-scan-on-query-pathkeys
Description: https://www.notion.so/Push-Sort-into-the-outer-of-OUTER-JOIN-329ca5f34df28038904fd0afbbe4daf8?source=copy_link

Idea

If we have ORDER BY a.x (maybe GROUP BY too?) clause, it may happen that a forced sorted path on the a ' table may improve performance drastically. In case the Scan A` happens on the outer side of a LEFT JOIN.

NOTE: There may be additional improvements to observe: MergeJoin uses indexes on the inner side more effectively in this case.

[danolivo]

Task: we need to pick the most optimal scan path, not just a SeqScan one

[danolivo]

"relation "%s" does not exist"