Message-ID: <a99e8522-4dd3-43e6-a1b7-144169bb315f@gmail.com>
Date: Tue, 3 Sep 2024 14:58:05 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: using extended statistics to improve join estimates
To: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Andrei Lepikhov <a.lepikhov@postgrespro.ru>, Andy Fan <zhihuifan1213@163.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>, Andres Freund <andres@anarazel.de>,
 pgsql-hackers@postgresql.org, Justin Pryzby <pryzby@telsasoft.com>,
 pgsql-hackers@lists.postgresql.org
References: <c8c0ff31-3a8a-7562-bbd3-78b2ec65f16c@enterprisedb.com>
 <2ed75657-e084-9539-c6de-597e5675014c@enterprisedb.com>
 <ff412e82-5427-3b72-cf32-4700156dc47c@enterprisedb.com>
 <20211122012315.GH17618@telsasoft.com>
 <0213d237-efd5-8e7e-fa29-3f36e6e26023@enterprisedb.com>
 <1ea3bf2b-57f9-e632-251a-e718623d7a72@enterprisedb.com>
 <20220104235550.dgr4rz6zkjx2rykz@alap3.anarazel.de>
 <20220119101809.wzrjvh4jsnoakgny@jrouhaud>
 <20220302173821.GG15744@telsasoft.com> <20220302173943.GA28810@telsasoft.com>
 <87cyr89nk5.fsf@163.com>
 <2a9604a5-520d-48ee-b3c8-5f3342b607d3@enterprisedb.com>
 <866452b8-67b3-4f67-b85c-c3bb8e831ff2@postgrespro.ru>
 <87o790kgj3.fsf@163.com>
 <a8fea1ce-4f5e-4374-9c75-ef18f5afb449@postgrespro.ru>
 <87a5khi8as.fsf@163.com>
 <5c9a1eee-c8ac-408b-95c4-ff388e5e12f0@postgrespro.ru>
 <b8079953-9347-468b-ada9-db26752ce00b@enterprisedb.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <b8079953-9347-468b-ada9-db26752ce00b@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/a99e8522-4dd3-43e6-a1b7-144169bb315f%40gmail.com>
Precedence: bulk

On 17/6/2024 18:10, Tomas Vondra wrote:
> Let me quickly go through the original parts - most of this is already
> in the "review" patches, but it's better to quote the main points here
> to start a discussion. I'll omit some of the smaller suggestions, so
> please look at the 'review' patches.
> 
> 
> v20240617-0001-Estimate-joins-using-extended-statistics.patch
> 
> - rewords a couple comments, particularly for statext_find_matching_mcv
> 
> - a couple XXX comments about possibly stale/inaccurate comments
> v20240617-0054-clauselist_selectivity_hook.patch
> 
> - I believe this does not work with the earlier patch that removed
> estimatedclaused bitmap from the "try" function.
This patch set is too big to eat at once - it's just challenging to 
invent examples and counterexamples. Can we see these two patches in the 
master and analyse further improvements based on that?

Some thoughts:
You remove verRelid. I have thought about replacing this value with 
RelOptInfo, which would allow extensions (remember selectivity hook) to 
know about the underlying path tree.

The first patch is generally ok, and I vote for having it in the master. 
However, the most harmful case I see most reports about is parameterised 
JOIN on multiple anded clauses. In that case, we have a scan filter on 
something like the below:
x = $1 AND y = $2 AND ...
As I see, current patch doesn't resolve this issue currently.

-- 
regards, Andrei Lepikhov