From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Mineharu Takahara <mtakahara@yugabyte.com>,
        pgsql-bugs@lists.postgresql.org,
        "mtakahar@gmail.com" <mtakahar@gmail.com>
Subject: Re: Suboptimal query plans for BETWEEN SYMMETRIC operations
In-reply-to: 
 <CAApHDvpGOi0=bDp3jVB6YXTxSNdvt082uNmPK15LaYyD9HT1+Q@mail.gmail.com>
References: 
 <CACfbPhMXe2f81nKgX9PC0q3n4jVkHobXcUj-88h27j1zUAb0Lw@mail.gmail.com>
 <CAApHDvpGOi0=bDp3jVB6YXTxSNdvt082uNmPK15LaYyD9HT1+Q@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Fri, 08 Nov 2024 11:58:07 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3515826.1731021757.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 07 Nov 2024 18:22:37 -0500
Message-ID: <3515827.1731021757@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3515827.1731021757%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Fri, 8 Nov 2024 at 08:36, Mineharu Takahara <mtakahara@yugabyte.com> =
wrote:
>> A condition: "col BETWEEN SYMMETRIC val1 AND val2" is currently rewritt=
en to "(((col >=3D val1) AND (col <=3D val2)) OR ((col >=3D val2) AND (col=
 <=3D val1)))" that would lead to suboptimal plans using an extra Bitmap I=
ndex Scan or Index Scan/Index Only Scan with the entire predicate placed i=
n the "Filter" instead of "Index Cond".

> This isn't a bug, it's just something that could perhaps be made more op=
timal.

Indeed.

The trouble with the LEAST/GREATEST formulation is that it may result
in different semantics in situations where val1 and val2 aren't the
same type.  Also, LEAST/GREATEST rely on the default btree opclass
for the common type, which might not match the semantics of the
comparison operators that the current coding chooses.

There are ways around that --- one could be to transform to
LEAST/GREATEST only when the arguments do resolve as the same type.
And perhaps you could convince people that BETWEEN ought to depend
on the default btree opclass not on operator names.  But it's all
a lot messier than you might think.

> If you're interested in making improvements in this area for core
> PostgreSQL, then pgsql-hackers is the place to discuss that.

Yup.

			regards, tom lane