From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Mineharu Takahara <mtakahara@yugabyte.com>
Cc: pgsql-bugs@lists.postgresql.org, "mtakahar@gmail.com" <mtakahar@gmail.com>
Subject: Re: Suboptimal query plans for BETWEEN SYMMETRIC operations
Date: Thu, 07 Nov 2024 18:22:37 -0500
Message-ID: <3515827.1731021757@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvpGOi0=bDp3jVB6YXTxSNdvt082uNmPK15LaYyD9HT1+Q@mail.gmail.com>
References: <CACfbPhMXe2f81nKgX9PC0q3n4jVkHobXcUj-88h27j1zUAb0Lw@mail.gmail.com>
<CAApHDvpGOi0=bDp3jVB6YXTxSNdvt082uNmPK15LaYyD9HT1+Q@mail.gmail.com>
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 rewritten to "(((col >= val1) AND (col <= val2)) OR ((col >= val2) AND (col <= val1)))" that would lead to suboptimal plans using an extra Bitmap Index Scan or Index Scan/Index Only Scan with the entire predicate placed in the "Filter" instead of "Index Cond".
> This isn't a bug, it's just something that could perhaps be made more optimal.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, mtakahara@yugabyte.com, mtakahar@gmail.com
Subject: Re: Suboptimal query plans for BETWEEN SYMMETRIC operations
In-Reply-To: <3515827.1731021757@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox