Message-ID: <41ba3d47-2a48-476c-88d4-6ebd889a7af2@gmail.com>
Date: Wed, 15 Jan 2025 15:24:27 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
From: Andrei Lepikhov <lepihov@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>,
 Alena Rybakina <a.rybakina@postgrespro.ru>
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>
References: <567ED6CA.2040504@sigaev.ru>
 <CACJufxGS_MKqkfnw3BMhfi+=xuf2SAFvwf0Eq3e12XqAQaKdZg@mail.gmail.com>
 <0026f562-d403-4f97-b9dc-3fe3a279e8c9@postgrespro.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>
Content-Language: en-AU
In-Reply-To: <ab8aedce-7a2f-4bb2-bfad-3f1ad91e7336@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/41ba3d47-2a48-476c-88d4-6ebd889a7af2%40gmail.com>
Precedence: bulk

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.

-- 
regards, Andrei Lepikhov