Re: Suboptimal query plans for BETWEEN SYMMETRIC operations

> > This isn't a bug, it's just something that could perhaps be made more
optimal.
>
> Indeed.

I agree. I should've clarified upfront that this was rather an enhancement
request. Or is there some specific way of tagging enhancement requests /
improvement suggestions that I can follow?

> 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.

Great points. Thanks for the insights!

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

Got it, thanks!

On Thu, Nov 7, 2024 at 6:22 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>