Re: Memory consumed by paths during partitionwise join planning

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