Re: Suboptimal query plans for BETWEEN SYMMETRIC operations

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> On Fri, 8 Nov 2024 at 08:36, Mineharu Takahara <mtakahara(at)yugabyte(dot)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