Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning

From: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
To: Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, tomas(at)vondra(dot)me, vignesh C <vignesh21(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Richard Guo <guofenglinux(at)gmail(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning
Date: 2025-03-17 08:47:33
Message-ID: CAExHW5sfiDQ4kOmva_feXg2z9Jnk5LssFvr0SgD-pN8fUKqAHQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, Mar 14, 2025 at 5:36 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:

>
> Thanks for the patch and the extensive benchmarking.
>
> Would you please also share a simple, self-contained example that I
> can use to reproduce and verify the performance improvements? It’s
> helpful to first see the patch in action with a representative query,
> and then refer to the more extensive benchmark results you've already
> shared as needed. I'm also having a hard time telling from the scripts
> which query was used to produce the numbers in the report.
>

Here are steps
1. Run setup.sql attached in [1]. It will add a few helper functions
and create required tables (partitioned and non-partitioned ones).
2. The sheet named "rae data" attached to [1] has queries whose
performance is measured. They use the tables created by setup.sql.

You may further use the same scripts to run benchmarks.

> Btw, in the commit message, you mention:
>
> ===
> When there are thousands of partitions in a partitioned table, there
> can be thousands of derived clauses in the list making it inefficient
> for a lookup.
> ===
>
> I haven’t found a description of how that many clauses end up in the
> ec->ec_derived list. IIUC, it's create_join_clause() where the child
> clauses get added, and it would be helpful to mention that, since that
> also appears to be the hotspot your patch is addressing.

you are right. create_join_clause() adds the derived clauses for
partitions. Please note that the optimization, being modelled after
join rel list, is applicable to partitioned, non-partititoned cases as
well as with or without partitionwise join.

>
> > I think the first patch in the attached set is worth committing, just
> > to tighten things up.
>
> I agree and am happy to commit it if there are no objections.

Thanks.

[1] https://www.postgresql.org/message-id/CAExHW5vnwgTgfsCiNM7E4TnkxD1b_ZHPafNe1f041u=o131PYg@mail.gmail.com

--
Best Wishes,
Ashutosh Bapat

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2025-03-17 09:03:37 Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET
Previous Message Álvaro Herrera 2025-03-17 08:44:20 Re: pg_stat_statements and "IN" conditions