On Wed, Jun 5, 2024 at 3:48 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.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