Re: Reuse child_relids in try_partitionwise_join was Re: Assert failure on bms_equal(child_joinrel->relids, child_joinrelids)

On Thu, Jun 6, 2024 at 10:00 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Jun 5, 2024 at 3:48 AM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > Here's planning time measurements.
> > num_joins | master (ms) | patched (ms) | change in planning time (ms) |
> change in planning time
> >
> -----------+-------------+--------------+------------------------------+---------------------------------------
> > 2 | 187.86 | 177.27 | 10.59
> | 5.64%
> > 3 | 721.81 | 758.80 | -36.99
> | -5.12%
> > 4 | 2239.87 | 2236.19 | 3.68
> | 0.16%
> > 5 | 6830.86 | 7027.76 | -196.90
> | -2.88%
>
> I find these results concerning. Why should the planning time
> sometimes go up? And why should it go up for odd numbers of joinrels
> and down for even numbers of joinrels? I wonder if these results are
> really correct, and if they are, I wonder what could account for such
> odd behavior
>

This is just one instance of measurements. If I run the experiment multiple
times the results and the patterns will vary. Usually I have found planning
time to vary within 5% for regular tables and within 9% for partitioned
tables with a large number of partitions. Below are measurements with the
experiment repeated multiple times. For a given number of partitioned
tables (each with 1000 partitions) being joined, planning time is measured
10 consecutive times. For this set of 10 runs we calculate average and
standard deviation of planning time. Such 10 sets are sampled. This means
planning time is sampled 100 times in total with and without patch
respectively. Measurements with master and patched are reported in the
attached excel sheet.

Observations from spreadsheet
1. Differences in planning time with master or patched version are within
standard deviation. The difference is both ways. Indicating that the patch
doesn't affect planning time adversely.
2. If we just look at the 5-way join numbers, there are more +ve s in
column E and sometimes higher than standard deviation. Indicating that the
patch improves planning time when there are higher number of partitioned
tables being joined.
3. If we just look at the 5-way join numbers, the patch seems to reduce the
standard deviation (column B vs column D) indicating that the planning time
is more stable and predictable with patched version.
3. There is no bias towards even or odd numbers of partitioned tables being
joined.

Looking at the memory savings numbers reported earlier, the patch improves
memory consumption of partitionwise join without adversely affecting
planning time. It doesn't affect the code paths which do not use
partitionwise join. That's overall net positive impact for relatively less
code churn.

--
Best Wishes,
Ashutosh Bapat