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

On Wed, Jun 5, 2024 at 1:17 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> Hi David,
> Thanks for looking into this.
>
> On Fri, May 31, 2024 at 2:19 AM David Christensen <david+pg(at)pgguru(dot)net>
> wrote:
>
>> Hello,
>>
>> I am looking through some outstanding CommitFest entries; I wonder if
>> this particular patch is already effectively fixed by 5278d0a2, which
>> is both attributed to the original author as well as an extremely
>> similar approach. Can this entry
>> (https://commitfest.postgresql.org/48/4553/) be closed?
>>
>
> This is different. But it needs a rebase. PFA rebased patch. The revised
> commit message explains the change better.
>
> Here are numbers revised after 5278d0a2. Since the code changes only
> affect partitionwise join code path, I am reporting only partition wise
> join numbers. The first column reports the number of joining relations,
> each having 1000 partitions. Rest of the column names are self-explanatory.
>
> Planning memory used:
> num_joins | master | patched | memory saved | memory saved
> -----------+---------+---------+--------------+----------------------------
> 2 | 31 MB | 30 MB | 525 kB | 1.68%
> 3 | 111 MB | 107 MB | 4588 kB | 4.03%
> 4 | 339 MB | 321 MB | 17 MB | 5.13%
> 5 | 1062 MB | 967 MB | 95 MB | 8.96%
>
> 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%
>

Here are some numbers with lower number of partitions per relation
For 100 partitions
Planning memory:
num_joins | master | patched | memory saved | memory saved in percentage
-----------+---------+---------+---------------+----------------------------
2 | 2820 kB | 2812 kB | 8192.00 bytes | 0.28%
3 | 9335 kB | 9270 kB | 65 kB | 0.70%
4 | 27 MB | 27 MB | 247 kB | 0.90%
5 | 78 MB | 77 MB | 1101 kB | 1.37%

Planning time:
num_joins | master (ms) | patched (ms) | change in planning time (ms) |
change in planning time as percentage
-----------+-------------+--------------+------------------------------+---------------------------------------
2 | 3.33 | 3.21 | 0.12 |
3.60%
3 | 11.40 | 11.17 | 0.23 |
2.02%
4 | 37.61 | 37.56 | 0.05 |
0.13%
5 | 124.39 | 126.08 | -1.69 |
-1.36%

For 10 partitions
Planning memory:
num_joins | master | patched | memory saved | memory saved in percentage
-----------+---------+---------+---------------+----------------------------
2 | 310 kB | 310 kB | 0.00 bytes | 0.00
3 | 992 kB | 989 kB | 3072.00 bytes | 0.30
4 | 2891 kB | 2883 kB | 8192.00 bytes | 0.28
5 | 8317 kB | 8290 kB | 27 kB | 0.32

Planning time:
num_joins | master (ms) | patched (ms) | change in planning time (ms) |
change in planning time as percentage
-----------+-------------+--------------+------------------------------+---------------------------------------
2 | 0.42 | 0.37 | 0.05 |
11.90
3 | 1.08 | 1.09 | -0.01 |
-0.93
4 | 3.56 | 3.32 | 0.24 |
6.74
5 | 8.86 | 8.78 | 0.08 |
0.90

Observations:
1. As expected the memory savings are smaller for a small number of
partitions, but they are not negative. Thus the patch helps in case of a
large number of partitions but does not adversely affect a small number of
partitions
2. Planning time changes are within noise. But usually I see that the
planning time varies a lot. Thus the numbers here just indicate that the
planning times are not adversely affected by the change. Theoretically, I
would expect the patch to improve planning time as explained in the earlier
email.
3. As mentioned in the earlier email, the patch affects only the
partitionwise join code path hence I am reporting numbers with
partitionwise join enabled.

--
Best Wishes,
Ashutosh Bapat