Re: Maintain the pathkesy for subquery from outer side information

On Sat, Jul 24, 2021 at 10:14 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:
> > When I am working on the UnqiueKey stuff, I find the following cases.
> > SELECT * FROM (SELECT * FROM t offset 0) v ORDER BY a;
> > // root->query_keys = A. root->order_pathkeys = A
> > // Current: subroot->query_pathkeys = NIL.
> > // Expected: subroot->xxxx_pathkeys = [A].
>
> Why do you "expect" that? I think pushing the outer ORDER BY past a
> LIMIT is an unacceptable semantics change.
>
> regards, tom lane

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.

--
Best Regards
Andy Fan (https://www.aliyun.com/)