Message-ID: <182fbad0-f3f6-4692-8fc4-778378e1db01@postgrespro.ru>
Date: Fri, 16 Feb 2024 10:12:47 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Memory consumed by paths during partitionwise join planning
Content-Language: en-US
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
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>
 <c11abcfa-cdbf-4883-adbd-c09c6bc74b0d@postgrespro.ru>
 <CAExHW5vqfg8J-Oe4AOaUnBLz4rWhbJGDKFJHBDDEQ907Om53yg@mail.gmail.com>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CAExHW5vqfg8J-Oe4AOaUnBLz4rWhbJGDKFJHBDDEQ907Om53yg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/182fbad0-f3f6-4692-8fc4-778378e1db01%40postgrespro.ru>
Precedence: bulk

On 15/2/2024 19:06, Ashutosh Bapat wrote:
> On Thu, Feb 15, 2024 at 9:41 AM Andrei Lepikhov
>> But I'm not sure about freeing unreferenced paths. I would have to see
>> alternatives in the pathlist.
> 
> I didn't understand this. Can you please elaborate? A path in any
> pathlist is referenced. An unreferenced path should not be in any
> pathlist.
I mean that at some point, an extension can reconsider the path tree 
after building the top node of this path. I vaguely recall that we 
already have (or had) kind of such optimization in the core where part 
of the plan changes after it has been built.
Live example: right now, I am working on the code like MSSQL has - a 
combination of NestLoop and HashJoin paths and switching between them in 
real-time. It requires both paths in the path list at the moment when 
extensions are coming. Even if one of them isn't referenced from the 
upper pathlist, it may still be helpful for the extension.

>> About partitioning. As I discovered planning issues connected to
>> partitions, the painful problem is a rule, according to which we are
>> trying to use all nomenclature of possible paths for each partition.
>> With indexes, it quickly increases optimization work. IMO, this can help
>> a 'symmetrical' approach, which could restrict the scope of possible
>> pathways for upcoming partitions if we filter some paths in a set of
>> previously planned partitions.
> 
> filter or free?
Filter.
I meant that Postres tries to apply IndexScan, BitmapScan, 
IndexOnlyScan, and other strategies, passing throughout the partition 
indexes. The optimizer spends a lot of time doing that. So, why not 
introduce a symmetrical strategy and give away from the search some 
indexes of types of scan based on the pathifying experience of previous 
partitions of the same table: if you have dozens of partitions, Is it 
beneficial for the system to find a bit more optimal IndexScan on one 
partition having SeqScans on 999 other?

-- 
regards,
Andrei Lepikhov
Postgres Professional