On Wed, 27 Mar 2024 at 06:20, Tom Lane <t...@sss.pgh.pa.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
