Message-ID: <944ed18c-3e7d-42ef-816e-0afc41610e93@postgrespro.ru>
Date: Mon, 19 Aug 2024 10:43:35 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Asymmetric partition-wise JOIN
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>, KaiGai Kohei
 <kaigai@heterodb.com>, sulamul@gmail.com,
 Alexander Pyhalov <a.pyhalov@postgrespro.ru>,
 Jaime Casanova <jcasanov@systemguards.com.ec>,
 Aleksander Alekseev <afiskon@gmail.com>, pgsql-hackers@lists.postgresql.org,
 "a.rybakina" <a.rybakina@postgrespro.ru>
References: <CAOP8fzaVL_2SCJayLL9kj5pCA46PJOXXjuei6-3aFUV45j4LJQ@mail.gmail.com>
 <CALtqXTfhC=FfHe0+_RP84w=X13d+z+akyN_ROCEQAyQigW7PDA@mail.gmail.com>
 <163118104689.1167.8241611097516113795.pgcf@coridan.postgresql.org>
 <20210909153833.GA6514@ahch-to>
 <2c9caede-55c0-2042-e421-dd0021f28837@postgrespro.ru>
 <792d60f4-37bc-e6ad-68ca-c2af5cbb2d9b@postgrespro.ru>
 <88bc3c051d285653215393a56bdf3056@postgrespro.ru>
 <CAPpHfdtm2_xM1WP1=Jyj9vRQ5jNu-kSqaoazvUp_s_7Cq0906A@mail.gmail.com>
 <5c0e38e3-7ab5-4b10-a1bb-70ca69771ff0@postgrespro.ru>
 <CAPpHfdvwp7cBWs=iBv7zc7e9NOWQf0TmcwNNzw=--H1qFoBcNA@mail.gmail.com>
 <e2544a0e-7d84-4ba7-b796-99b0f1d9f4a1@postgrespro.ru>
 <CAPpHfdt0NevYsYSy_QuJAJmk34+9qJY3Cc34Qi6LbdTugPBTmw@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CAPpHfdt0NevYsYSy_QuJAJmk34+9qJY3Cc34Qi6LbdTugPBTmw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/944ed18c-3e7d-42ef-816e-0afc41610e93%40postgrespro.ru>
Precedence: bulk

On 1/8/2024 20:56, Alexander Korotkov wrote:
> On Tue, Apr 2, 2024 at 6:07 AM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
> Actually, the idea I tried to express is the combination of #1 and #2:
> to build individual plan for every partition, but consider the 'Common
> Resources'.  Let me explain this a bit more.
Thanks for keeping your eye on it!
> My idea is to introduce a new property for paths selection.
> 3) Usage of common resources.  The common resource can be: hash
> representation of relation, memoize over relation scan, etc.  We can
> exclude the cost of common resource generation from the path cost, but
> keep the reference for the common resource with its generation cost.
> If one path uses more common resources than another path, it could
> cost-dominate another one only if its cheaper together with its extra
> common resources cost.  If one path uses less or equal common
> resources than another, it could normally cost-dominate another one.
The most challenging part for me is the cost calculation, which is 
bonded with estimations of other paths. To correctly estimate the 
effect, we need to remember at least the whole number of paths sharing 
resources.
Also, I wonder if it can cause some corner cases where prediction error 
on a shared resource will cause an even worse situation upstream.
I think we could push off here from an example and a counter-example, 
but I still can't find them.

> However, I understand this is huge amount of work given we have to
> introduce new basic optimizer concepts.  I get that the main
> application of this patch is sharding.  If we have global tables
> residing each shard, we can push down any joins with them.  Given this
> patch gives some optimization for non-sharded case, I think we
> *probably* can accept its concept even that it this optimization is
> obviously not perfect.
Yes, right now sharding is the most profitable case. We can push down 
parts of the plan which references only some common resources: 
FunctionScan, ValueScan, tables which can be proved are existed 
everywhere and provide the same output. But for now it is too far from 
the core code, IMO. - So, I search for cases that can be helpful for a 
single instance.

-- 
regards,
Andrei Lepikhov
Postgres Professional