Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJEAW-003TNV-88 for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Jun 2024 15:21:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sJEAU-000hOT-2A for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Jun 2024 15:21:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJEAT-000hOJ-C6 for pgsql-hackers@lists.postgresql.org; Mon, 17 Jun 2024 15:21:14 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJEAQ-001lbm-0I for pgsql-hackers@postgresql.org; Mon, 17 Jun 2024 15:21:13 +0000 Received: from [10.4.12.74] (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: a.rybakina@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 4A2CCE20EAD; Mon, 17 Jun 2024 18:21:07 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1718637667; bh=gBKGh2ii7R3uYqjY6Tltx5ByTlYLP0yvjlx/ILd+M9U=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=Y2P1tlig7Hkouza7RtbD8HCUCu80bcKfIflLqfUopZn4LGXKKFfssUsE/8aeT2LVU aJKA63Cc7AS/YgVj2TUqejjljxc0OZxQzAEhLzlyLRu24qbkmRDMliqqiEJxcVPb1x denHCrELc3kf6Mxk5WlSGgDhvGjYZ40M3kY2wDoX3G0QiuUgkFHEPy71C1ekiOYzaJ EnqzfrtRKWjfCBVlQOrWa4JOfQJkKMAvVm6YZHUVXVWKQubFPNFB8VdPIym5jjJL9I zJ0iNSioEhAOPdml0zCcIv6VFVpqanrnkMHYjo+cUdpFCQpLiRiG0t88Iwk91woQFA 7eKYqpajfcnug== Content-Type: multipart/alternative; boundary="------------nZObwKVk100380W6E0RIbWTM" Message-ID: <196b44d5-f4e5-41a0-9a8f-9fbf26a13760@postgrespro.ru> Date: Mon, 17 Jun 2024 18:21:09 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: POC, WIP: OR-clause support for indexes To: Alexander Korotkov Cc: Andrei Lepikhov , jian he , Robert Haas , pgsql-hackers@postgresql.org, Peter Geoghegan , "Finnerty, Jim" , Marcos Pegoraro , teodor@sigaev.ru, Tomas Vondra , Peter Eisentraut , Ranier Vilela References: <567ED6CA.2040504@sigaev.ru> <7389d0dd-05d5-41b7-a12d-2e73f939f851@postgrespro.ru> <74e3c8bc-dccd-45e5-ad2d-6a6e96fc5864@postgrespro.ru> <6d27d752-db0b-4cac-9843-6ba3dd7a1e94@postgrespro.ru> <7d5aed92-d4cc-4b76-8ae0-051d182c9eec@postgrespro.ru> Content-Language: en-US From: Alena Rybakina In-Reply-To: X-KSMG-AntiPhishing: NotDetected, bases: 2024/06/17 14:47:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/06/17 12:47:00 #25641689 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------nZObwKVk100380W6E0RIbWTM Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 17.06.2024 15:11, Alexander Korotkov wrote: > On Mon, Jun 17, 2024 at 1:33 PM Alena Rybakina > wrote: >> I noticed that 7 libraries have been added to src/backend/optimizer/plan/initsplan.c, and as far as I remember, Tom Lane has already expressed doubts about the approach that requires adding a large number of libraries [0], but I'm afraid I'm out of ideas about alternative approach. > Thank you for pointing. Right, the number of extra headers included > was one of points for criticism on this patch. I'll look to move this > functionality elsewhere, while the stage of transformation could > probably be the same. Yes, I thing so. >> In addition, I checked the fix in the previous cases that you wrote earlier [1] and noticed that SeqScan continues to generate, unfortunately, without converting expressions: > I've rechecked and see I made wrong conclusion about this. The plan > regression is still here. But I'm still looking to workaround this > without extra GUC. > > I think we need to additionally do something like [1], but take > further steps to avoid planning overhead when not necessary. Iagreewithyoutoreconsiderthisplacein detailonceagain,becauseotherwiseit lookslike we're likelyto runinto aperformanceissue. > In > particular, I think we should only consider splitting SAOP for bitmap > OR in the following cases: > 1. There are partial indexes with predicates over target column. Frankly, I see that we will need to split SAOP anyway to check it, right? > 2. There are multiple indexes covering target column and different > subsets of other columns presented in restrictions. I see two cases in one. First, we need to check whether there is an index for the columns specified in the restrictlist, and secondly, the index ranges for which the conditions fall into the "OR" expressions. > 3. There are indexes covreing target column without support of SAOP > (amsearcharray == false). > Hopefully this should skip generation of useless bitmap paths in > majority cases. Thoughts? I'm notsureIfullyunderstandhowusefulthiscanbe.Couldyouexplainit to mein more detail? > Links. > 1.https://www.postgresql.org/message-id/67bd918d-285e-44d2-a207-f52d9a4c35e6%40postgrespro.ru -- Regards, Alena Rybakina Postgres Professional:http://www.postgrespro.com The Russian Postgres Company --------------nZObwKVk100380W6E0RIbWTM Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 17.06.2024 15:11, Alexander Korotkov wrote:
On Mon, Jun 17, 2024 at 1:33 PM Alena Rybakina
<a.rybakina@postgrespro.ru> wrote:

I noticed that 7 libraries have been added to src/backend/optimizer/plan/initsplan.c, and as far as I remember, Tom Lane has already expressed doubts about the approach that requires adding a large number of libraries [0], but I'm afraid I'm out of ideas about alternative approach.

Thank you for pointing.  Right, the number of extra headers included
was one of points for criticism on this patch.  I'll look to move this
functionality elsewhere, while the stage of transformation could
probably be the same.
Yes, I thing so. 
In addition, I checked the fix in the previous cases that you wrote earlier [1] and noticed that SeqScan continues to generate, unfortunately, without converting expressions:

I've rechecked and see I made wrong conclusion about this.  The plan
regression is still here.  But I'm still looking to workaround this
without extra GUC.

I think we need to additionally do something like [1], but take
further steps to avoid planning overhead when not necessary.

I agree with you to reconsider this place in detail once again, because otherwise it looks like we're likely to run into a performance issue.
In
particular, I think we should only consider splitting SAOP for bitmap
OR in the following cases:
1. There are partial indexes with predicates over target column.
Frankly, I see that we will need to split SAOP anyway to check it, right?
2. There are multiple indexes covering target column and different
subsets of other columns presented in restrictions.
I see two cases in one. First, we need to check whether there is an index for the columns specified in the restrictlist, and secondly, the index ranges for which the conditions fall into the "OR" expressions. 
3. There are indexes covreing target column without support of SAOP
(amsearcharray == false).
Hopefully this should skip generation of useless bitmap paths in
majority cases. Thoughts?
I'm not sure I fully understand how useful this can be. Could you explain it to me in more detail? 
Links.
1. https://www.postgresql.org/message-id/67bd918d-285e-44d2-a207-f52d9a4c35e6%40postgrespro.ru

-- 
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--------------nZObwKVk100380W6E0RIbWTM--