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

On Mon, Mar 17, 2025 at 5:47 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> 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.

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