From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Maintain the pathkesy for subquery from outer side information |
Date: | 2021-07-24 13:14:42 |
Message-ID: | CAKU4AWrE750ruYbHG39oqHw7xChwVVk93VCFFdK9Qo=i03bgrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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/)
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2021-07-24 14:01:05 | Re: visibility map corruption |
Previous Message | Andrey Borodin | 2021-07-24 10:53:15 | Re: Avoiding data loss with synchronous replication |