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 1qw6KH-009cTd-DH for pgsql-hackers@arkaria.postgresql.org; Thu, 26 Oct 2023 19:47:30 +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 1qw6KF-004Tqx-U7 for pgsql-hackers@arkaria.postgresql.org; Thu, 26 Oct 2023 19:47:27 +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 1qw6KF-004Tqo-BM for pgsql-hackers@lists.postgresql.org; Thu, 26 Oct 2023 19:47:27 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qw6KB-002SGu-DF for pgsql-hackers@postgresql.org; Thu, 26 Oct 2023 19:47:26 +0000 Received: from [10.4.12.205] (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 99F6DE20E5B; Thu, 26 Oct 2023 22:47:20 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1698349640; bh=CRxzSbZfUmiy3jwllK47Os+x7nQSHagyG7utNhYeazc=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=DIe18QsHtNGgJKZK/Y1Y9uZ/NSSm1i46WvlLGp9v9lCgr7gBD3UZxew/jjPmb6jo2 y/kKVd9NUi6boThtZsZKqU/8HTX2kysA8nty7FYKxMDQawHvHQ6HRDvSfVn1FeKJXH ZTsm0BSY20cY2sPEZJjm/Ks9JJGxhOHw/VHkRpknbfguIHDikrzeC0kAn/y3/eC+GW JUOdwL1KXddd1p3vsBQFhIHhrtm2BMSk9ESZc5rnyLACQkPxtQXXbeQPb4/xJr6yAg Gnuspjm9vjRlxLPATKxwXs7Ua0ErqEea2OFyLRHmJIfgbl8xuCShTTJ/0z+BliWWkg +gymkFDstWITA== Content-Type: multipart/alternative; boundary="------------O5WS02MuJ9GXruPDcSKtOcJU" Message-ID: Date: Thu, 26 Oct 2023 22:47:20 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: POC, WIP: OR-clause support for indexes To: Robert Haas , Alexander Korotkov Cc: pgsql-hackers@postgresql.org, "Finnerty, Jim" , Marcos Pegoraro , Andrey Lepikhov , teodor@sigaev.ru, Ranier Vilela , Tomas Vondra , Peter Geoghegan , Peter Eisentraut References: <567ED6CA.2040504@sigaev.ru> <4bac271d-1700-db24-74ac-8414f2baf9fd@postgrespro.ru> <89fb769c-7d45-f0c5-a9b3-b706c9661c57@postgrespro.ru> <668892c1-fb11-3a79-ce5e-1c194b7b3263@postgrespro.ru> Content-Language: en-US From: Alena Rybakina In-Reply-To: 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. --------------O5WS02MuJ9GXruPDcSKtOcJU Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi! Thank you for your feedback! On 25.10.2023 22:54, Robert Haas wrote: > On Sat, Oct 14, 2023 at 6:37 PM Alexander Korotkov wrote: >> Regarding the GUC parameter, I don't see we need a limit. It's not >> yet clear whether a small number or a large number of OR clauses are >> more favorable for transformation. I propose to have just a boolean >> enable_or_transformation GUC. > That's a poor solution. So is the GUC patch currently has > (or_transform_limit). What you need is a heuristic that figures out > fairly reliably whether the transformation is going to be better or > worse. Or else, do the whole thing in some other way that is always > same-or-better. > > In general, adding GUCs makes sense when the user knows something that > we can't know. For example, shared_buffers makes some sense because, > even if we discovered how much memory the machine has, we can't know > how much of it the user wants to devote to PostgreSQL as opposed to > anything else. And track_io_timing makes sense because we can't know > whether the user wants to pay the price of gathering that additional > data. But GUCs are a poor way of handling cases where the real problem > is that we don't know what code to write. In this case, some queries > will be better with enable_or_transformation=on, and some will be > better with enable_or_transformation=off. Since we don't know which > will work out better, we make the user figure it out and set the GUC, > possibly differently for each query. That's terrible. It's the query > optimizer's whole job to figure out which transformations will speed > up the query. It shouldn't turn around and punt the decision back to > the user. > > Notice that superficially-similar GUCs like enable_seqscan aren't > really the same thing at all. That's just for developer testing and > debugging. Nobody expects that you have to adjust that GUC on a > production system - ever. I noticed that the costs of expressions are different and it can help to assess when it is worth leaving the conversion, when not. With small amounts of "OR" elements, the cost of orexpr is lower than with "ANY", on the contrary, higher. postgres=# SET or_transform_limit = 500; EXPLAIN (analyze) SELECT oid,relname FROM pg_class WHERE   oid = 13779 AND (oid = 2 OR oid = 4 OR oid = 5) ; SET                                                           QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------  Index Scan using pg_class_oid_index on pg_class  (*cost=0.27..8.30* rows=1 width=68) (actual time=0.105..0.106 rows=0 loops=1)    Index Cond: (oid = '13779'::oid)    Filter: ((oid = '2'::oid) OR (oid = '4'::oid) OR (oid = '5'::oid))  Planning Time: 0.323 ms  Execution Time: 0.160 ms (5 rows) postgres=# SET or_transform_limit = 0; EXPLAIN (analyze) SELECT oid,relname FROM pg_class WHERE   oid = 13779 AND (oid = 2 OR oid = 4 OR oid = 5) ; SET                                                           QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------  Index Scan using pg_class_oid_index on pg_class  (*cost=0.27..16.86* rows=1 width=68) (actual time=0.160..0.161 rows=0 loops=1)    Index Cond: ((oid = ANY (ARRAY['2'::oid, '4'::oid, '5'::oid])) AND (oid = '13779'::oid))  Planning Time: 4.515 ms  Execution Time: 0.313 ms (4 rows) Index Scan using pg_class_oid_index on pg_class  (*cost=0.27..2859.42* rows=414 width=68) (actual time=1.504..34.183 rows=260 loops=1)    Index Cond: (oid = ANY (ARRAY['1'::oid, '2'::oid, '3'::oid, '4'::oid, '5'::oid, '6'::oid, '7'::oid, Bitmap Heap Scan on pg_class  (*cost=43835.00..54202.14* rows=414 width=68) (actual time=39.958..41.293 rows=260 loops=1)    Recheck Cond: ((oid = '1'::oid) OR (oid = '2'::oid) OR (oid = '3'::oid) OR (oid = '4'::oid) OR (oid = I think we could see which value is lower, and if lower with expressions converted to ANY, then work with it further, otherwise work with the original "OR" expressions. But we still need to make this conversion to find out its cost. In addition, I will definitely have to postpone the transformation of "OR" to "ANY" at the stage of creating indexes (?) or maybe a little earlier so that I have to count only the cost of the transformed expression. --------------O5WS02MuJ9GXruPDcSKtOcJU Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi! Thank you for your feedback!

On 25.10.2023 22:54, Robert Haas wrote:
On Sat, Oct 14, 2023 at 6:37 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:

Regarding the GUC parameter, I don't see we need a limit.  It's not
yet clear whether a small number or a large number of OR clauses are
more favorable for transformation.  I propose to have just a boolean
enable_or_transformation GUC.

That's a poor solution. So is the GUC patch currently has
(or_transform_limit). What you need is a heuristic that figures out
fairly reliably whether the transformation is going to be better or
worse. Or else, do the whole thing in some other way that is always
same-or-better.

In general, adding GUCs makes sense when the user knows something that
we can't know. For example, shared_buffers makes some sense because,
even if we discovered how much memory the machine has, we can't know
how much of it the user wants to devote to PostgreSQL as opposed to
anything else. And track_io_timing makes sense because we can't know
whether the user wants to pay the price of gathering that additional
data. But GUCs are a poor way of handling cases where the real problem
is that we don't know what code to write. In this case, some queries
will be better with enable_or_transformation=on, and some will be
better with enable_or_transformation=off. Since we don't know which
will work out better, we make the user figure it out and set the GUC,
possibly differently for each query. That's terrible. It's the query
optimizer's whole job to figure out which transformations will speed
up the query. It shouldn't turn around and punt the decision back to
the user.

Notice that superficially-similar GUCs like enable_seqscan aren't
really the same thing at all. That's just for developer testing and
debugging. Nobody expects that you have to adjust that GUC on a
production system - ever.

I noticed that the costs of expressions are different and it can help to assess when it is worth leaving the conversion, when not.

With small amounts of "OR" elements, the cost of orexpr is lower than with "ANY", on the contrary, higher.

postgres=# SET or_transform_limit = 500;
EXPLAIN (analyze)
SELECT oid,relname FROM pg_class
WHERE
  oid = 13779 AND (oid = 2 OR oid = 4 OR oid = 5)
;
SET
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.30 rows=1 width=68) (actual time=0.105..0.106 rows=0 loops=1)
   Index Cond: (oid = '13779'::oid)
   Filter: ((oid = '2'::oid) OR (oid = '4'::oid) OR (oid = '5'::oid))
 Planning Time: 0.323 ms
 Execution Time: 0.160 ms

