Message-ID: <d46e1950-9228-3fe2-02b6-72ab7aaafb3a@enterprisedb.com>
Date: Sat, 21 Jan 2023 22:12:27 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.0
Subject: Re: Implement missing join selectivity estimation for range types
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Mahmoud Sakr <mahmoud.sakr@ulb.be>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 SCHOEMANS Maxime <Maxime.Schoemans@ulb.be>,
 Diogo Repas <diogo.repas@gmail.com>, Luo Zhicheng <zhicheng.luo@ulb.be>,
 Andrey Lepikhov <a.lepikhov@postgrespro.ru>
References: <CAB4o4asMq3k6HN9WfDsssQ5DDVfAziB4TpiFJ8RBJgZTVuwC7g@mail.gmail.com>
 <8afecd87-d1e5-241c-5e3e-75e1c62c279b@enterprisedb.com>
 <1bc5e1e4-380b-213d-6e6a-d5e8f7f0c7f5@enterprisedb.com>
 <CAB4o4aud47V_iRyWtA8+ZAmdXDjCF165R73AeCjx2RL0nzQzHA@mail.gmail.com>
 <84ffb566-8038-ab35-c841-7a5e5728a247@enterprisedb.com>
In-Reply-To: <84ffb566-8038-ab35-c841-7a5e5728a247@enterprisedb.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/d46e1950-9228-3fe2-02b6-72ab7aaafb3a%40enterprisedb.com>
Precedence: bulk

Hi Mahmoud,

I finally had time to properly read the paper today - the general
approach mostly matches how I imagined the estimation would work for
inequalities, but it's definitely nice to see the algorithm properly
formalized and analyzed.

What seems a bit strange to me is that the patch only deals with range
types, leaving the scalar cases unchanged. I understand why (not having
a MCV simplifies it a lot), but I'd bet joins on range types are waaaay
less common than inequality joins on scalar types. I don't even remember
seeing inequality join on a range column, TBH.

That doesn't mean the patch is wrong, of course. But I'd expect users to
be surprised we handle range types better than "old" scalar types (which
range types build on, in some sense).

Did you have any plans to work on improving estimates for the scalar
case too? Or did you do the patch needed for the paper, and have no
plans to continue working on this?

I'm also wondering about not having MCV for ranges. I was a bit
surprised we don't build MCV in compute_range_stats(), and perhaps we
should start building those - if there are common ranges, this might
significantly improve some of the estimates (just like for scalar
columns). Which would mean the estimates for range types are just as
complex as for scalars. Of course, we don't do that now.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company