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/)