public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: David Rowley <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Memory consumed by paths during partitionwise join planning
Date: Fri, 16 Feb 2024 10:12:47 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAExHW5vqfg8J-Oe4AOaUnBLz4rWhbJGDKFJHBDDEQ907Om53yg@mail.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>
<[email protected]>
<CAExHW5vqfg8J-Oe4AOaUnBLz4rWhbJGDKFJHBDDEQ907Om53yg@mail.gmail.com>
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
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Memory consumed by paths during partitionwise join planning
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox