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-28T04:27:40Z
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 27/7/2023 14:58, Andrey Lepikhov wrote:
> 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.
Sorry for this. It was definitely a false alarm. In this patch, 
assertion checking adds much overhead. After switching it off, I found 
out that this feature solves my problem with a quick pass through the 
members of an equivalence class. Planning time results for the queries 
from the previous letter:
1 - 0.4s, 2 - 1.3s, 3 - 1.3s; (with the patches applied)
1 - 5s; 2 - 8.7s; 3 - 22s; (current master).

I have attached flamegraph that shows query 2 planning process after 
applying this set of patches. As you can see, overhead at the 
equivalence class routines has gone.

-- 
regards,
Andrey Lepikhov
Postgres Professional