Subject: Re: Memory consumed by paths during partitionwise join planning

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 15 Feb 2024 17:36:16 +0530

On Thu, Feb 15, 2024 at 9:41 AM Andrei Lepikhov
<[email-masked]> wrote:
>
> On 6/2/2024 19:51, Ashutosh Bapat wrote:
> > On Fri, Dec 15, 2023 at 5:22 AM Ashutosh Bapat
> > 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.
> Let me write words of opinion on that feature.
> I generally like the idea of a refcount field. We had problems
> generating alternative paths in extensions and designing the Asymmetric
> Join feature [1] when we proposed an alternative path one level below
> and called the add_path() routine. We had lost the path in the path list
> referenced from the upper RelOptInfo. This approach allows us to make
> more handy solutions instead of hacking with a copy/restore pathlist.

Thanks for your comments. I am glad that you find this useful.

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

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

> Also, I am glad to see a positive opinion about the path_walker()
> routine. Somewhere else, for example, in [2], it seems we need it too.
>

I agree that we should introduce path_walker() yes.

I am waiting for David's opinion about the performance numbers shared
in [1] before working further on the patches and bring them out of WIP
state.

[1] postgr.es/m/CAExHW5uDkMQL8SicV3_=[email-masked]

-- 
Best Wishes,
Ashutosh Bapat

---

From: Andrei Lepikhov <[email-masked]>
Date: Fri, 16 Feb 2024 10:12:47 +0700

> pathlist is referenced. An unreferenced path should not be in any
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.

>> previously planned partitions.
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

---

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 19 Feb 2024 17:55:28 +0530

> upper pathlist, it may still be helpful for the extension.

There is no guarantee that every path presented to add_path will be
preserved. Suboptimal paths are freed as and when add_path discovers
that they are suboptimal. So I don't think an extension can rely on
existence of a path. But having a refcount makes it easy to preserve
the required paths by referencing them.

> partition having SeqScans on 999 other?
IIUC, you are suggesting that instead of planning each
partition/partitionwise join, we only create paths with the strategies
which were found to be optimal with previous partitions. That's a good
heuristic but it won't work if partition properties - statistics,
indexes etc. differ between groups of partitions.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 20 Feb 2024 09:49:38 +0700

> the required paths by referencing them.
I don't insist, just provide my use case. It would be ideal if you would 
provide some external routines for extensions that allow for sticking 
the path in pathlist even when it has terrible cost estimation.
> indexes etc. differ between groups of partitions.
Sure, but the "Symmetry" strategy assumes that on the scope of a 
thousand partitions, especially with parallel append involved, it 
doesn't cause sensible performance degradation if we find a bit 
suboptimal path in a small subset of partitions. Does it make sense?
As I see, when people use 10-100 partitions for the table, they usually 
strive to keep indexes symmetrical for all partitions.

-- 
regards,
Andrei Lepikhov
Postgres Professional

---

From: Ashutosh Bapat <[email-masked]>
Date: Tue, 20 Feb 2024 10:38:31 +0530

> the path in pathlist even when it has terrible cost estimation.

With refcounts you can reference it and store it somewhere other than
pathlist. The path won't be lost until it is dereferrenced.
RelOptInfo::Pathlist is for optimal paths.

> strive to keep indexes symmetrical for all partitions.

I agree that we need something like that. In order to do that, we need
machinery to prove that all partitions have similar properties. Once
that is proved, we can skip creating paths for similar partitions. But
that's out of scope of this work and complements it.

-- 
Best Wishes,
Ashutosh Bapat