Content-Type: multipart/alternative;
 boundary="------------qitupXfotRrAhbMUIzx0N2NE"
Message-ID: <e40494ad-cad8-43e6-8372-d0c7b3411ad7@postgrespro.ru>
Date: Fri, 31 Jan 2025 17:31:44 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Ranier Vilela <ranier.vf@gmail.com>,
 Alexander Lakhin <exclusion@gmail.com>, jian he
 <jian.universality@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
 Nikolay Shaplov <dhyan@nataraj.su>, pgsql-hackers@lists.postgresql.org,
 pgsql-hackers@postgresql.org, Peter Geoghegan <pg@bowt.ie>,
 Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru,
 Peter Eisentraut <peter@eisentraut.org>, Andrei Lepikhov <lepihov@gmail.com>
References: <567ED6CA.2040504@sigaev.ru>
 <CAPpHfduzBgV3AecMU0jFqOSjK9iP86HiHEzj2Hv6hLqWu7JJFQ@mail.gmail.com>
 <CAPpHfduqOuvh6i=jYDPWnAUg325hsOLUAW9r_awdirQRA7uzHA@mail.gmail.com>
 <37bf870a-22e0-4aba-b845-549482118f8d@gmail.com>
 <CAPpHfdtPkA3XGM+FOanXfFNm45YEMkuqpfL0ZSL7uOP1bHPPEg@mail.gmail.com>
 <8174de69-9e1a-0827-0e81-ef97f56a5939@gmail.com>
 <ed2b909c-35d4-4a39-ac38-8a15634a281b@postgrespro.ru>
 <CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
 <3b717020-14d9-425f-8c60-ce336b5a23b3@postgrespro.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>
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
In-Reply-To: <CAPpHfdvehQbCpQG9zYr739y2nXQkE=FYsat5VE-pDoB4RDPfBw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/e40494ad-cad8-43e6-8372-d0c7b3411ad7%40postgrespro.ru>
Precedence: bulk

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

Hi!

On 25.01.2025 08:04, Alexander Korotkov wrote:
> On Wed, Jan 15, 2025 at 10:24 AM Andrei Lepikhov<lepihov@gmail.com>  wrote:
>> On 1/13/25 10:39, Andrei Lepikhov wrote:
>>> On 1/13/25 01:39, Alexander Korotkov wrote:
>>> It can be resolved with a single-line change (see attached). But I need
>>> some time to ponder over the changing behaviour when a clause may match
>>> an index and be in joinorclauses.
>> In addition, let me raise a couple of issues:
>> 1. As Robert has said before, it may interfere with some short-circuit
>> optimisations like below:
>>
>> EXPLAIN (COSTS OFF)
>> SELECT * FROM bitmap_split_or t1
>> WHERE t1.a=2 AND (t1.b=2 OR t1.b = (
>>     SELECT sum(c1.reltuples) FROM pg_class c1, pg_class c2
>>     WHERE c1.relpages=c2.relpages AND c1.relpages = t1.a));
>>
>> Here, a user may avoid evaluating the subplan at all if t1.b=2 all the
>> time when t1.a=2. OR->ANY may accidentally shift this behaviour.
>>
>> 2. The query:
>>
>> EXPLAIN (ANALYZE, COSTS OFF)
>> SELECT * FROM bitmap_split_or t1
>> WHERE t1.a=2 OR t1.a = (
>>     SELECT sum(c1.reltuples) FROM pg_class c1, pg_class c2
>>     WHERE c1.relpages=c2.relpages AND c1.relpages = t1.a)::integer;
>>
>> causes SEGFAULT during index keys evaluation. I haven't dived into it
>> yet, but it seems quite a typical misstep and is not difficult to fix.
> Segfault appears to be caused by a typo.  Patch used parent rinfo
> instead of child rinfo.  Fixed in the attached patch.
>
> It appears that your first query also changed a plan after fixing
> this.  Could you, please, provide another example of a regression for
> short-circuit optimization, which is related to this patch?
>
> Also, I've integrated your fix from [1].
>
> Links.
> 1.https://www.postgresql.org/message-id/41ba3d47-2a48-476c-88d4-6ebd889a7af2%40gmail.com

I started reviewing at the patch and saw some output "ERROR" in the 
output of the test and is it okay here?

