Message-ID: <933001d6-65f7-46b7-9bfe-5f08df58beb7@gmail.com>
Date: Thu, 19 Sep 2024 12:48:40 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Memory consumed by paths during partitionwise join planning
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>,
 David Rowley <dgrowleyml@gmail.com>
References: <CAExHW5tUcVsBkq9qT=L5vYz4e-cwQNw=KAGJrtSyzOp3F=XacA@mail.gmail.com>
 <CAApHDvoDc4QwxMH4h_WRjJO4NK47L_gafv0oTpyjAkXKyXFz_A@mail.gmail.com>
 <CAExHW5u-X+7xj2udyXSxqPhqyLDz99o_Ev6YoPViZX5tT0Uquw@mail.gmail.com>
 <CAApHDvrc4UoP=E+zzVdQRgC0gJ37+W_zw=3wjnLkXJhvToZ50A@mail.gmail.com>
 <CAExHW5uDkMQL8SicV3_=AYcsWwMTNR8GzJo310J7sv7TMLoL6Q@mail.gmail.com>
 <CAExHW5s1ZX3HF2q9Tcmo9wxkfPp6pEdmjSySP1Whc=M5__mXrQ@mail.gmail.com>
 <CAExHW5uAdVev9CQYrdZ7w0xucqp2mYHPzcCx6EevegZVVgDbLQ@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CAExHW5uAdVev9CQYrdZ7w0xucqp2mYHPzcCx6EevegZVVgDbLQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/933001d6-65f7-46b7-9bfe-5f08df58beb7%40gmail.com>
Precedence: bulk

On 6/2/2024 13:51, Ashutosh Bapat wrote:
> On Fri, Dec 15, 2023 at 5:22 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> 
>>>
>>> That looks pretty small considering the benefits. What do you think?
>>>
>>> [1] https://www.postgresql.org/message-id/CAExHW5stmOUobE55pMt83r8UxvfCph+Pvo5dNpdrVCsBgXEzDQ@mail.gmail.com
>>
>> If you want to experiment, please use attached patches. There's a fix
>> for segfault during initdb in them. The patches are still raw.
> 
> First patch is no longer required. Here's rebased set
> 
> The patches are raw. make check has some crashes that I need to fix. I
> am waiting to hear whether this is useful and whether the design is on
> the right track.
I think this work is promising, especially in the scope of partitioning.
I've analysed how it works by basing these patches on the current 
master. You propose freeing unused paths after the end of the standard 
path search.
In my view, upper paths generally consume less memory than join and scan 
paths. This is primarily due to the limited options provided by Postgres 
so far.
At the same time, this technique (while highly useful in general) adds 
fragility and increases complexity: a developer needs to remember to 
link the path using the pointer in different places of the code.
So, maybe go the alternative way? Invent a subquery memory context and 
store all the path allocations there. It can be freed after setrefs 
finishes this subquery planning without pulling up this subquery.

-- 
regards, Andrei Lepikhov