Message-ID: <bd68c396-5e17-4254-a2c3-992ab8e912cc@postgrespro.ru>
Date: Tue, 20 Feb 2024 09:49:38 +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>
 <182fbad0-f3f6-4692-8fc4-778378e1db01@postgrespro.ru>
 <CAExHW5s_y_VxHcGZ1XX5_J5Yf34DWHU=Czj6BRf23Qx3LhE6jg@mail.gmail.com>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CAExHW5s_y_VxHcGZ1XX5_J5Yf34DWHU=Czj6BRf23Qx3LhE6jg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bd68c396-5e17-4254-a2c3-992ab8e912cc%40postgrespro.ru>
Precedence: bulk

On 19/2/2024 19:25, Ashutosh Bapat wrote:
> On Fri, Feb 16, 2024 at 8:42 AM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> 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.
> 
> 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.
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.
> 
>>
>>>> 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?
>>
> 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.
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