Horton

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

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: Yuya Watari <watari.yuya@gmail.com>
Cc: Thom Brown <thom@linux.com>, David Rowley <dgrowleyml@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Zhang Mingli <zmlpostgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2023-07-27T07:58:16Z
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

On 5/7/2023 16:57, Yuya Watari wrote:
> Hello,
> 
> On Fri, Mar 10, 2023 at 5:38 PM Yuya Watari <watari.yuya@gmail.com> wrote:
>> Thank you for pointing it out. I have attached the rebased version to
>> this email.
> 
> Recent commits, such as a8c09daa8b [1], have caused conflicts and
> compilation errors in these patches. I have attached the fixed version
> to this email.
> 
> The v19-0004 adds an 'em_index' field representing the index within
> root->eq_members of the EquivalenceMember. This field is needed to
> delete EquivalenceMembers when iterating them using the ec_members
> list instead of the ec_member_indexes.
> 
> [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a8c09daa8bb1d741bb8b3d31a12752448eb6fb7c
> 
Discovering quality of partition pruning at the stage of execution 
initialization and using your set of patches I have found some dubious 
results with performance degradation. Look into the test case in attachment.
Here is three queries. Execution times:
1 - 8s; 2 - 30s; 3 - 131s (with your patch set).
1 - 5s; 2 - 10s; 3 - 33s (current master).

Maybe it is a false alarm, but on my laptop I see this degradation at 
every launch.

-- 
regards,
Andrey Lepikhov
Postgres Professional