Content-Type: multipart/mixed; boundary="------------4MDoaihohn0fsiN9oMvGyh8M"
Message-ID: <ab8aedce-7a2f-4bb2-bfad-3f1ad91e7336@gmail.com>
Date: Mon, 13 Jan 2025 10:39:23 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
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>
 <CAPpHfdvxF1OZUoJr2bg8cmAnty-KyRkswPa3hCPSmR5gSx7-Yg@mail.gmail.com>
 <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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ab8aedce-7a2f-4bb2-bfad-3f1ad91e7336%40gmail.com>
Precedence: bulk

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

On 1/13/25 01:39, Alexander Korotkov wrote:
> The revised patch is attached.  Most notably it revises
> group_similar_or_args() to have the same notion of const-ness as
> others.  In that function we split potential index key and constant
> early to save time on enumerating all possible index keys.  But it
> appears to be possible to split by relids bitmapsets: index key should
> use our relid, while const shouldn't.  Other that that, comments,
> commit message and naming are revised.
Hmm, I would say we should carefully review this code.
Curiously, this patch has activated the dormant problem of duplicated 
clauses in joinorclauses. Look:

EXPLAIN (COSTS OFF)
SELECT * FROM bitmap_split_or t1, bitmap_split_or t2
WHERE t1.a=t2.b OR t1.a=1;

  Nested Loop
    ->  Seq Scan on bitmap_split_or t2
    ->  Bitmap Heap Scan on bitmap_split_or t1
          Recheck Cond: (((a = t2.b) OR (a = 1)) AND
                         ((a = t2.b) OR (a = 1)))
          ->  Bitmap Index Scan on t_a_b_idx
                Index Cond: ((a = ANY (ARRAY[t2.b, 1])) AND
                             (a = ANY (ARRAY[t2.b, 1])))

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.

