Message-ID: <2c54b453-dc12-4717-997f-b51370215139@tantorlabs.com>
Date: Mon, 9 Jun 2025 21:38:27 +0300
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: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <b8079953-9347-468b-ada9-db26752ce00b@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2c54b453-dc12-4717-997f-b51370215139%40tantorlabs.com>
Precedence: bulk

Hi hackers

Еhank you for your work.

Let me start my review from the top — specifically, in clausesel.c, the 
function clauselist_selectivity_ext():

1. About check clauses == NULL. In my opinion, this check should be 
kept. This issue has already been discussed previously[0], and I think 
it's better to keep the safety check.

2. I noticed that the patch applies extended statistics to OR clauses as 
well. There's an example from regression tests illustrating this:

Before applying ext stats:
SELECT * FROM check_estimated_rows('select * from join_test_1 j1 join 
join_test_2 j2 on ((j1.a + 1 = j2.a + 1) or (j1.b = j2.b))');
  estimated | actual
-----------+--------
     104500 | 100000

After applying ext stats:
SELECT * FROM check_estimated_rows('select * from join_test_1 j1 join 
join_test_2 j2 on ((j1.a + 1 = j2.a + 1) or (j1.b = j2.b))');
  estimated | actual
-----------+--------
     190000 | 100000
(1 row)

I agree that, at least for now, we should focus solely on AND clauses. 
To do that, we should impose the same restriction in 
clauselist_selectivity_or() as we already do in 
clauselist_selectivity_ext().

What do you think? Or shall we consider OR-clauses as well?

[0]: 
https://www.postgresql.org/message-id/flat/016e33b7-2830-4300-bc89-e7ce9e613bad%40tantorlabs.com

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.