public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashutosh Bapat <[email protected]>
To: David Rowley <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Memory consumed by paths during partitionwise join planning
Date: Thu, 14 Dec 2023 17:34:07 +0530
Message-ID: <CAExHW5uDkMQL8SicV3_=AYcsWwMTNR8GzJo310J7sv7TMLoL6Q@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvrc4UoP=E+zzVdQRgC0gJ37+W_zw=3wjnLkXJhvToZ50A@mail.gmail.com>
References: <CAExHW5tUcVsBkq9qT=L5vYz4e-cwQNw=KAGJrtSyzOp3F=XacA@mail.gmail.com>
<CAApHDvoDc4QwxMH4h_WRjJO4NK47L_gafv0oTpyjAkXKyXFz_A@mail.gmail.com>
<CAExHW5u-X+7xj2udyXSxqPhqyLDz99o_Ev6YoPViZX5tT0Uquw@mail.gmail.com>
<CAApHDvrc4UoP=E+zzVdQRgC0gJ37+W_zw=3wjnLkXJhvToZ50A@mail.gmail.com>
On Thu, Dec 7, 2023 at 6:19 PM David Rowley <[email protected]> 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/[email protected]...
--
Best Wishes,
Ashutosh Bapat
view thread (14+ 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]
Subject: Re: Memory consumed by paths during partitionwise join planning
In-Reply-To: <CAExHW5uDkMQL8SicV3_=AYcsWwMTNR8GzJo310J7sv7TMLoL6Q@mail.gmail.com>
* 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