Re: Maintain the pathkesy for subquery from outer side information - Mailing list pgsql-hackers

On Sat, Jul 24, 2021 at 10:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andy Fan <zhihui.fan1213@gmail.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/)