-- 
regards, Andrei Lepikhov
--------------4MDoaihohn0fsiN9oMvGyh8M
Content-Type: text/x-patch; charset=UTF-8; name="joinorclauses-fix.diff"
Content-Disposition: attachment; filename="joinorclauses-fix.diff"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2luZHhwYXRoLmMgYi9z
cmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jCmluZGV4IGMwMzU0N2E5OTcu
LjJhODYyOThkOWQgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2lu
ZHhwYXRoLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvaW5keHBhdGguYwpA
QCAtMjQ0MCw3ICsyNDQwLDcgQEAgbWF0Y2hfam9pbl9jbGF1c2VzX3RvX2luZGV4KFBsYW5u
ZXJJbmZvICpyb290LAogCiAJCS8qIFBvdGVudGlhbGx5IHVzYWJsZSwgc28gc2VlIGlmIGl0
IG1hdGNoZXMgdGhlIGluZGV4IG9yIGlzIGFuIE9SICovCiAJCWlmIChyZXN0cmljdGlvbl9p
c19vcl9jbGF1c2UocmluZm8pKQotCQkJKmpvaW5vcmNsYXVzZXMgPSBsYXBwZW5kKCpqb2lu
b3JjbGF1c2VzLCByaW5mbyk7CisJCQkqam9pbm9yY2xhdXNlcyA9IGxpc3RfYXBwZW5kX3Vu
aXF1ZV9wdHIoKmpvaW5vcmNsYXVzZXMsIHJpbmZvKTsKIAogCQltYXRjaF9jbGF1c2VfdG9f
aW5kZXgocm9vdCwgcmluZm8sIGluZGV4LCBjbGF1c2VzZXQpOwogCX0KZGlmZiAtLWdpdCBh
L3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY3JlYXRlX2luZGV4Lm91dCBiL3NyYy90ZXN0
L3JlZ3Jlc3MvZXhwZWN0ZWQvY3JlYXRlX2luZGV4Lm91dAppbmRleCA4MDExYzE0MWJmLi40
N2JkZWUxZDQxIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2NyZWF0
ZV9pbmRleC5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jcmVhdGVfaW5k
ZXgub3V0CkBAIC0zMjU1LDYgKzMyNTUsMTYgQEAgQ1JFQVRFIElOREVYIHRfYl9jX2lkeCBP
TiBiaXRtYXBfc3BsaXRfb3IgKGIsIGMpOwogQ1JFQVRFIFNUQVRJU1RJQ1MgdF9hX2Jfc3Rh
dCAobWN2KSBPTiBhLCBiIEZST00gYml0bWFwX3NwbGl0X29yOwogQ1JFQVRFIFNUQVRJU1RJ
Q1MgdF9iX2Nfc3RhdCAobWN2KSBPTiBiLCBjIEZST00gYml0bWFwX3NwbGl0X29yOwogQU5B
TFlaRSBiaXRtYXBfc3BsaXRfb3I7CitFWFBMQUlOIChDT1NUUyBPRkYpCitTRUxFQ1QgKiBG
Uk9NIGJpdG1hcF9zcGxpdF9vciB0MSwgYml0bWFwX3NwbGl0X29yIHQyIFdIRVJFIHQxLmE9
dDIuYiBPUiB0MS5hPTE7CisgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAg
ICAgICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyBOZXN0ZWQgTG9vcAorICAgLT4gIFNlcSBTY2Fu
IG9uIGJpdG1hcF9zcGxpdF9vciB0MgorICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdF9hX2Jf
aWR4IG9uIGJpdG1hcF9zcGxpdF9vciB0MQorICAgICAgICAgSW5kZXggQ29uZDogKGEgPSBB
TlkgKEFSUkFZW3QyLmIsIDFdKSkKKyg0IHJvd3MpCisKIEVYUExBSU4gKENPU1RTIE9GRikK
IFNFTEVDVCAqIEZST00gYml0bWFwX3NwbGl0X29yIFdIRVJFIGEgPSAxIEFORCAoYiA9IDEg
T1IgYiA9IDIpIEFORCBjID0gMjsKICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVS
WSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgIApkaWZmIC0tZ2l0IGEvc3JjL3Rl
c3QvcmVncmVzcy9zcWwvY3JlYXRlX2luZGV4LnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2NyZWF0ZV9pbmRleC5zcWwKaW5kZXggMDY4YzY2Yjk1YS4uYmMxMmQyZjA5OCAxMDA2NDQK
LS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY3JlYXRlX2luZGV4LnNxbAorKysgYi9zcmMv
dGVzdC9yZWdyZXNzL3NxbC9jcmVhdGVfaW5kZXguc3FsCkBAIC0xMzY3LDYgKzEzNjcsOCBA
QCBDUkVBVEUgU1RBVElTVElDUyB0X2FfYl9zdGF0IChtY3YpIE9OIGEsIGIgRlJPTSBiaXRt
YXBfc3BsaXRfb3I7CiBDUkVBVEUgU1RBVElTVElDUyB0X2JfY19zdGF0IChtY3YpIE9OIGIs
IGMgRlJPTSBiaXRtYXBfc3BsaXRfb3I7CiBBTkFMWVpFIGJpdG1hcF9zcGxpdF9vcjsKIEVY
UExBSU4gKENPU1RTIE9GRikKK1NFTEVDVCAqIEZST00gYml0bWFwX3NwbGl0X29yIHQxLCBi
aXRtYXBfc3BsaXRfb3IgdDIgV0hFUkUgdDEuYT10Mi5iIE9SIHQxLmE9MTsKK0VYUExBSU4g
KENPU1RTIE9GRikKIFNFTEVDVCAqIEZST00gYml0bWFwX3NwbGl0X29yIFdIRVJFIGEgPSAx
IEFORCAoYiA9IDEgT1IgYiA9IDIpIEFORCBjID0gMjsKIERST1AgVEFCTEUgYml0bWFwX3Nw
bGl0X29yOwogCg==

--------------4MDoaihohn0fsiN9oMvGyh8M--