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>
User-agent: mu4e 1.10.7; emacs 29.1
From: Andy Fan <zhihuifan1213@163.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
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
Subject: Re: using extended statistics to improve join estimates
Date: Thu, 23 May 2024 10:04:27 +0800
In-reply-to: <a8fea1ce-4f5e-4374-9c75-ef18f5afb449@postgrespro.ru>
Message-ID: <87a5khi8as.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87a5khi8as.fsf%40163.com>
Precedence: bulk


Andrei Lepikhov <a.lepikhov@postgrespro.ru> writes:

> 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.

OK, I didn't pay enough attention to this comment before. and yes, I get
the same conclusion as you -  there is no implementation of this.

and if so, I think we should remove the comments and do the
implementation in the next patch. 

>>> 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.

OK.

Do you think the hook proposal is closely connected with the current
topic? IIUC it's seems not. So a dedicated thread to explain the problem
to slove and the proposal and the follwing discussion should be helpful
for both topics. I'm just worried about mixing the two in one thread
would make things complexer unnecessarily.

-- 
Best Regards
Andy Fan