public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Robert Haas <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: jian he <[email protected]>
Cc: [email protected]
Cc: Marcos Pegoraro <[email protected]>
Cc: [email protected]
Cc: Andrei Lepikhov <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Ranier Vilela <[email protected]>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Thu, 27 Jun 2024 23:06:49 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAPpHfdsfdoX_sDbY6eCzgngnt93HcX=NFp0_hrkq0NrSu9r=ag@mail.gmail.com>
<[email protected]>
<CA+Tgmobu0DUFCTF28DuAi975mEc4xYqX3xyt8RA0WbnyrYg+Fw@mail.gmail.com>
<[email protected]>
<CA+TgmoY=NFe2gdLB5QOkU0ALfNUXrbdFWpVAd-WYRGAU1EZoCA@mail.gmail.com>
<CAH2-WzmuJRV59y2LZGrjnNRgzpv57jbfJCdZqEAeDb5Q4fbp9A@mail.gmail.com>
<CA+TgmoZxZLcee6CM0Xd00x5DBM9Qp4BLgjC6mDzs6yq5b3SuxA@mail.gmail.com>
<CAH2-WzncwApGKwZVyZUWBRncjgXE38iiuCPsXz3s1NgLMEQNuA@mail.gmail.com>
<CA+TgmoYjBgPgNCuGhMmFp_4HdaZDiEBRFzDsW9p92DK8sie_Fg@mail.gmail.com>
<CAH2-WzmKYo2pnQcKAhjZFofW8Ow23B4CZHCUSpCZ+z5YR4eXvQ@mail.gmail.com>
<[email protected]>
<CA+TgmoarYLO6PL+FEnXJ6A-57KsVsotpvHnB771M-wXQOGNy9w@mail.gmail.com>
<[email protected]>
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
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: POC, WIP: OR-clause support for indexes
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox