MIME-Version: 1.0
References: <567ED6CA.2040504@sigaev.ru> <32d23d97-d07f-4d44-9933-ba3295fb3ed8@postgrespro.ru>
 <aec17a1a-9de0-4994-8a53-c6b4e64afb17@postgrespro.ru> <CACJufxGXhJ823cdAdp2Ho7qC-HZ3_-dtdj-myaAi_u9RQLn45g@mail.gmail.com>
 <CACJufxG=wB6qLbOAWaYEQHccN+qi=6-pw7zKVWZ53ZiDZNRTRA@mail.gmail.com>
 <CACJufxFrZS07oBHMk1_c8P3A84VZ3ysXiZV8NeU6gAnvu+HsVA@mail.gmail.com>
 <a155849d-7e74-4700-8c55-8a945d42935e@postgrespro.ru> <CACJufxFS-xcjaWq2Du2OyJUjRAyqCk12Q_zGOPxv61sgrdpw9w@mail.gmail.com>
 <7e11e27b-7ab9-4d59-af0a-a921861a9206@postgrespro.ru> <13ad923a-33df-4b44-9e00-c9323cd4c37d@postgrespro.ru>
 <CACJufxFSfSGZpmVt_98SRojdugW2s9H-qYm2sYr0A3o-xPea0g@mail.gmail.com>
 <e3338e82-a28d-4631-9eec-b9c0984b32d5@postgrespro.ru> <1cf6b382-6e27-4268-9f2a-54ada66eb2eb@postgrespro.ru>
 <CACJufxH-zju3VfosSRBu8d3xs99sGENn6+P3AETLuTiAPFY+0g@mail.gmail.com>
 <5f119769-ab11-4807-abcc-71d5dd914ea0@postgrespro.ru> <CACJufxFMFoosvbuFYDiFtpam4sKCSGJeh5jYiZSY8PHjCpEHYA@mail.gmail.com>
 <cf0d1615-b594-404d-9a23-3d8ffd9070ec@postgrespro.ru>
In-Reply-To: <cf0d1615-b594-404d-9a23-3d8ffd9070ec@postgrespro.ru>
From: jian he <jian.universality@gmail.com>
Date: Tue, 20 Feb 2024 12:03:31 +0800
Message-ID: <CACJufxG0v_Fv7bnuys2mNZGsFSb4qsQFspArnYghqm0Jswa9vw@mail.gmail.com>
Subject: Re: POC, WIP: OR-clause support for indexes
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>, Alexander Korotkov <aekorotkov@gmail.com>, 
	Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@postgresql.org, 
	Peter Geoghegan <pg@bowt.ie>, "Finnerty, Jim" <jfinnert@amazon.com>, Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru, 
	Ranier Vilela <ranier.vf@gmail.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, 
	Peter Eisentraut <peter@eisentraut.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxG0v_Fv7bnuys2mNZGsFSb4qsQFspArnYghqm0Jswa9vw%40mail.gmail.com>
Precedence: bulk

On Mon, Feb 19, 2024 at 4:35=E2=80=AFPM Andrei Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
>
> In attachment - v17 for both patches. As I see it, the only general
> explanation of the idea is not addressed. I'm not sure how deeply we
> should explain it.


> On Tue, Nov 28, 2023 at 5:04=E2=80=AFAM Robert Haas <robertmhaas@gmail.co=
m> wrote:
>
> On Mon, Nov 27, 2023 at 3:02=E2=80=AFAM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
> > On 25/11/2023 08:23, Alexander Korotkov wrote:
> > > I think patch certainly gets better in this aspect.  One thing I can'=
t
> > > understand is why do we use home-grown code for resolving
> > > hash-collisions.  You can just define custom hash and match functions
> > > in HASHCTL.  Even if we need to avoid repeated JumbleExpr calls, we
> > > still can save pre-calculated hash value into hash entry and use
> > > custom hash and match.  This doesn't imply us to write our own
> > > collision-resolving code.
> >
> > Thanks, it was an insightful suggestion.
> > I implemented it, and the code has become shorter (see attachment).
>
> Neither the code comments nor the commit message really explain the
> design idea here. That's unfortunate, principally because it makes
> review difficult.
>
> I'm very skeptical about the idea of using JumbleExpr for any part of
> this. It seems fairly expensive, and it might produce false matches.
> If expensive is OK, then why not just use equal()? If it's not, then
> this probably isn't really OK either. But in any case there should be
> comments explaining why this strategy was chosen.

The above message
(https://postgr.es/m/CA%2BTgmoZCgP6FrBQEusn4yaWm02XU8OPeoEMk91q7PRBgwaAkFw%=
40mail.gmail.com)
seems still not answered.
How can we evaluate whether JumbleExpr is expensive or not?
I used this naive script to test, but didn't find a big difference
when enable_or_transformation is ON or OFF.

`
create table test_1_100 as (select (random()*1000)::int x,
(random()*1000) y from generate_series(1,1_000_000) i);
explain(costs off, analyze)
select * from test
where x =3D 1 or x + 2=3D 3 or x + 3=3D 4 or x + 4=3D 5
or x + 5=3D 6 or x + 6=3D 7 or x + 7=3D 8 or x + 8=3D 9 or x + 9=3D10
or x + 10=3D 11 or x + 11=3D 12 or x + 12=3D 13 or x + 13=3D 14
or x + 14=3D 15 or x + 15=3D 16 or x + 16=3D 17 or x + 17=3D 18
or x + 18=3D19 or x + 19=3D 20 or x + 20=3D 21 or x + 21=3D 22
or x + 22=3D 23 or x + 23=3D 24 or x + 24=3D 25 or x + 25=3D 26
or x + 26=3D 27 or x + 27=3D28 or x + 28=3D 29 or x + 29=3D 30
or x + 30=3D 31 \watch i=3D0.1 c=3D10
`

`leftop operator rightop`
the operator can also be volatile.
Do we need to check (op_volatile(opno) =3D=3D PROVOLATILE_VOLATILE) within
transformBoolExprOr?