Re: Maintain the pathkesy for subquery from outer side information

Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
> On Sat, Jul 24, 2021 at 10:14 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Why do you "expect" that? I think pushing the outer ORDER BY past a
>> LIMIT is an unacceptable semantics change.

> I don't mean push down a "ORDER BY" clause to subquery, I mean push
> down an "interesting order" to subquery. for example we have index t(a);
> then SELECT * FROM (SELECT a FROM t OFFSET 0) v ORDER BY a;
> In the current implementation, when we are planning the subuqery, planners
> think the "pathkey a" is not interesting, but it should be IIUC.

No, it should not be.

(1) We have long treated "OFFSET 0" as an optimization fence. That means
that the outer query shouldn't affect what plan you get for the subquery.

(2) If you ignore point (1), you could argue that choosing a different
result order doesn't matter for this subquery. However, it potentially
*does* matter for a large fraction of the cases in which we'd not have
flattened the subquery into the outer query. In subqueries involving
things like volatile functions, aggregates, window functions, etc,
encouraging the sub-planner to pick a different result ordering could
lead to visibly different output.

I think that in cases where there's not a semantic hazard involved,
we'd usually have pulled up the subquery so that this is all moot
anyway.

regards, tom lane