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

Hi, Andrei!

On Mon, 24 Mar 2025 at 14:10, Andrei Lepikhov <lepihov@gmail.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

I agree with Andrey's changes and think we should fix this, because otherwise it might be inconvenient.
For example, without this changes we will have to have different test output files for the same query for different versions of Postres in extensions if the whole change is only related to the order of column output for a transformation that was not applied.

-- 
Regards,
Alena Rybakina
Postgres Professional