Message-ID: <a8fea1ce-4f5e-4374-9c75-ef18f5afb449@postgrespro.ru>
Date: Mon, 20 May 2024 16:40:31 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: using extended statistics to improve join estimates
To: Andy Fan <zhihuifan1213@163.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 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>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <87o790kgj3.fsf@163.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/a8fea1ce-4f5e-4374-9c75-ef18f5afb449%40postgrespro.ru>
Precedence: bulk

On 20/5/2024 15:52, Andy Fan wrote:
> 
> Hi Andrei,
> 
>> On 4/3/24 01:22, Tomas Vondra wrote:
>>> Cool! There's obviously no chance to get this into v18, and I have stuff
>>> to do in this CF. But I'll take a look after that.
>> I'm looking at your patch now - an excellent start to an eagerly awaited
>> feature!
>> A couple of questions:
>> 1. I didn't find the implementation of strategy 'c' - estimation by the
>> number of distinct values. Do you forget it?
> 
> What do you mean the "strategy 'c'"?
As described in 0001-* patch:
* c) No extended stats with MCV. If there are multiple join clauses,
* we can try using ndistinct coefficients and do what eqjoinsel does.

> 
>> 2. Can we add a clauselist selectivity hook into the core (something
>> similar the code in attachment)? It can allow the development and
>> testing of multicolumn join estimations without patching the core.
> 
> The idea LGTM. But do you want
> 
> +	if (clauselist_selectivity_hook)
> +		s1 = clauselist_selectivity_hook(root, clauses, varRelid, jointype,
> +
> 
> rather than
> 
> +	if (clauselist_selectivity_hook)
> +		*return* clauselist_selectivity_hook(root, clauses, ..)
Of course - library may estimate not all the clauses - it is a reason, 
why I added input/output parameter 'estimatedclauses' by analogy with 
statext_clauselist_selectivity.

-- 
regards,
Andrei Lepikhov
Postgres Professional