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

On Wed, Mar 19, 2025 at 8:22 AM Amit Langote <amitlangote09@gmail.com> wrote:
>
> On Tue, Mar 18, 2025 at 8:48 PM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > On Tue, Mar 18, 2025 at 4:02 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > >
> > > 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 assume these are with enable_partitionwise_join = off since it's not
> > enabled in the query.
>
> Yes, those were with pwj=off.  FTR, numbers I get with pwj=on.
>
> 3-way:
>
> nparts  master      patched     %change
> 0       38.407      34.675      -9.717
> 16      69.357      64.312      -7.274
> 32      70.027      67.079      -4.210
> 64      73.807      70.725      -4.176
> 128     83.875      81.945      -2.301
> 256     102.858     106.19       3.239
> 512     181.95      180.891     -0.582
> 1024    464.503     440.355     -5.199
>
> 6-way:
>
> nparts  master      patched     %change
> 0       64.411      67.175       4.291
> 16      203.344     209.75       3.150
> 32      296.952     300.966      1.352
> 64      445.536     449.917      0.983
> 128     805.103     781.892     -2.883
> 256     1695.746    1574.346    -7.159
> 512     4743.16     4010.196    -15.453
> 1024    16772.454   12284.706   -26.757
>
> So a bit less impressive than the improvements for pwj=off.  Also,
> patch seems to make things worse for low partition counts (0-16) for
> 6-way joins, which I am not quite sure is within the noise range.
> Have you noticed that too and, if yes, do you know what might be
> causing it?

I have observed similar things with lower numbers of partitions. In my
observation, such results have coincided with relatively large
variance. With 0 partitions there is no difference in the code
behaviour irrespective of pwj ON or Off. Hence we don't expect any
variation in the numbers you posted yesterday and today when nparts =
0. But there it is. I have always observed that much variation in
planning time for one combination or the other.

With 6 -way join there will be 6 * 5 - 5 derived clauses in one
equivalence class, That's close to 32, which is the threshold to start
using a hash table. So some slight perturbation is expected around
that threshold. But given that it's lower than 32, that shouldn't
apply here.

--
Best Wishes,
Ashutosh Bapat