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

Hi David,

 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%

The patch calls find_appinfos_by_relids() only once (instead of twice), calls bms_union() on child relids only once, allocates and deallocates appinfos array only once saving some CPU cycles but spends some CPU cycles in bms_free(). There's expected to be a net small saving in planning time. But above numbers show planning time increases in some cases and decreases in some cases. Repeating the experiment multiple times does not show a stable increase or decrease. But the variations all being within noise levels. We could conclude that the patch does not affect planning time adversely.

The scripts I used originally [1] need a revision too since EXPLAIN MEMORY output has changed. PFA the scripts. setup.sql creates the required functions and tables. queries.sql EXPLAINs queries with different number of joining relations collecting planning time and memory numbers in a table (msmts). We need to change psql variable code to 'patched' or 'master' when using master + patches or master branch respectively. Following queries are used to report above measurements from msmt.

planning memory: select p.num_joins, pg_size_pretty(m.average * 1024) "master", pg_size_pretty(p.average * 1024) as "patched", pg_size_pretty((m.average - p.average) * 1024) "memory saved", ((m.average - p.average) * 100/m.average)::numeric(42, 2) "memory saved in percentage" from msmts p, msmts m where p.num_joins = m.num_joins and p.variant = m.variant and p.code = 'patched' and m.code = 'master' and p.measurement = m.measurement and p.measurement = 'planning memory' and p.variant = 'pwj' order by num_joins;

planning time: select p.num_joins, m.average "master (ms)", p.average "patched (ms)", m.average - p.average "change in planning time (ms)", ((m.average - p.

[1] https://www.postgresql.org/message-id/CAExHW5snUW7pD2RdtaBa1T_TqJYaY6W_YPVjWDrgSf33i-0uqA%40mail.gmail.com