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

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]
>> 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