public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Andrey Lepikhov <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Tue, 27 Jun 2023 16:19:48 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAH2-WzmD5u5kCZG0qMtVySz8VB1_drOiX=j0buDufK-EJc3YkQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAB-JLwb7NQHUdrc9_coV+kKGYF7hBb+XktY6TkYj4aejCqF9DQ@mail.gmail.com>
<[email protected]>
<CAH2-WzmD5u5kCZG0qMtVySz8VB1_drOiX=j0buDufK-EJc3YkQ@mail.gmail.com>
On 26.06.2023 06:18, Peter Geoghegan wrote:
> On Sun, Jun 25, 2023 at 6:48 PM Alena Rybakina<[email protected]> wrote:
>> I finished writing the code patch for transformation "Or" expressions to "Any" expressions.
> This seems interesting to me. I'm currently working on improving
> nbtree's "native execution of ScalarArrayOpExpr quals" (see commit
> 9e8da0f7 for background information). That is relevant to what you're
> trying to do here.
>
> Right now nbtree's handling of ScalarArrayOpExpr is rather
> inefficient. The executor does pass the index scan an array of
> constants, so the whole structure already allows the nbtree code to
> execute the ScalarArrayOpExpr in whatever way would be most efficient.
> There is only one problem: it doesn't really try to do so. It more or
> less just breaks down the large ScalarArrayOpExpr into "mini" queries
> -- one per constant. Internally, query execution isn't significantly
> different to executing many of these "mini" queries independently. We
> just sort and deduplicate the arrays. We don't intelligently decide
> which pages dynamically. This is related to skip scan.
>
> Attached is an example query that shows the problem. Right now the
> query needs to access a buffer containing an index page a total of 24
> times. It's actually accessing the same 2 pages 12 times. My draft
> patch only requires 2 buffer accesses -- because it "coalesces the
> array constants together" dynamically at run time. That is a little
> extreme, but it's certainly possible.
>
> BTW, this project is related to skip scan. It's part of the same
> family of techniques -- MDAM techniques. (I suppose that that's
> already true for ScalarArrayOpExpr execution by nbtree, but without
> dynamic behavior it's not nearly as valuable as it could be.)
>
> If executing ScalarArrayOpExprs was less inefficient in these cases
> then the planner could be a lot more aggressive about using them.
> Seems like these executor improvements might go well together with
> what you're doing in the planner. Note that I have to "set
> random_page_cost=0.1" to get the planner to use all of the quals from
> the query as index quals. It thinks (correctly) that the query plan is
> very inefficient. That happens to match reality right now, but the
> underlying reality could change significantly. Something to think
> about.
>
> --
> Peter Geoghegan
Thank you for your feedback, your work is also very interesting and
important, and I will be happy to review it. I learned something new
from your letter, thank you very much for that!
I analyzed the buffer consumption when I ran control regression tests
using my patch. diff shows me that there is no difference between the
number of buffer block scans without and using my patch, as far as I
have seen. (regression.diffs)
In addition, I analyzed the scheduling and duration of the execution
time of the source code and with my applied patch. I generated 20
billion data from pgbench and plotted the scheduling and execution time
depending on the number of "or" expressions.
By runtime, I noticed a clear acceleration for queries when using the
index, but I can't say the same when the index is disabled.
At first I turned it off in this way:
1)enable_seqscan='off'
2)enable_indexonlyscan='off'
enable_indexscan='off'
Unfortunately, it is not yet clear which constant needs to be set when
the transformation needs to be done, I will still study in detail. (the
graph for all this is presented in graph1.svg)
\\
--
Regards,
Alena Rybakina
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/create_index.out /home/alena/postgrespro7/src/test/regress/results/create_index.out
--- /home/alena/postgrespro7/src/test/regress/expected/create_index.out 2023-06-27 10:20:52.287769054 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/create_index.out 2023-06-27 10:32:20.701220051 +0300
@@ -1838,26 +1838,14 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1 (actual rows=1 loops=1)
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- Heap Blocks: exact=1
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1 (actual rows=1 loops=1)
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
Buffers: shared hit=5 read=2
- -> BitmapOr (actual rows=0 loops=1)
- Buffers: shared hit=4 read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- Buffers: shared read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=1 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 42))
- Buffers: shared hit=2
Planning:
Buffers: shared hit=51
-(17 rows)
+(5 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1869,12 +1857,12 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=5 read=3
-> Bitmap Heap Scan on tenk1 (actual rows=10 loops=1)
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
Heap Blocks: exact=10
Buffers: shared hit=5 read=3
-> BitmapAnd (actual rows=0 loops=1)
@@ -1882,15 +1870,10 @@
-> Bitmap Index Scan on tenk1_hundred (actual rows=100 loops=1)
Index Cond: (hundred = 42)
Buffers: shared read=2
- -> BitmapOr (actual rows=0 loops=1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=20 loops=1)
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
Buffers: shared hit=3 read=1
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 42)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 99)
- Buffers: shared hit=1 read=1
-(19 rows)
+(14 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/join.out /home/alena/postgrespro7/src/test/regress/results/join.out
--- /home/alena/postgrespro7/src/test/regress/expected/join.out 2023-06-27 10:21:22.096306419 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/join.out 2023-06-27 10:32:26.049288441 +0300
@@ -4207,10 +4207,10 @@
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Nested Loop (actual rows=201 loops=1)
- Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
Rows Removed by Join Filter: 102
Buffers: shared hit=98
-> Bitmap Heap Scan on tenk1 b (actual rows=101 loops=1)
@@ -4228,7 +4228,7 @@
-> Materialize (actual rows=3 loops=101)
Buffers: shared hit=8
-> Bitmap Heap Scan on tenk1 a (actual rows=3 loops=1)
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
Heap Blocks: exact=2
Buffers: shared hit=8
-> BitmapOr (actual rows=0 loops=1)
@@ -4236,13 +4236,10 @@
-> Bitmap Index Scan on tenk1_unique1 (actual rows=1 loops=1)
Index Cond: (unique1 = 1)
Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 3)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 7)
- Buffers: shared hit=2
-(33 rows)
+ -> Bitmap Index Scan on tenk1_unique2 (actual rows=2 loops=1)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ Buffers: shared hit=4
+(30 rows)
--
-- test placement of movable quals in a parameterized join tree
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out /home/alena/postgrespro7/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out 2023-06-27 00:35:33.761010034 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/stats_ext.out 2023-06-27 10:32:37.537436241 +0300
@@ -1322,19 +1322,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out /home/alena/postgrespro7/src/test/regress/results/partition_prune.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out 2023-06-27 10:31:39.596703531 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_prune.out 2023-06-27 10:32:48.285575599 +0300
@@ -82,23 +82,25 @@
(2 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-(5 rows)
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ Planning:
+ Buffers: shared hit=16
+(7 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
@@ -518,8 +520,10 @@
QUERY PLAN
----------------------------------------------
Seq Scan on rlp2 rlp (actual rows=0 loops=1)
- Filter: ((a = 1) OR (a = 7))
-(2 rows)
+ Filter: (a = ANY ('{1,7}'::integer[]))
+ Planning:
+ Buffers: shared hit=16
+(4 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
QUERY PLAN
@@ -600,9 +604,9 @@
--------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on rlp4_1 rlp_1 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -1933,10 +1937,10 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp (actual rows=0 loops=1)
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
Rows Removed by Filter: 2
Buffers: shared hit=1
(4 rows)
@@ -2269,11 +2273,11 @@
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
(16 rows)
-- Test pruning during parallel nested loop query
Attachments:
[text/plain] regression.diffs (13.1K, ../[email protected]/3-regression.diffs)
download | inline:
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/create_index.out /home/alena/postgrespro7/src/test/regress/results/create_index.out
--- /home/alena/postgrespro7/src/test/regress/expected/create_index.out 2023-06-27 10:20:52.287769054 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/create_index.out 2023-06-27 10:32:20.701220051 +0300
@@ -1838,26 +1838,14 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1 (actual rows=1 loops=1)
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- Heap Blocks: exact=1
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1 (actual rows=1 loops=1)
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
Buffers: shared hit=5 read=2
- -> BitmapOr (actual rows=0 loops=1)
- Buffers: shared hit=4 read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- Buffers: shared read=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=0 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=1 loops=1)
- Index Cond: ((thousand = 42) AND (tenthous = 42))
- Buffers: shared hit=2
Planning:
Buffers: shared hit=51
-(17 rows)
+(5 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1869,12 +1857,12 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=5 read=3
-> Bitmap Heap Scan on tenk1 (actual rows=10 loops=1)
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
Heap Blocks: exact=10
Buffers: shared hit=5 read=3
-> BitmapAnd (actual rows=0 loops=1)
@@ -1882,15 +1870,10 @@
-> Bitmap Index Scan on tenk1_hundred (actual rows=100 loops=1)
Index Cond: (hundred = 42)
Buffers: shared read=2
- -> BitmapOr (actual rows=0 loops=1)
+ -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=20 loops=1)
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
Buffers: shared hit=3 read=1
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 42)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_thous_tenthous (actual rows=10 loops=1)
- Index Cond: (thousand = 99)
- Buffers: shared hit=1 read=1
-(19 rows)
+(14 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/join.out /home/alena/postgrespro7/src/test/regress/results/join.out
--- /home/alena/postgrespro7/src/test/regress/expected/join.out 2023-06-27 10:21:22.096306419 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/join.out 2023-06-27 10:32:26.049288441 +0300
@@ -4207,10 +4207,10 @@
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Nested Loop (actual rows=201 loops=1)
- Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
Rows Removed by Join Filter: 102
Buffers: shared hit=98
-> Bitmap Heap Scan on tenk1 b (actual rows=101 loops=1)
@@ -4228,7 +4228,7 @@
-> Materialize (actual rows=3 loops=101)
Buffers: shared hit=8
-> Bitmap Heap Scan on tenk1 a (actual rows=3 loops=1)
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
Heap Blocks: exact=2
Buffers: shared hit=8
-> BitmapOr (actual rows=0 loops=1)
@@ -4236,13 +4236,10 @@
-> Bitmap Index Scan on tenk1_unique1 (actual rows=1 loops=1)
Index Cond: (unique1 = 1)
Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 3)
- Buffers: shared hit=2
- -> Bitmap Index Scan on tenk1_unique2 (actual rows=1 loops=1)
- Index Cond: (unique2 = 7)
- Buffers: shared hit=2
-(33 rows)
+ -> Bitmap Index Scan on tenk1_unique2 (actual rows=2 loops=1)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ Buffers: shared hit=4
+(30 rows)
--
-- test placement of movable quals in a parameterized join tree
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out /home/alena/postgrespro7/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out 2023-06-27 00:35:33.761010034 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/stats_ext.out 2023-06-27 10:32:37.537436241 +0300
@@ -1322,19 +1322,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out /home/alena/postgrespro7/src/test/regress/results/partition_prune.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out 2023-06-27 10:31:39.596703531 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_prune.out 2023-06-27 10:32:48.285575599 +0300
@@ -82,23 +82,25 @@
(2 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-(5 rows)
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ Planning:
+ Buffers: shared hit=16
+(7 rows)
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on lp_ad lp_1 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2 (actual rows=0 loops=1)
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
@@ -518,8 +520,10 @@
QUERY PLAN
----------------------------------------------
Seq Scan on rlp2 rlp (actual rows=0 loops=1)
- Filter: ((a = 1) OR (a = 7))
-(2 rows)
+ Filter: (a = ANY ('{1,7}'::integer[]))
+ Planning:
+ Buffers: shared hit=16
+(4 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
QUERY PLAN
@@ -600,9 +604,9 @@
--------------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on rlp4_1 rlp_1 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2 (actual rows=0 loops=1)
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -1933,10 +1937,10 @@
EXPLAIN (ANALYZE, COSTS OFF, BUFFERS, TIMING OFF, SUMMARY OFF) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp (actual rows=0 loops=1)
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
Rows Removed by Filter: 2
Buffers: shared hit=1
(4 rows)
@@ -2269,11 +2273,11 @@
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
(16 rows)
-- Test pruning during parallel nested loop query
[image/png] graph1.png (109.5K, ../[email protected]/4-graph1.png)
download | view image
view thread (3+ 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]
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