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

FWIW here's patch set

On Thu, Mar 27, 2025 at 10:12 AM Ashutosh Bapat <
ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:

>
>
> 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
>

--
Best Wishes,
Ashutosh Bapat