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

From Ashutosh Bapat
Subject Re: Reuse child_relids in try_partitionwise_join was Re: Assert failure on bms_equal(child_joinrel->relids, child_joinrelids)
Date
Msg-id CAExHW5vL=0zHArP7z_+UkwZP972Qdr40b47uF4pS2soj9wmq3A@mail.gmail.com
Whole thread Raw
In response to Re: Reuse child_relids in try_partitionwise_join was Re: Assert failure on bms_equal(child_joinrel->relids, child_joinrelids)  (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>)
List pgsql-hackers


On Wed, Jun 5, 2024 at 1:17 PM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
Hi David,
Thanks for looking into this.

On Fri, May 31, 2024 at 2:19 AM David Christensen <david+pg@pgguru.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

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Fix use of possible uninitialized variable retval (src/pl/plpgsql/src/pl_handler.c)
Next
From: Masahiko Sawada
Date:
Subject: Re: Logical Replication of sequences