Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning - Mailing list pgsql-hackers

On Mon, Mar 17, 2025 at 5:47 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> On Fri, Mar 14, 2025 at 5:36 PM Amit Langote <amitlangote09@gmail.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.

Thanks for pointing me to that.

I ran a couple of benchmarks of my own as follows.

cat benchmark_amit.sh
for p in 0 16 32 64 128 256 512 1024; do
  echo -ne "$p\t";
  pgbench -i --partitions=$p > /dev/null 2>&1
  pgbench -n -T 30 -f /tmp/query.sql | grep latency | awk '{print $4}';
done

For a 3-way join:
cat /tmp/query.sql
select * from pgbench_accounts t1, pgbench_accounts t2,
pgbench_accounts t3 where t2.aid = t1.aid AND t3.aid = t2.aid;

nparts  master      patched     %change
0       35.508      36.066      1.571
16      66.79       67.704      1.368
32      67.774      68.179      0.598
64      51.023      50.471     -1.082
128     56.4        55.759     -1.137
256     70.134      68.401     -2.471
512     120.621     113.552    -5.861
1024    405.339     312.726    -22.848

For a 6-way jon
cat /tmp/query.sql
select * from pgbench_accounts t1, pgbench_accounts t2,
pgbench_accounts t3, pgbench_accounts t4, pgbench_accounts t5,
pgbench_accounts t6 where t2.aid = t1.aid AND t3.aid = t2.aid and
t4.aid = t3.aid and t5.aid = t4.aid and t6.aid = t5.aid;

nparts  master      patched     %change
0       66.144      64.932     -1.832
16      100.874     100.491    -0.380
32      104.645     104.536    -0.104
64      114.415     109.193    -4.564
128     145.422     130.458    -10.290
256     273.761     209.919    -23.320
512     1359.896    616.295    -54.681
1024    7183.765    2857.086   -60.229

-60% means 60% reduction in latency due to the patch.

As others have already found, performance improvements become
substantial as both partition count and join depth increase. The patch
seems to have minimal impact at low partition counts and low join
complexity -- base cases (e.g., 0–32 partitions, 3-way joins) are
essentially unchanged, which is good to see.

I haven’t measured memory increase from the patch, but let me know if
that has already been evaluated and shown not to be a showstopper.

I also noticed using perf that create_join_clause() is a hotspot when
running without the patch, especially at high partition counts (> 500)
and the more join relations.

Let me know if my methodology seems off or if the results look reasonable.

--
Thanks, Amit Langote