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

On 24.03.2025 13:46, Pavel Borisov wrote:
> 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
>
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