public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Lepikhov <[email protected]>
To: Yuya Watari <[email protected]>
Cc: Thom Brown <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Zhang Mingli <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Subject: Re: [PoC] Reducing planning time when tables have many partitions
Date: Fri, 28 Jul 2023 11:27:40 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAJ2pMkZwp545Fj=vyiyy=j=zPAEisb1=72PAU6vwyNc=Nx9vpA@mail.gmail.com>
<[email protected]>
<CAApHDvqoUdEMFPh_E9G4SW9615X2EWG1-nc-rvP3QSeuxseNKQ@mail.gmail.com>
<CAJ2pMkY10J_PA2jpH5M-VoOo6BvJnTOO_-t_znu_pOaP0q10pA@mail.gmail.com>
<CAJ2pMkbsP4f4SvUx+GguQ1BaA8oVo4BfcLvy6--c3QqQcB8PAQ@mail.gmail.com>
<[email protected]>
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 <[email protected]>
>> 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=a8c09daa8bb1d741bb8b3d31a12752448eb6f...
>>
> 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
Attachments:
[image/svg+xml] 2-Yuya-kernel.svg (614.4K, ../[email protected]/2-2-Yuya-kernel.svg)
download | view image
view thread (29+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PoC] Reducing planning time when tables have many partitions
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox