Re: [PoC] Reducing planning time when tables have many partitions - Mailing list pgsql-hackers

On Mon, Mar 24, 2025 at 11:08 AM David Rowley <dgrowleyml@gmail.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