MIME-Version: 1.0
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>
 <d46e1950-9228-3fe2-02b6-72ab7aaafb3a@enterprisedb.com>
 <CAB4o4asvPN=NT7KvS9zVQjZbdsiRW5t8aQctEkW7mxc4hbBxVQ@mail.gmail.com>
 <525ea957-88ff-e2c6-2975-474c849aa234@ulb.be>
 <ac128d6a-08a4-4848-e351-65cface6d6b5@ulb.be>
 <48a78946-f380-a279-e75f-711846e509f8@ulb.be>
 <CALH1Lgurg1y1DTeFOXOkpP=+X7saVGCh8gSjDLoCBOcFFWhz-A@mail.gmail.com>
 <4c443cf6-96af-0953-fd5b-bd2b5c0c6f00@ulb.be>
 <2516071.1699991181@sss.pgh.pa.us>
 <f530d708-bcb4-1487-f25e-248ea101e571@ulb.be>
 <CALDaNm29NMhpPvoM49qqfYzVcRJq0CPTd-xSUcTF7RZQO2TASQ@mail.gmail.com>
 <08b2dea8-6dca-06e4-a53d-3e6334e7645b@ulb.be>
 <CALDaNm3A4j7=rmY128Eb3v8zi=hHAnGizUcbSX5ekZKHy-wmMA@mail.gmail.com>
In-Reply-To: 
 <CALDaNm3A4j7=rmY128Eb3v8zi=hHAnGizUcbSX5ekZKHy-wmMA@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Mon, 22 Jan 2024 16:10:37 +0800
Message-ID: 
 <CACJufxFhES7untAPmxJDrB-y_pNgah_1HNbXjcOj6g=yxh3kdA@mail.gmail.com>
Subject: Re: Implement missing join selectivity estimation for range types
To: vignesh C <vignesh21@gmail.com>
Cc: Schoemans Maxime <maxime.schoemans@ulb.be>, Tom Lane <tgl@sss.pgh.pa.us>,
	Damir Belyalov <dam.bel07@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 SAKR Mahmoud <mahmoud.sakr@ulb.be>,
	Diogo Repas <diogo.repas@gmail.com>, LUO Zhicheng <zhicheng.luo@ulb.be>,
	Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxFhES7untAPmxJDrB-y_pNgah_1HNbXjcOj6g%3Dyxh3kdA%40mail.gmail.com>
Precedence: bulk

I cannot figure out why it aborts.

as Tom mentioned in upthread about the test cases.
similar to src/test/regress/sql/stats_ext.sql check_estimated_rows function.
we can test it by something:

create or replace function check_estimated_rows(text) returns table (ok bool)
language plpgsql as
$$
declare
    ln text;
    tmp text[];
    first_row bool := true;
begin
    for ln in
        execute format('explain analyze %s', $1)
    loop
        if first_row then
            first_row := false;
            tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)');
            return query select 0.2 < tmp[1]::float8 / tmp[2]::float8
and tmp[1]::float8 / tmp[2]::float8 < 5;
        end if;
    end loop;
end;
$$;

select * from check_estimated_rows($$select * from test_range_join_1,
test_range_join_2 where ir1 && ir2$$);
select * from check_estimated_rows($$select * from test_range_join_1,
test_range_join_2 where ir1 << ir2$$);
select * from check_estimated_rows($$select * from test_range_join_1,
test_range_join_2 where ir1 >> ir2$$);

Do you need 3 tables to do the test? because we need to actually run
the query then compare the estimated row
and actually returned rows.
If you really execute the query with 3 table joins, it will take a lot of time.
So two tables join with where quql should be fine?

/* Fast-forwards i and j to start of iteration */
+ for (i = 0; range_cmp_bound_values(typcache, &hist1[i], &hist2[0]) < 0; i++);
+ for (j = 0; range_cmp_bound_values(typcache, &hist2[j], &hist1[0]) < 0; j++);
+
+ /* Do the estimation on overlapping regions */
+ while (i < nhist1 && j < nhist2)
+ {
+ double cur_sel1,
+ cur_sel2;
+ RangeBound cur_sync;
+
+ if (range_cmp_bound_values(typcache, &hist1[i], &hist2[j]) < 0)
+ cur_sync = hist1[i++];
+ else if (range_cmp_bound_values(typcache, &hist1[i], &hist2[j]) > 0)
+ cur_sync = hist2[j++];
+ else
+ {
+ /* If equal, skip one */
+ cur_sync = hist1[i];
+

this part range_cmp_bound_values "if else if" part computed twice, you
can just do
`
int cmp;
cmp = range_cmp_bound_values(typcache, &hist1[i], &hist2[j]);
if cmp <0  then
else if cmp > 0 then
else then
`

also. I think you can put the following into  main while loop.
+ for (i = 0; range_cmp_bound_values(typcache, &hist1[i], &hist2[0]) < 0; i++);
+ for (j = 0; range_cmp_bound_values(typcache, &hist2[j], &hist1[0]) < 0; j++);

split range and multirange into 2 patches might be a good idea.
seems: same function (calc_hist_join_selectivity) with same function
signature in src/backend/utils/adt/multirangetypes_selfuncs.c
and src/backend/utils/adt/rangetypes_selfuncs.c,
previously mail complaints not resolved.