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 1sMvOS-005dDo-RY for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Jun 2024 20:06:57 +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 1sMvOR-00D6sq-8o for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Jun 2024 20:06:55 +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 1sMvOQ-00D6sh-ND for pgsql-hackers@lists.postgresql.org; Thu, 27 Jun 2024 20:06:55 +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 1sMvOM-003RBY-V5 for pgsql-hackers@postgresql.org; Thu, 27 Jun 2024 20:06:53 +0000 Received: from [10.10.11.95] (unknown [83.138.59.251]) (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 1DB51E21112; Thu, 27 Jun 2024 23:06:48 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1719518808; bh=SESRLL1cjjL3zUd1Vr71Ttwr2LrrMMwTGt7wfsNVp1M=; h=Message-ID:Date:User-Agent:Subject:From:To:Cc:References: In-Reply-To:From; b=BPztd8zZWKTSkRq8AuquZEqkfcYqOIlxhtuowtwj43QMjAzJP6DDidrv5fd2keEz8 Y640pmJNvKgtJdgQ6SMlrKFpb8coBdT07CD85HoD73MGk9p9Q9jVnM7kJirFcFoP6w pG30gy+vrZpOtcaZL1cyLo7HSm2SPGQ1vhDgK+oyvT2NEbmnj2jSogYRdMSMJtlbg3 p8wM+oor06jgcPU+x22vYu49PoL1oxN92W4tm7lIES2r4dwQARM8BiY0M0ivd5qwZF rVH7cxFJUFGbmo641j6wa/N9bhkaONyQjv4x8lXqQ/3SxU+864XJxV/EMNEBD3S/ZE P+EFOc1sTn9rw== Content-Type: multipart/alternative; boundary="------------oZpAUepVczX50aGG1dnUVpiJ" Message-ID: <75265eac-aa87-41dc-bf1b-13c11ce2d499@postgrespro.ru> Date: Thu, 27 Jun 2024 23:06:49 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: POC, WIP: OR-clause support for indexes From: Alena Rybakina To: Robert Haas Cc: Peter Geoghegan , Alexander Korotkov , jian he , pgsql-hackers@postgresql.org, Marcos Pegoraro , teodor@sigaev.ru, Andrei Lepikhov , Tomas Vondra , Peter Eisentraut , Ranier Vilela References: <567ED6CA.2040504@sigaev.ru> <6850c306-4e9d-40b7-8096-1f3c7d29cd9e@postgrespro.ru> <0f50882c-e639-4856-aab6-6ccfec848164@postgrespro.ru> Content-Language: en-US In-Reply-To: X-KSMG-AntiPhishing: NotDetected, bases: 2024/06/27 19:15: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/27 17:06:00 #25741087 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. --------------oZpAUepVczX50aGG1dnUVpiJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha misunderstandingof howto correctlycreatea conditionfor"OR"expressionsthatare notsubjectto transformation. > > For example,the expressions b=1in the query below: > > alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = > ANY(ARRAY[5,4])) or (b=1); QUERY PLAN > ---------------------------------------------------------------------------------- > Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR > (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows) > > I see that two expressions have remained unchanged and it only works > for "AND" binary operations. > > But I think it might be worth applying this together, where does the > optimizer generate indexes (build_paths_for_OR function)? > Sorry, it works) I needed to create one more index for b column. Just in case, I gave an example of a complete case, otherwise it might not be entirely clear: alena@postgres=# create table x (a int, b int); CREATE TABLE alena@postgres=# create index a_idx on x(a);                         insert into x select id,id from generate_series(1, 5000) as id; CREATE INDEX INSERT 0 5000 alena@postgres=# analyze; ANALYZE alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN ---------------------------------------------------------------------------------- Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows) alena@postgres=# create index b_idx on x(b); CREATE INDEX alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4]))  or (b=1);                                 QUERY PLAN --------------------------------------------------------------------------  Bitmap Heap Scan on x  (cost=12.87..21.68 rows=1 width=8)    Recheck Cond: ((a = ANY ('{5,4}'::integer[])) OR (b = 1))    ->  BitmapOr  (cost=12.87..12.87 rows=3 width=0)          ->  Bitmap Index Scan on a_idx  (cost=0.00..8.58 rows=2 width=0)                Index Cond: (a = ANY ('{5,4}'::integer[]))          ->  Bitmap Index Scan on b_idx  (cost=0.00..4.29 rows=1 width=0)                Index Cond: (b = 1) (7 rows) -- Regards, Alena Rybakina Postgres Professional:http://www.postgrespro.com The Russian Postgres Company --------------oZpAUepVczX50aGG1dnUVpiJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit To be honest, I've already started writing code to do this, but I'm faced with a misunderstanding of how to correctly create a condition for "OR" expressions that are not subject to transformation.

For example, the expressions b=1 in the query below:

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN ---------------------------------------------------------------------------------- Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows)

I see that two expressions have remained unchanged and it only works for "AND" binary operations.

But I think it might be worth applying this together, where does the optimizer generate indexes (build_paths_for_OR function)?


Sorry, it works) I needed to create one more index for b column.

Just in case, I gave an example of a complete case, otherwise it might not be entirely clear:

alena@postgres=# create table x (a int, b int);
CREATE TABLE
alena@postgres=# create index a_idx on x(a);
                        insert into x select id,id from generate_series(1, 5000) as id;
CREATE INDEX
INSERT 0 5000
alena@postgres=# analyze;
ANALYZE

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN ---------------------------------------------------------------------------------- Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows)

alena@postgres=# create index b_idx on x(b);

CREATE INDEX

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = ANY(ARRAY[5,4]))  or (b=1);
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Bitmap Heap Scan on x  (cost=12.87..21.68 rows=1 width=8)
   Recheck Cond: ((a = ANY ('{5,4}'::integer[])) OR (b = 1))
   ->  BitmapOr  (cost=12.87..12.87 rows=3 width=0)
         ->  Bitmap Index Scan on a_idx  (cost=0.00..8.58 rows=2 width=0)
               Index Cond: (a = ANY ('{5,4}'::integer[]))
         ->  Bitmap Index Scan on b_idx  (cost=0.00..4.29 rows=1 width=0)
               Index Cond: (b = 1)
(7 rows)

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