From ff9f72c68f678ded340b431c3e280fe56644a3e7 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Wed, 10 Apr 2024 02:07:34 +0300 Subject: revert: Transform OR clauses to ANY expression This commit reverts 72bd38cc99 due to implementation and design issues. Reported-by: Tom Lane Discussion: https://postgr.es/m/3604469.1712628736%40sss.pgh.pa.us --- src/test/regress/expected/create_index.out | 159 -------------------------- src/test/regress/expected/join.out | 50 -------- src/test/regress/expected/partition_prune.out | 37 +++--- src/test/regress/sql/create_index.sql | 45 -------- src/test/regress/sql/join.sql | 11 -- src/test/regress/sql/partition_prune.sql | 2 - 6 files changed, 18 insertions(+), 286 deletions(-) (limited to 'src/test') diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index b4b42173e51..cf6eac57349 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1889,165 +1889,6 @@ SELECT count(*) FROM tenk1 10 (1 row) -SET or_to_any_transform_limit = 0; -EXPLAIN (COSTS OFF) -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); - QUERY PLAN ------------------------------------------------------------------------------- - Index Scan using tenk1_thous_tenthous on tenk1 - Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[]))) -(2 rows) - -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); - unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ----------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- - 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx -(1 row) - -SET or_to_any_transform_limit = 3; -EXPLAIN (COSTS OFF) -- or_transformation still works -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); - QUERY PLAN ------------------------------------------------------------------------------- - Index Scan using tenk1_thous_tenthous on tenk1 - Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[]))) -(2 rows) - -SET or_to_any_transform_limit = 4; -EXPLAIN (COSTS OFF) -- or_transformation must be disabled -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ - Bitmap Heap Scan on tenk1 - Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42))) - -> BitmapOr - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand = 42) AND (tenthous = 1)) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand = 42) AND (tenthous = 3)) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand = 42) AND (tenthous = 42)) -(9 rows) - -RESET or_to_any_transform_limit; -SET or_to_any_transform_limit = 0; -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); - QUERY PLAN ------------------------------------------------------------------------------------- - Aggregate - -> Bitmap Heap Scan on tenk1 - Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[]))) - -> BitmapAnd - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 42) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = ANY ('{42,99}'::integer[])) -(8 rows) - -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); - count -------- - 10 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand); - QUERY PLAN ------------------------------------------------------------------------------------------- - Aggregate - -> Bitmap Heap Scan on tenk1 - Recheck Cond: ((hundred = 42) AND (thousand < ANY ('{42,99,43,42}'::integer[]))) - -> BitmapAnd - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 42) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand < ANY ('{42,99,43,42}'::integer[])) -(8 rows) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41; - QUERY PLAN --------------------------------------------------------------------------------------------------------- - Aggregate - -> Bitmap Heap Scan on tenk1 - Recheck Cond: (((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[]))) OR (thousand = 41)) - -> BitmapOr - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[]))) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = 41) -(8 rows) - -SELECT count(*) FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41; - count -------- - 10 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ - Aggregate - -> Bitmap Heap Scan on tenk1 - Recheck Cond: (((hundred = 42) AND ((thousand = ANY ('{42,99}'::integer[])) OR (tenthous < 2))) OR (thousand = 41)) - -> BitmapOr - -> BitmapAnd - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 42) - -> BitmapOr - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = ANY ('{42,99}'::integer[])) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (tenthous < 2) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = 41) -(14 rows) - -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; - count -------- - 20 -(1 row) - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- - Aggregate - -> Bitmap Heap Scan on tenk1 - Recheck Cond: ((hundred = 42) AND ((thousand = ANY ('{42,41}'::integer[])) OR ((thousand = 99) AND (tenthous = 2)))) - -> BitmapAnd - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 42) - -> BitmapOr - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: (thousand = ANY ('{42,41}'::integer[])) - -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand = 99) AND (tenthous = 2)) -(11 rows) - -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); - count -------- - 10 -(1 row) - -RESET or_to_any_transform_limit; -- -- Check behavior with duplicate index column contents -- diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 407d251a508..8b640c2fc2f 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4233,56 +4233,6 @@ select * from tenk1 a join tenk1 b on Index Cond: (unique2 = 7) (19 rows) -SET or_to_any_transform_limit = 0; -explain (costs off) -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 ------------------------------------------------------------------------------------------------------------------------- - Nested Loop - Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) - -> Bitmap Heap Scan on tenk1 b - Recheck Cond: ((unique1 = 2) OR (hundred = 4)) - -> BitmapOr - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 = 2) - -> Bitmap Index Scan on tenk1_hundred - Index Cond: (hundred = 4) - -> Materialize - -> Bitmap Heap Scan on tenk1 a - Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[]))) - -> BitmapOr - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 = 1) - -> Bitmap Index Scan on tenk1_unique2 - Index Cond: (unique2 = ANY ('{3,7}'::integer[])) -(17 rows) - -explain (costs off) -select * from tenk1 a join tenk1 b on - (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or - ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- - Nested Loop - Join Filter: ((a.unique1 < 20) OR (a.unique1 = 3) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4))) - -> Seq Scan on tenk1 b - -> Materialize - -> Bitmap Heap Scan on tenk1 a - Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR (unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[]))) - -> BitmapOr - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 < 20) - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 = 3) - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 = 1) - -> Bitmap Index Scan on tenk1_unique2 - Index Cond: (unique2 = ANY ('{3,7}'::integer[])) -(15 rows) - -RESET or_to_any_transform_limit; -- -- test placement of movable quals in a parameterized join tree -- diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index db507eff448..46b78ba3c41 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -3,7 +3,6 @@ -- -- Force generic plans to be used for all prepared statements in this file. set plan_cache_mode = force_generic_plan; -set or_to_any_transform_limit = 0; create table lp (a char) partition by list (a); create table lp_default partition of lp default; create table lp_ef partition of lp for values in ('e', 'f'); @@ -83,23 +82,23 @@ explain (costs off) select * from lp where a is null; (2 rows) explain (costs off) select * from lp where a = 'a' or a = 'c'; - QUERY PLAN ------------------------------------------------ + QUERY PLAN +---------------------------------------------------------- Append -> Seq Scan on lp_ad lp_1 - Filter: (a = ANY ('{a,c}'::bpchar[])) + Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) -> Seq Scan on lp_bc lp_2 - Filter: (a = ANY ('{a,c}'::bpchar[])) + Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) (5 rows) explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- Append -> Seq Scan on lp_ad lp_1 - Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[]))) + Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) -> Seq Scan on lp_bc lp_2 - Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[]))) + Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) (5 rows) explain (costs off) select * from lp where a <> 'g'; @@ -516,10 +515,10 @@ explain (costs off) select * from rlp where a <= 31; (27 rows) explain (costs off) select * from rlp where a = 1 or a = 7; - QUERY PLAN ------------------------------------------- + QUERY PLAN +-------------------------------- Seq Scan on rlp2 rlp - Filter: (a = ANY ('{1,7}'::integer[])) + Filter: ((a = 1) OR (a = 7)) (2 rows) explain (costs off) select * from rlp where a = 1 or b = 'ab'; @@ -597,13 +596,13 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25); -- where clause contradicts sub-partition's constraint explain (costs off) select * from rlp where a = 20 or a = 40; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +---------------------------------------- Append -> Seq Scan on rlp4_1 rlp_1 - Filter: (a = ANY ('{20,40}'::integer[])) + Filter: ((a = 20) OR (a = 40)) -> Seq Scan on rlp5_default rlp_2 - Filter: (a = ANY ('{20,40}'::integer[])) + Filter: ((a = 20) OR (a = 40)) (5 rows) explain (costs off) select * from rlp3 where a = 20; /* empty */ @@ -2073,10 +2072,10 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde'; explain (costs 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 - Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text)) + Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3))) (2 rows) drop table hp2; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 7059b4ea86b..e296891cab8 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -738,51 +738,6 @@ SELECT count(*) FROM tenk1 SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); -SET or_to_any_transform_limit = 0; -EXPLAIN (COSTS OFF) -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); -SET or_to_any_transform_limit = 3; -EXPLAIN (COSTS OFF) -- or_transformation still works -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); -SET or_to_any_transform_limit = 4; -EXPLAIN (COSTS OFF) -- or_transformation must be disabled -SELECT * FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); -RESET or_to_any_transform_limit; - -SET or_to_any_transform_limit = 0; -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand); - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41; -SELECT count(*) FROM tenk1 - WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41; - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41; - -EXPLAIN (COSTS OFF) -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); -SELECT count(*) FROM tenk1 - WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); -RESET or_to_any_transform_limit; - -- -- Check behavior with duplicate index column contents -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 266461fb5b7..c4c6c7b8ba2 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1409,17 +1409,6 @@ 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); -SET or_to_any_transform_limit = 0; -explain (costs off) -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); -explain (costs off) -select * from tenk1 a join tenk1 b on - (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or - ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); -RESET or_to_any_transform_limit; - -- -- test placement of movable quals in a parameterized join tree -- diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index c49153f38ad..dc716938610 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -4,7 +4,6 @@ -- Force generic plans to be used for all prepared statements in this file. set plan_cache_mode = force_generic_plan; -set or_to_any_transform_limit = 0; create table lp (a char) partition by list (a); create table lp_default partition of lp default; @@ -22,7 +21,6 @@ explain (costs off) select * from lp where a is not null; explain (costs off) select * from lp where a is null; explain (costs off) select * from lp where a = 'a' or a = 'c'; explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'); - explain (costs off) select * from lp where a <> 'g'; explain (costs off) select * from lp where a <> 'a' and a <> 'd'; explain (costs off) select * from lp where a not in ('a', 'd'); -- cgit v1.2.3