SELECT * FROM tenk1 t1
WHERE t1.thousand= 42OR t1.thousand= (SELECT t2.tenthousFROM tenk1 t2 
WHERE t2.thousand= t1.tenthous);
ERROR: more than one row returned by a subquery used as an expression

-- 
Regards,
Alena Rybakina
Postgres Professional

--------------qitupXfotRrAhbMUIzx0N2NE
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi!<br>
    </p>
    <div class="moz-cite-prefix">On 25.01.2025 08:04, Alexander Korotkov
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAPpHfdvehQbCpQG9zYr739y2nXQkE=FYsat5VE-pDoB4RDPfBw@mail.gmail.com">
      <pre class="moz-quote-pre" wrap="">On Wed, Jan 15, 2025 at 10:24 AM 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="">On 1/13/25 10:39, Andrei Lepikhov wrote:
</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">On 1/13/25 01:39, Alexander Korotkov wrote:
It can be resolved with a single-line change (see attached). But I need
some time to ponder over the changing behaviour when a clause may match
an index and be in joinorclauses.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">In addition, let me raise a couple of issues:
1. As Robert has said before, it may interfere with some short-circuit
optimisations like below:

EXPLAIN (COSTS OFF)
SELECT * FROM bitmap_split_or t1
WHERE t1.a=2 AND (t1.b=2 OR t1.b = (
   SELECT sum(c1.reltuples) FROM pg_class c1, pg_class c2
   WHERE c1.relpages=c2.relpages AND c1.relpages = t1.a));

Here, a user may avoid evaluating the subplan at all if t1.b=2 all the
time when t1.a=2. OR-&gt;ANY may accidentally shift this behaviour.

2. The query:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM bitmap_split_or t1
WHERE t1.a=2 OR t1.a = (
   SELECT sum(c1.reltuples) FROM pg_class c1, pg_class c2
   WHERE c1.relpages=c2.relpages AND c1.relpages = t1.a)::integer;

causes SEGFAULT during index keys evaluation. I haven't dived into it
yet, but it seems quite a typical misstep and is not difficult to fix.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Segfault appears to be caused by a typo.  Patch used parent rinfo
instead of child rinfo.  Fixed in the attached patch.

It appears that your first query also changed a plan after fixing
this.  Could you, please, provide another example of a regression for
short-circuit optimization, which is related to this patch?

Also, I've integrated your fix from [1].

Links.
1. <a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/41ba3d47-2a48-476c-88d4-6ebd889a7af2%40gmail.com">https://www.postgresql.org/message-id/41ba3d47-2a48-476c-88d4-6ebd889a7af2%40gmail.com</a>
</pre>
    </blockquote>
    <br>
    <p>I started reviewing at the patch and saw some output "ERROR" in
      the output of the test and is it okay here?</p>
    <div
style="color: #000000;background-color: #ffffff;font-family: 'Droid Sans Mono', 'monospace', monospace;font-weight: normal;font-size: 16px;line-height: 22px;white-space: pre;"><div><span
    style="color: #000000;">SELECT * FROM tenk1 t1</span></div><div><span
    style="color: #000000;">  WHERE </span><span style="color: #0000ff;">t1.thousand</span><span
    style="color: #000000;"> = </span><span style="color: #0000ff;">42</span><span
    style="color: #000000;"> OR </span><span style="color: #0000ff;">t1.thousand</span><span
    style="color: #000000;"> = (SELECT </span><span
    style="color: #0000ff;">t2.tenthous</span><span
    style="color: #000000;"> FROM tenk1 t2 WHERE </span><span
    style="color: #0000ff;">t2.thousand</span><span
    style="color: #000000;"> = </span><span style="color: #0000ff;">t1.tenthous</span><span
    style="color: #000000;">);</span></div><div><span
    style="color: #a31515;font-weight: bold;">ERROR</span><span
    style="color: #000000;">:  more than one row returned by a subquery used as an expression</span></div></div>
    <p><span style="white-space: pre-wrap">
</span></p>
    <pre class="moz-signature" cols="72">-- 
Regards,
Alena Rybakina
Postgres Professional</pre>
  </body>
</html>

--------------qitupXfotRrAhbMUIzx0N2NE--