|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On 27/7/2023 14:58, Andrey Lepikhov wrote:
> On 5/7/2023 16:57, Yuya Watari wrote:
>> On Fri, Mar 10, 2023 at 5:38 PM Yuya Watari <watari(dot)yuya(at)gmail(dot)com>
>>> Thank you for pointing it out. I have attached the rebased version to
>>> this email.
>> Recent commits, such as a8c09daa8b , 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.
> 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
> 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.
|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|