Re: Propagate pathkeys from CTEs up to the outer query

On Wed, 27 Mar 2024 at 06:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> That's not the fault of anything we did here; the IndexOnlyScan path
> in the subquery is in fact not marked with any pathkeys, even though
> clearly its result is sorted. I believe that's an intentional
> decision from way way back, that pathkeys only correspond to orderings
> that are of interest in the current query level. "select unique1 from
> tenk1 b order by unique1" has an interest in ordering by unique1,
> but "select unique1 from tenk1 b" does not, so it's choosing that
> path strictly according to cost. Not generating pathkeys in such a
> query saves a few cycles and ensures that we won't improperly prefer
> a path on the basis of pathkeys if it hasn't got a cost advantage.
> So I'm quite hesitant to muck with that old decision, especially in
> the waning days of a development cycle, but the results do feel a
> little strange here.

I agree that add_path() might start making questionable choices if we
were to include unrelated pathkeys in a path. However, I don't think
it would be a bad idea to give a subquery a bit more context about
where it's running and which orders might be useful for the outer
query.

What's been reported in [1] I think could be solved by giving the
planner some way to tell subqueries what pathkeys are useful to the
outer query.

David

[1] https://www.postgresql.org/message-id/242fc7c6-a8aa-2daf-ac4c-0a231e2619c1%40gmail.com