Re: Memory consumed by paths during partitionwise join planning

From: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
To: David Rowley <dgrowleyml(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Memory consumed by paths during partitionwise join planning
Date: 2023-12-14 23:52:54
Message-ID: CAExHW5s1ZX3HF2q9Tcmo9wxkfPp6pEdmjSySP1Whc=M5__mXrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Forgot to mention,

On Thu, Dec 14, 2023 at 5:34 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Thu, Dec 7, 2023 at 6:19 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> >
> > Maybe we can try to move forward with your refcount idea and see how
> > the performance looks. If that's intolerable then that might help us
> > decide on the next best alternative solution.
> >
>
> Here are performance numbers
>
> setup
>
> create table t1 (a integer primary key, b integer);
> create table t1_parted (a integer primary key, b integer) partition by range(a);
> create 1000 partitions of t1
>
> query (a five way self-join)
> select * from t1 a, t1 b, t1 c, t1 d, t1 e where a.a = b.a and b.a =
> c.a and c.a = d.a and d.a = e.a -- unpartitioned table case
> select * from t1_parted a, t1_parted b, t1_parted c, t1_parted d,
> t1_parted e where a.a = b.a and b.a = c.a and c.a = d.a and d.a =
> e.a; -- partitioned table case
>
> The numbers with patches attached to [1] with limitations listed in
> the email are thus
>
> Ran each query 10 times through EXPLAIN (SUMMARY) and averaged
> planning time with and without patch.
> unpartitioned case
> without patch: 0.25
> with patch: 0.19
> this improvement is probably misleading. The planning time for this
> query change a lot.
>
> partitioned case (without partitionwise join)
> without patch: 14580.65
> with patch: 14625.12
> % degradation: 0.3%
>
> partitioned case (with partitionwise join)
> without patch: 23273.69
> with patch: 23328.52
> % degradation: 0.2%
>
> That looks pretty small considering the benefits. What do you think?
>
> [1] https://www.postgresql.org/message-id/CAExHW5stmOUobE55pMt83r8UxvfCph+Pvo5dNpdrVCsBgXEzDQ@mail.gmail.com

If you want to experiment, please use attached patches. There's a fix
for segfault during initdb in them. The patches are still raw.

--
Best Wishes,
Ashutosh Bapat

Attachment Content-Type Size
0002-Basic-infrastructure-to-link-unlink-and-fre-20231215.patch text/x-patch 9.3 KB
0001-Report-memory-used-for-planning-a-query-in--20231215.patch text/x-patch 6.7 KB
0003-Actual-code-to-use-pathnode-referencing-inf-20231215.patch text/x-patch 15.9 KB
0004-Local-variables-pointing-to-path-node-used--20231215.patch text/x-patch 1.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sutou Kouhei 2023-12-15 00:53:05 Re: Make COPY format extendable: Extract COPY TO format implementations
Previous Message Thomas Munro 2023-12-14 22:56:25 Re: Simplify newNode()