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].
SELECT * FROM (SELECT * FROM t offset 0) v, t2 WHERE t2.a = t.a; // root->query_keys = NIL // Current: subroot->query_keys = NIL // Expected: subroot->xxx_pathkeys = A To resolve this issue, I want to add a root->outer_pathkeys which means the interesting order from the outer side for a subquery. To cover the cases like below // root->query_keys = root->order_keys = b. // Expected: subroot->xxx_pathkeys = (a)? (b)? SELECT * FROM (SELECT * FROM t offset 0) v, t2 WHERE t2.a = t.a order by v.b; the root->outer_pathkeys should be a list of lists. in above case subroot->outer_pathkeys should be [ [a], [b] ], this value may be checked at many places, like pathkeys_useful_for_ordering, get_useful_pathkeys_for_relation, build_index_paths and more. My list might be incomplete, but once we have a new place to check and the data is maintained already, it would be easy to improve. My thinking is we maintain the root->outer_pathkeys first, and then improve the well known function as the first step. What do you think? -- Best Regards Andy Fan (https://www.aliyun.com/)
