Message-ID: <deb87eba-d4e2-40ad-84e9-219a25516b2d@gmail.com>
Date: Wed, 23 Oct 2024 15:51:02 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: allowing extensions to control planner behavior
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
References: <CA+TgmoZY+baV-T-5ifDn6P=L=aV-VkVBrPmi0TQkcEq-5Finww@mail.gmail.com>
 <2909615.1724693854@sss.pgh.pa.us>
 <CA+TgmoZQyVxnRU--4g2bJonJ8RyJqNi2CHpy-=nwwBTNpAj71A@mail.gmail.com>
 <CA+TgmoYadvnRGM7VEqgKbuZ+c+VN3jSDx+_38e66a9yk2fiovA@mail.gmail.com>
 <CA+TgmoZ=6jJi9TGyZCm33vads46HFkyz6Aju_saLT6GFS-iFug@mail.gmail.com>
 <b840f925-bb09-470b-9bd2-6d4769a7c9fb@gmail.com>
 <CAKZiRmyZiJbgduUaKJR+vfLDzm-ack+66hVA5Gkj19aLqcgW-g@mail.gmail.com>
 <a04c17c7-ece1-4842-a52d-ac4f4b3714c9@gmail.com>
 <CA+TgmoZb_u6aH+ziQecEf8YLTxQNOnKmhNJZZQsoXjj5t9s_9Q@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CA+TgmoZb_u6aH+ziQecEf8YLTxQNOnKmhNJZZQsoXjj5t9s_9Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/deb87eba-d4e2-40ad-84e9-219a25516b2d%40gmail.com>
Precedence: bulk

On 10/23/24 15:05, Robert Haas wrote:
> On Sat, Oct 19, 2024 at 6:00 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
>> Generally, a hash value doesn't 100% guarantee the uniqueness of a node
>> identification. Also, RelOptInfo corresponds to a subtree in the final
>> plan, and sometimes, it takes work to find which node in the partially
>> executed plan corresponds to this specific estimation on row number
>> during selectivity estimation. Remember parameterised paths - you should
>> attach some signature for each path. So, it is not fully strict method.
>> If you are interested, I can perhaps explain the method a little bit
>> more at some meetup.
> 
> Yeah, I agree that this is not the best method. While it's true that
> you could get a false match in case of a hash value collision, IMHO
> the bigger problem is that it seems like an expensive way of
> determining something that we really should know already. If the user
> types the same query, mentioning the same relations, in the same
> order, with the same constructs around them, it's hard to believe that
> hashing is the cheapest way of matching up the old and new ones. I'm
> not sure exactly what we should do instead, but it feels like we more
> or less have this information during parsing and then we lose track of
> it as the query goes through the rewrite and planning phases.
Parse tree may be implemented with multiple execution plans. Even 
clauses can be transformed during optimisation (Remember OR -> ANY). 
Also, the cardinality of a middle-tree join depends on the inner and 
outer subtrees. Because of that, having a hash on RelOptInfo's relids 
and restrictions + hashes of child RelOptInfos and carrying it through 
all other stages up to the end of execution is the most stable approach 
I know.

-- 
regards, Andrei Lepikhov