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-27 07:58:16
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
parts-problem.sql application/sql 1.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message David Steele 2023-07-27 08:18:47 Re: odd buildfarm failure - "pg_ctl: control file appears to be corrupt"
Previous Message Amit Kapila 2023-07-27 06:54:19 Re: Synchronizing slots from primary to standby