(5 rows)

postgres=# SET or_transform_limit = 0;
EXPLAIN (analyze)
SELECT oid,relname FROM pg_class
WHERE
  oid = 13779 AND (oid = 2 OR oid = 4 OR oid = 5)
;
SET
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=0.27..16.86 rows=1 width=68) (actual time=0.160..0.161 rows=0 loops=1)
   Index Cond: ((oid = ANY (ARRAY['2'::oid, '4'::oid, '5'::oid])) AND (oid = '13779'::oid))
 Planning Time: 4.515 ms
 Execution Time: 0.313 ms
(4 rows)


Index Scan using pg_class_oid_index on pg_class  (cost=0.27..2859.42 rows=414 width=68) (actual time=1.504..34.183 rows=260 loops=1)
   Index Cond: (oid = ANY (ARRAY['1'::oid, '2'::oid, '3'::oid, '4'::oid, '5'::oid, '6'::oid, '7'::oid,

Bitmap Heap Scan on pg_class  (cost=43835.00..54202.14 rows=414 width=68) (actual time=39.958..41.293 rows=260 loops=1)
   Recheck Cond: ((oid = '1'::oid) OR (oid = '2'::oid) OR (oid = '3'::oid) OR (oid = '4'::oid) OR (oid =

I think we could see which value is lower, and if lower with expressions converted to ANY, then work with it further, otherwise work with the original "OR" expressions. But we still need to make this conversion to find out its cost.

In addition, I will definitely have to postpone the transformation of "OR" to "ANY" at the stage of creating indexes (?) or maybe a little earlier so that I have to count only the cost of the transformed expression.

--------------O5WS02MuJ9GXruPDcSKtOcJU--