Re: POC, WIP: OR-clause support for indexes

Hi, Andrei!

On Mon, 24 Mar 2025 at 14:10, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>
> Hi,
>
> Playing with the feature, I found a slightly irritating permutation -
> even if this code doesn't group any clauses, it may permute positions of
> the quals. See:
>
> DROP TABLE IF EXISTS main_tbl;
> CREATE TABLE main_tbl(id bigint, hundred int, thousand int);
> CREATE INDEX mt_hundred_ix ON main_tbl(hundred);
> CREATE INDEX mt_thousand_ix ON main_tbl(thousand);
> VACUUM (ANALYZE) main_tbl;
>
> SET enable_seqscan = off;
> EXPLAIN (COSTS OFF)
> SELECT m.id, m.hundred, m.thousand
> FROM main_tbl m WHERE (m.hundred < 2 OR m.thousand < 3);
>
> Bitmap Heap Scan on public.main_tbl m
> Output: id, hundred, thousand
> Recheck Cond: ((m.thousand < 3) OR (m.hundred < 2))
> -> BitmapOr
> -> Bitmap Index Scan on mt_thousand_ix
> Index Cond: (m.thousand < 3)
> -> Bitmap Index Scan on mt_hundred_ix
> Index Cond: (m.hundred < 2)
>
> Conditions on the columns "thousand" and "hundred" changed their places
> according to the initial positions defined in the user's SQL.
> It isn't okay. I see that users often use the trick of "OR order" to
> avoid unnecessary calculations - most frequently, Subplan evaluations.
> So, it makes sense to fix.
> In the attachment, I have included a quick fix for this issue. Although
> many tests returned to their initial (pre-18) state, I added some tests
> specifically related to this issue to make it clearer.

I looked at your patch and have no objections to it.

However it's clearly stated in PostgreSQL manual that nothing about
the OR order is warranted [1]. So changing OR order was (and is) ok
and any users query tricks about OR order may work and may not work.

[1] https://www.postgresql.org/docs/17/sql-expressions.html#SYNTAX-EXPRESS-EVAL

Regards,
Pavel Borisov
Supabase