Content-Type: multipart/alternative;
 boundary="------------9Ryb9fx9hjdJRTXYGpzNQwfc"
Message-ID: <72eab2bf-7f94-4f58-84bc-2b80d74b6443@postgrespro.ru>
Date: Mon, 24 Mar 2025 15:46:03 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
To: Pavel Borisov <pashkin.elfe@gmail.com>,
 Andrei Lepikhov <lepihov@gmail.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>,
 Alexander Lakhin <exclusion@gmail.com>, pgsql-hackers@lists.postgresql.org
References: <567ED6CA.2040504@sigaev.ru>
 <CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
 <43db6a40-5c5f-4909-ad62-3060b297e686@postgrespro.ru>
 <CAPpHfdtgcye9ELaLHw4ZOUxWQ9t1P=oBDJX8KwwOHqasESdXQQ@mail.gmail.com>
 <CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
 <ab8aedce-7a2f-4bb2-bfad-3f1ad91e7336@gmail.com>
 <41ba3d47-2a48-476c-88d4-6ebd889a7af2@gmail.com>
 <CAPpHfdvehQbCpQG9zYr739y2nXQkE=FYsat5VE-pDoB4RDPfBw@mail.gmail.com>
 <e40494ad-cad8-43e6-8372-d0c7b3411ad7@postgrespro.ru>
 <CAPpHfdtPz+J-n6=qAMBqx1p88Ut_cbS9JRbL+EKRpWY04tGcRg@mail.gmail.com>
 <036d64e6-4d47-485c-b047-6664d452b3e8@postgrespro.ru>
 <CAPpHfdumgFuUihGqrmDVR5FnzOLsAah-ZdZ269FgsUrOeNU6rw@mail.gmail.com>
 <1a4f1631-86c1-4185-a520-03b24a389449@postgrespro.ru>
 <CALT9ZEE94JN01za2o872foQErgyLHaX7OnOtoh70QbmXP7=S2A@mail.gmail.com>
 <3ac7c436-81e1-4191-9caf-b0dd70b51511@gmail.com>
 <CALT9ZEEyivV=CDE=CCf8JwT5dU2epBDmbiRyVg8fO7-m6rud7g@mail.gmail.com>
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
In-Reply-To: 
 <CALT9ZEEyivV=CDE=CCf8JwT5dU2epBDmbiRyVg8fO7-m6rud7g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/72eab2bf-7f94-4f58-84bc-2b80d74b6443%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------9Ryb9fx9hjdJRTXYGpzNQwfc
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------9Ryb9fx9hjdJRTXYGpzNQwfc
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 24.03.2025 13:46, Pavel Borisov
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CALT9ZEEyivV=CDE=CCf8JwT5dU2epBDmbiRyVg8fO7-m6rud7g@mail.gmail.com">
      <pre class="moz-quote-pre" wrap="">Hi, Andrei!

On Mon, 24 Mar 2025 at 14:10, Andrei Lepikhov <a class="moz-txt-link-rfc2396E" href="mailto:lepihov@gmail.com">&lt;lepihov@gmail.com&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">
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 &lt; 2 OR m.thousand &lt; 3);

  Bitmap Heap Scan on public.main_tbl m
    Output: id, hundred, thousand
    Recheck Cond: ((m.thousand &lt; 3) OR (m.hundred &lt; 2))
    -&gt;  BitmapOr
          -&gt;  Bitmap Index Scan on mt_thousand_ix
                Index Cond: (m.thousand &lt; 3)
          -&gt;  Bitmap Index Scan on mt_hundred_ix
                Index Cond: (m.hundred &lt; 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.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
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] <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/17/sql-expressions.html#SYNTAX-EXPRESS-EVAL">https://www.postgresql.org/docs/17/sql-expressions.html#SYNTAX-EXPRESS-EVAL</a>

</pre>
    </blockquote>
    I agree with Andrey's changes and think we should fix this, because
    otherwise it might be inconvenient. <br>
    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.<br>
    <span style="white-space: pre-wrap">
</span>
    <pre class="moz-signature" cols="72">-- 
Regards,
Alena Rybakina
Postgres Professional</pre>
  </body>
</html>

--------------9Ryb9fx9hjdJRTXYGpzNQwfc--