Re: [PoC] Reducing planning time when tables have many partitions

On Mon, Mar 24, 2025 at 11:08 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 2. You should use list_free() in the following:
>
> /*
> * XXX Should we use list_free()? I decided to use this style to take
> * advantage of speculative execution.
> */
> if (unlikely(it->list_is_copy))
> pfree(it->ec_members);
>
> The reason is that you're wrongly assuming that calling pfree on the
> List pointer is enough to get rid of all memory used by the list. The
> List may have a separately allocated elements[] array (this happens
> when there's > 5 elements) which you're leaking with the current code.
>
> I assume the speculative execution comment is there because you want
> to omit the "list == NULL" check in list_free_private. Is this
> measurable, performance-wise?
>

Here are memory consumption numbers using list_free() instead of pfree(),
using the same method as [1], using a binary without asserts and debug
info. PFA the patchset where all the patches are the same as v35 but with
an extra patch fixing memory leak. The memory leak is visible with a higher
number of joins. At a lower number of joins, I expect that the memory saved
is less than a KB or the leaked memory fits within 1 chunk of memory
context and hence not visible.

rows by number of partitions
columns by number of joins
each cell is a triplet, s = memory saving in %, mm - memory consumed
without fix, pm = memory consumed with fix
with PWJ = off
num_parts | 2 | 3
| 4 | 5
-----------+---------------------------------+---------------------------------+---------------------------------+---------------------------------
0 | s=0.00%, mm=15 kB, pm=15 kB | s=0.00%, mm=21 kB, pm=21 kB
| s=0.00%, mm=27 kB, pm=27 kB | s=0.00%, mm=34 kB, pm=34 kB
10 | s=0.00%, mm=231 kB, pm=231 kB | s=0.00%, mm=485 kB, pm=485
kB | s=0.00%, mm=924 kB, pm=924 kB | s=2.21%, mm=1901 kB, pm=1859 kB
100 | s=0.00%, mm=1965 kB, pm=1965 kB | s=0.00%, mm=4082 kB, pm=4082
kB | s=0.00%, mm=7115 kB, pm=7115 kB | s=3.35%, mm=12 MB, pm=12 MB
500 | s=0.00%, mm=10 MB, pm=10 MB | s=0.00%, mm=23 MB, pm=23 MB
| s=0.00%, mm=42 MB, pm=42 MB | s=2.58%, mm=80 MB, pm=78 MB
1000 | s=0.00%, mm=22 MB, pm=22 MB | s=0.00%, mm=55 MB, pm=55 MB
| s=0.00%, mm=107 MB, pm=107 MB | s=1.97%, mm=209 MB, pm=205 MB

without PWJ = on
num_parts | 2 | 3
| 4 | 5
-----------+---------------------------------+---------------------------------+---------------------------------+---------------------------------
0 | s=0.00%, mm=15 kB, pm=15 kB | s=0.00%, mm=21 kB, pm=21 kB
| s=0.00%, mm=27 kB, pm=27 kB | s=0.00%, mm=34 kB, pm=34 kB
10 | s=0.00%, mm=379 kB, pm=379 kB | s=0.00%, mm=1228 kB, pm=1228
kB | s=0.00%, mm=3628 kB, pm=3628 kB | s=0.40%, mm=10 MB, pm=10 MB
100 | s=0.00%, mm=3478 kB, pm=3478 kB | s=0.00%, mm=11 MB, pm=11 MB
| s=0.00%, mm=34 MB, pm=34 MB | s=0.41%, mm=99 MB, pm=99 MB
500 | s=0.00%, mm=18 MB, pm=18 MB | s=0.00%, mm=62 MB, pm=62 MB
| s=0.00%, mm=186 MB, pm=186 MB | s=0.37%, mm=564 MB, pm=562 MB
1000 | s=0.00%, mm=38 MB, pm=38 MB | s=0.00%, mm=139 MB, pm=139
MB | s=0.00%, mm=420 MB, pm=420 MB | s=0.32%, mm=1297 MB, pm=1293 MB

But overall the patches consume more memory than before as seen from
measurements below
Each cell is a triplet (s, mm, pm) where s = memory saving in % (-ve
indicates that memory consumption has increased), mm = memory consumption
with no patches applied, pm = memory consumption with all patches applied
PWJ=off
num_parts | 2 | 3
| 4 | 5

-----------+----------------------------------+----------------------------------+-----------------------------------+----------------------------------
0 | s=0.00%, mm=15 kB, pm=15 kB | s=0.00%, mm=21 kB, pm=21 kB
| s=0.00%, mm=27 kB, pm=27 kB | s=-3.03%, mm=33 kB, pm=34 kB
10 | s=-5.96%, mm=218 kB, pm=231 kB | s=-6.59%, mm=455 kB, pm=485
kB | s=-6.45%, mm=868 kB, pm=924 kB | s=-9.55%, mm=1697 kB, pm=1859 kB
100 | s=-7.73%, mm=1824 kB, pm=1965 kB | s=-9.79%, mm=3718 kB,
pm=4082 kB | s=-11.17%, mm=6400 kB, pm=7115 kB | s=-19.04%, mm=10233 kB,
pm=12 MB
500 | s=-10.91%, mm=9395 kB, pm=10 MB | s=-16.99%, mm=20 MB, pm=23
MB | s=-21.14%, mm=35 MB, pm=42 MB | s=-31.14%, mm=59 MB, pm=78 MB
1000 | s=-14.33%, mm=19 MB, pm=22 MB | s=-23.95%, mm=45 MB, pm=55
MB | s=-29.77%, mm=82 MB, pm=107 MB | s=-40.45%, mm=146 MB, pm=205 MB

PWJ=on
num_parts | 2 | 3
| 4 | 5
-----------+----------------------------------+----------------------------------+----------------------------------+----------------------------------
0 | s=0.00%, mm=15 kB, pm=15 kB | s=0.00%, mm=21 kB, pm=21 kB
| s=0.00%, mm=27 kB, pm=27 kB | s=-3.03%, mm=33 kB, pm=34 kB
10 | s=-3.84%, mm=365 kB, pm=379 kB | s=-2.50%, mm=1198 kB,
pm=1228 kB | s=-1.60%, mm=3571 kB, pm=3628 kB | s=-1.55%, mm=10 MB, pm=10 MB
100 | s=-4.23%, mm=3337 kB, pm=3478 kB | s=-3.25%, mm=11 MB, pm=11
MB | s=-2.11%, mm=33 MB, pm=34 MB | s=-1.96%, mm=97 MB, pm=99 MB
500 | s=-5.96%, mm=17 MB, pm=18 MB | s=-5.71%, mm=59 MB, pm=62
MB | s=-4.12%, mm=179 MB, pm=186 MB | s=-3.40%, mm=544 MB, pm=562 MB
1000 | s=-7.88%, mm=35 MB, pm=38 MB | s=-8.33%, mm=128 MB, pm=139
MB | s=-6.19%, mm=395 MB, pm=420 MB | s=-4.79%, mm=1234 MB, pm=1293 MB

In the case of PWJ = on, the % wise memory consumption is less because
memory consumption without fixes is huge and the patch adds on top of it.
But without PWJ, the memory consumption is high, especially at higher
number of joins and higher number of partitions.

[1]
https://www.postgresql.org/message-id/CAExHW5vNk4f8VSTnizL-avREYhPgocEtH3E7MCEJgp=R33ChyQ@mail.gmail.com

--
Best Wishes,
Ashutosh Bapat