Horton

Re: [PoC] Reducing planning time when tables have many partitions

Yuya Watari <watari.yuya@gmail.com>

View thread
From: Yuya Watari <watari.yuya@gmail.com>
To: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Cc: jian he <jian.universality@gmail.com>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Alena Rybakina <lena.ribackina@yandex.ru>, Andrei Lepikhov <a.lepikhov@postgrespro.ru>, David Rowley <dgrowleyml@gmail.com>, Thom Brown <thom@linux.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Zhang Mingli <zmlpostgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2024-08-29T05:34:46Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Update wording in optimizer/README for EquivalenceClasses

  2. Speedup child EquivalenceMember lookup in planner

  3. Allow planner to use Merge Append to efficiently implement UNION

  4. Remove trailing zero words from Bitmapsets

  5. Make Vars be outer-join-aware.

  6. Avoid making commutatively-duplicate clauses in EquivalenceClasses.

Attachments

Hello,

On Thu, May 16, 2024 at 11:44 AM Yuya Watari <watari.yuya@gmail.com> wrote:
> I'm sorry it will take some time to solve
> this conflict, but I will post a new version when it is fixed.

The previous patches no longer apply to the master, so I rebased them.
I'm sorry for the delay.

I will summarize the patches again.

1. v25-0001
This patch is one of the main parts of my optimization. Traditionally,
EquivalenceClass has both parent and child members. However, this
leads to high iteration costs when there are many child partitions. In
v25-0001, EquivalenceClasses no longer have child members. If we need
to iterate over child EquivalenceMembers, we use the
EquivalenceChildMemberIterator and access the children through the
iterator. For more details, see [1] (note that there are some design
changes from [1]).

2. v25-0002
This patch was made in the previous work with David. Like
EquivalenceClass, there are many RestrictInfos in highly partitioned
cases. This patch introduces an indexing mechanism to speed up
searches for RestrictInfos.

3. v25-0003
v25-0002 adds its indexes to RangeTblEntry, but this is not a good
idea. RelOptInfo is the best place. This problem is a workaround
because some RelOptInfos can be NULL, so we cannot store indexes to
such RelOptInfos. v25-0003 moves the indexes from RangeTblEntry to
PlannerInfo. This is still a workaround, and I think it should be
reconsidered.

4. v25-0004
After our changes, add_eq_member() no longer creates and adds child
EquivalenceMembers. This commit renames it to add_parent_eq_member()
to clarify that it only creates parent members, and that we need to
use make_eq_member() to handle child EquivalenceMembers.

5. v25-0005
This commit resolves a conflict with commit 66c0185 [2], which adds
add_setop_child_rel_equivalences(). As I mentioned in the previous
email [3], this function creates child EquivalenceMembers by calling
add_eq_member(). This commit adjusts our optimization so that it can
handle such child members.

[1] https://www.postgresql.org/message-id/CAJ2pMkZk-Nr%3DyCKrGfGLu35gK-D179QPyxaqtJMUkO86y1NmSA%40mail.gmail.com
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=66c0185a3
[3] https://www.postgresql.org/message-id/CAJ2pMkZt6r94NUYm-F77FYahjgnMrY4CLHGAD7HxYZxGVwCaow%40mail.gmail.com

-- 
Best regards,
Yuya Watari