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

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Yuya Watari <watari(dot)yuya(at)gmail(dot)com>
Cc: Thom Brown <thom(at)linux(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Zhang Mingli <zmlpostgres(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: [PoC] Reducing planning time when tables have many partitions
Date: 2023-07-28 04:27:40
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(dot)yuya(at)gmail(dot)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]
> 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.

Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
2-Yuya-kernel.svg image/svg+xml 614.4 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2023-07-28 04:45:29 Re: Support worker_spi to execute the function dynamically.
Previous Message Alexander Lakhin 2023-07-28 04:00:01 Re: Postgres v15 windows bincheck regression test failures