From a34fd63c47a87e79d6b0fe0a705c527a5b2b22bc Mon Sep 17 00:00:00 2001 From: Evdokimov Ilia Date: Wed, 9 Apr 2025 22:26:16 +0300 Subject: [PATCH v1] Always use two fractional digits for estimated rows count --- .../expected/pg_overexplain.out | 12 +- .../postgres_fdw/expected/postgres_fdw.out | 15 +- src/test/regress/expected/explain.out | 146 +++++++++--------- src/test/regress/expected/inherit.out | 8 +- src/test/regress/expected/join.out | 134 ++++++++-------- src/test/regress/expected/join_hash.out | 19 +-- src/test/regress/expected/misc_functions.out | 84 +++++----- src/test/regress/expected/partition_join.out | 112 +++++++------- src/test/regress/expected/select_views.out | 19 ++- src/test/regress/expected/stats_ext.out | 12 +- src/test/regress/sql/stats_ext.sql | 4 +- 11 files changed, 277 insertions(+), 288 deletions(-) diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out index 28252dbff6c..731a0bf79a2 100644 --- a/contrib/pg_overexplain/expected/pg_overexplain.out +++ b/contrib/pg_overexplain/expected/pg_overexplain.out @@ -25,9 +25,9 @@ ERROR: unrecognized EXPLAIN option "debuff" LINE 1: EXPLAIN (DEBUFF) SELECT 1; ^ EXPLAIN (DEBUG) SELECT 1; - QUERY PLAN ------------------------------------------- - Result (cost=0.00..0.01 rows=1 width=4) + QUERY PLAN +--------------------------------------------- + Result (cost=0.00..0.01 rows=1.00 width=4) Disabled Nodes: 0 Parallel Safe: false Plan Node ID: 0 @@ -41,9 +41,9 @@ EXPLAIN (DEBUG) SELECT 1; (11 rows) EXPLAIN (RANGE_TABLE) SELECT 1; - QUERY PLAN ------------------------------------------- - Result (cost=0.00..0.01 rows=1 width=4) + QUERY PLAN +--------------------------------------------- + Result (cost=0.00..0.01 rows=1.00 width=4) RTI 1 (result): Eref: "*RESULT*" () (3 rows) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index d1acee5a5fa..e5b92d2017b 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10151,13 +10151,16 @@ SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER J -- left outer join + nullable clause EXPLAIN (VERBOSE, COSTS OFF) SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - Foreign Scan + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort Output: t1.a, fprt2.b, fprt2.c - Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2) - Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10)) ORDER BY r5.a ASC NULLS LAST, r6.b ASC NULLS LAST, r6.c ASC NULLS LAST -(4 rows) + Sort Key: t1.a, fprt2.b, fprt2.c + -> Foreign Scan + Output: t1.a, fprt2.b, fprt2.c + Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2) + Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10)) +(7 rows) SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3; a | b | c diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index c53bf9c8aa3..28991ccb665 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -55,32 +55,32 @@ set jit = off; set track_io_timing = off; -- Simple cases select explain_filter('explain select * from int8_tbl i8'); - explain_filter ---------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) + explain_filter +----------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (1 row) select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); - explain_filter -------------------------------------------------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +--------------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Execution Time: N.N ms (3 rows) select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); - explain_filter --------------------------------------------------------------------------------------------------------- - Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +---------------------------------------------------------------------------------------------------------- + Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Output: q1, q2 Planning Time: N.N ms Execution Time: N.N ms (4 rows) select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); - explain_filter -------------------------------------------------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +--------------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Execution Time: N.N ms (3 rows) @@ -98,7 +98,7 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8 i8 + N.N + N.N + - N + + N.N + N + N.N + N.N + @@ -147,7 +147,7 @@ select explain_filter('explain (analyze, serialize, buffers, format yaml) select Alias: "i8" + Startup Cost: N.N + Total Cost: N.N + - Plan Rows: N + + Plan Rows: N.N + Plan Width: N + Actual Startup Time: N.N + Actual Total Time: N.N + @@ -195,9 +195,9 @@ select explain_filter('explain (analyze, serialize, buffers, format yaml) select (1 row) select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); - explain_filter ---------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) + explain_filter +----------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (1 row) select explain_filter('explain (buffers, format json) select * from int8_tbl i8'); @@ -213,7 +213,7 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8' "Alias": "i8", + "Startup Cost": N.N, + "Total Cost": N.N, + - "Plan Rows": N, + + "Plan Rows": N.N, + "Plan Width": N, + "Disabled": false, + "Shared Hit Blocks": N, + @@ -247,35 +247,35 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8' select explain_filter('explain verbose select sum(unique1) over w, sum(unique2) over (w order by hundred), sum(tenthous) over (w order by hundred) from tenk1 window w as (partition by ten)'); explain_filter ------------------------------------------------------------------------------------------------------- - WindowAgg (cost=N.N..N.N rows=N width=N) + WindowAgg (cost=N.N..N.N rows=N.N width=N) Output: sum(unique1) OVER w, (sum(unique2) OVER w1), (sum(tenthous) OVER w1), ten, hundred Window: w AS (PARTITION BY tenk1.ten) - -> WindowAgg (cost=N.N..N.N rows=N width=N) + -> WindowAgg (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w1, sum(tenthous) OVER w1 Window: w1 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred) - -> Sort (cost=N.N..N.N rows=N width=N) + -> Sort (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous Sort Key: tenk1.ten, tenk1.hundred - -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N) + -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous (11 rows) select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) over (w1 order by hundred), sum(tenthous) over (w1 order by hundred rows 10 preceding) from tenk1 window w1 as (partition by ten)'); explain_filter --------------------------------------------------------------------------------------------------------- - WindowAgg (cost=N.N..N.N rows=N width=N) + WindowAgg (cost=N.N..N.N rows=N.N width=N) Output: sum(unique1) OVER w1, (sum(unique2) OVER w2), (sum(tenthous) OVER w3), ten, hundred Window: w1 AS (PARTITION BY tenk1.ten) - -> WindowAgg (cost=N.N..N.N rows=N width=N) + -> WindowAgg (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous, (sum(unique2) OVER w2), sum(tenthous) OVER w3 Window: w3 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred ROWS 'N'::bigint PRECEDING) - -> WindowAgg (cost=N.N..N.N rows=N width=N) + -> WindowAgg (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w2 Window: w2 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred) - -> Sort (cost=N.N..N.N rows=N width=N) + -> Sort (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous Sort Key: tenk1.ten, tenk1.hundred - -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N) + -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N.N width=N) Output: ten, hundred, unique1, unique2, tenthous (14 rows) @@ -295,7 +295,7 @@ select explain_filter('explain (analyze, buffers, format json) select * from int "Alias": "i8", + "Startup Cost": N.N, + "Total Cost": N.N, + - "Plan Rows": N, + + "Plan Rows": N.N, + "Plan Width": N, + "Actual Startup Time": N.N, + "Actual Total Time": N.N, + @@ -368,11 +368,11 @@ select explain_filter_to_json('explain (settings, format json) select * from int rollback; -- GENERIC_PLAN option select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); - explain_filter ---------------------------------------------------------------------------------- - Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N) + explain_filter +----------------------------------------------------------------------------------- + Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N.N width=N) Recheck Cond: (thousand = $N) - -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N) + -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N.N width=N) Index Cond: (thousand = $N) (4 rows) @@ -382,16 +382,16 @@ ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement -- MEMORY option select explain_filter('explain (memory) select * from int8_tbl i8'); - explain_filter ---------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) + explain_filter +----------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) Memory: used=NkB allocated=NkB (2 rows) select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); - explain_filter -------------------------------------------------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +--------------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Memory: used=NkB allocated=NkB Planning Time: N.N ms Execution Time: N.N ms @@ -408,7 +408,7 @@ select explain_filter('explain (memory, summary, format yaml) select * from int8 Alias: "i8" + Startup Cost: N.N + Total Cost: N.N + - Plan Rows: N + + Plan Rows: N.N + Plan Width: N + Disabled: false + Planning: + @@ -430,7 +430,7 @@ select explain_filter('explain (memory, analyze, format json) select * from int8 "Alias": "i8", + "Startup Cost": N.N, + "Total Cost": N.N, + - "Plan Rows": N, + + "Plan Rows": N.N, + "Plan Width": N, + "Actual Startup Time": N.N, + "Actual Total Time": N.N, + @@ -472,9 +472,9 @@ select explain_filter('explain (memory, analyze, format json) select * from int8 prepare int8_query as select * from int8_tbl i8; select explain_filter('explain (memory) execute int8_query'); - explain_filter ---------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) + explain_filter +----------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) Memory: used=NkB allocated=NkB (2 rows) @@ -496,12 +496,12 @@ create table gen_part_2 partition of gen_part for values in (2); -- should scan gen_part_1_1 and gen_part_1_2, but not gen_part_2 select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1'); - explain_filter ---------------------------------------------------------------------------- - Append (cost=N.N..N.N rows=N width=N) - -> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N width=N) + explain_filter +----------------------------------------------------------------------------- + Append (cost=N.N..N.N rows=N.N width=N) + -> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N.N width=N) Filter: ((key1 = N) AND (key2 = $N)) - -> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N width=N) + -> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N.N width=N) Filter: ((key1 = N) AND (key2 = $N)) (5 rows) @@ -561,7 +561,7 @@ select jsonb_pretty( "Schema": "public", + "Disabled": false, + "Node Type": "Seq Scan", + - "Plan Rows": 0, + + "Plan Rows": 0.0, + "Plan Width": 0, + "Total Cost": 0.0, + "Actual Rows": 0.0, + @@ -608,7 +608,7 @@ select jsonb_pretty( "tenk1.tenthous" + ], + "Node Type": "Sort", + - "Plan Rows": 0, + + "Plan Rows": 0.0, + "Plan Width": 0, + "Total Cost": 0.0, + "Actual Rows": 0.0, + @@ -652,7 +652,7 @@ select jsonb_pretty( ], + "Disabled": false, + "Node Type": "Gather Merge", + - "Plan Rows": 0, + + "Plan Rows": 0.0, + "Plan Width": 0, + "Total Cost": 0.0, + "Actual Rows": 0.0, + @@ -703,7 +703,7 @@ as 'begin return sin($1); end'; select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1) < 0.5'); explain_filter ------------------------------------------------------------ - Seq Scan on pg_temp.t1 (cost=N.N..N.N rows=N width=N) + Seq Scan on pg_temp.t1 (cost=N.N..N.N rows=N.N width=N) Output: f1 Filter: (pg_temp.mysin(t1.f1) < 'N.N'::double precision) (3 rows) @@ -711,53 +711,53 @@ select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1 -- Test compute_query_id set compute_query_id = on; select explain_filter('explain (verbose) select * from int8_tbl i8'); - explain_filter ----------------------------------------------------------------- - Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) + explain_filter +------------------------------------------------------------------ + Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) Output: q1, q2 Query Identifier: N (3 rows) -- Test compute_query_id with utility statements containing plannable query select explain_filter('explain (verbose) declare test_cur cursor for select * from int8_tbl'); - explain_filter -------------------------------------------------------------- - Seq Scan on public.int8_tbl (cost=N.N..N.N rows=N width=N) + explain_filter +--------------------------------------------------------------- + Seq Scan on public.int8_tbl (cost=N.N..N.N rows=N.N width=N) Output: q1, q2 Query Identifier: N (3 rows) select explain_filter('explain (verbose) create table test_ctas as select 1'); - explain_filter ----------------------------------------- - Result (cost=N.N..N.N rows=N width=N) + explain_filter +------------------------------------------ + Result (cost=N.N..N.N rows=N.N width=N) Output: N Query Identifier: N (3 rows) -- Test SERIALIZE option select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); - explain_filter -------------------------------------------------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +--------------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Serialization: time=N.N ms output=NkB format=text Execution Time: N.N ms (4 rows) select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8'); - explain_filter ------------------------------------------------------------------------------------ - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual rows=N.N loops=N) + explain_filter +------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual rows=N.N loops=N) Planning Time: N.N ms Serialization: output=NkB format=text Execution Time: N.N ms (4 rows) select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8'); - explain_filter -------------------------------------------------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +--------------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Serialization: time=N.N ms output=NkB format=binary Execution Time: N.N ms @@ -765,9 +765,9 @@ select explain_filter('explain (analyze,serialize binary,buffers,timing) select -- this tests an edge case where we have no data to return select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); - explain_filter -------------------------------------------------------------------------------------------------- - Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + explain_filter +--------------------------------------------------------------------------------------------------- + Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N) Planning Time: N.N ms Serialization: time=N.N ms output=NkB format=text Execution Time: N.N ms diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 2a8bfba768e..a6657496d58 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -695,10 +695,10 @@ select tableoid::regclass::text as relname, parted_tab.* from parted_tab order b -- modifies partition key, but no rows will actually be updated explain update parted_tab set a = 2 where false; - QUERY PLAN --------------------------------------------------------- - Update on parted_tab (cost=0.00..0.00 rows=0 width=0) - -> Result (cost=0.00..0.00 rows=0 width=10) + QUERY PLAN +----------------------------------------------------------- + Update on parted_tab (cost=0.00..0.00 rows=0.00 width=0) + -> Result (cost=0.00..0.00 rows=0.00 width=10) One-Time Filter: false (3 rows) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 14da5708451..25ae9f4e714 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2422,8 +2422,8 @@ from int4_tbl t1, int4_tbl t2 left join int4_tbl t3 on t3.f1 > 0 left join int4_tbl t4 on t3.f1 > 1 where t4.f1 is null; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +------------------------------------------------- Nested Loop -> Nested Loop Left Join Filter: (t4.f1 IS NULL) @@ -2433,10 +2433,9 @@ where t4.f1 is null; Join Filter: (t3.f1 > 1) -> Seq Scan on int4_tbl t3 Filter: (f1 > 0) - -> Materialize - -> Seq Scan on int4_tbl t4 + -> Seq Scan on int4_tbl t4 -> Seq Scan on int4_tbl t1 -(12 rows) +(11 rows) select t1.f1 from int4_tbl t1, int4_tbl t2 @@ -2573,11 +2572,10 @@ where t1.f1 = coalesce(t2.f1, 1); -> Materialize -> Seq Scan on int4_tbl t2 Filter: (f1 > 1) - -> Materialize - -> Seq Scan on int4_tbl t3 + -> Seq Scan on int4_tbl t3 -> Materialize -> Seq Scan on int4_tbl t4 -(14 rows) +(13 rows) explain (costs off) select * from int4_tbl t1 @@ -3740,10 +3738,9 @@ where i41.f1 > 0; -> Seq Scan on int8_tbl i81 -> Materialize -> Seq Scan on int4_tbl i42 - -> Materialize - -> Seq Scan on int4_tbl i43 - Filter: (f1 > 1) -(12 rows) + -> Seq Scan on int4_tbl i43 + Filter: (f1 > 1) +(11 rows) select * from int4_tbl as i41, @@ -4332,6 +4329,13 @@ select * from tenk1 a join tenk1 b on ------------------------------------------------------------------------------------------------- Nested Loop Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.hundred = 4))) + -> Bitmap Heap Scan on tenk1 a + Recheck Cond: ((unique1 = 1) OR (unique2 = 3)) + -> BitmapOr + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 1) + -> Bitmap Index Scan on tenk1_unique2 + Index Cond: (unique2 = 3) -> Bitmap Heap Scan on tenk1 b Recheck Cond: ((unique1 = 2) OR (hundred = 4)) -> BitmapOr @@ -4339,15 +4343,7 @@ select * from tenk1 a join tenk1 b on 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 = 3)) - -> BitmapOr - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 = 1) - -> Bitmap Index Scan on tenk1_unique2 - Index Cond: (unique2 = 3) -(17 rows) +(16 rows) explain (costs off) select * from tenk1 a join tenk1 b on @@ -4356,17 +4352,16 @@ select * from tenk1 a join tenk1 b on --------------------------------------------------------------------------------------------- Nested Loop Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.ten = 4))) + -> Bitmap Heap Scan on tenk1 a + Recheck Cond: ((unique1 = 1) OR (unique2 = 3)) + -> BitmapOr + -> Bitmap Index Scan on tenk1_unique1 + Index Cond: (unique1 = 1) + -> Bitmap Index Scan on tenk1_unique2 + Index Cond: (unique2 = 3) -> Seq Scan on tenk1 b Filter: ((unique1 = 2) OR (ten = 4)) - -> Materialize - -> Bitmap Heap Scan on tenk1 a - Recheck Cond: ((unique1 = 1) OR (unique2 = 3)) - -> BitmapOr - -> Bitmap Index Scan on tenk1_unique1 - Index Cond: (unique1 = 1) - -> Bitmap Index Scan on tenk1_unique2 - Index Cond: (unique2 = 3) -(12 rows) +(11 rows) explain (costs off) select * from tenk1 a join tenk1 b on @@ -5110,13 +5105,12 @@ select 1 from Join Filter: (i4.f1 IS NOT NULL) -> Seq Scan on int4_tbl i4 Filter: (2 < f1) - -> Materialize - -> Seq Scan on int8_tbl i8 + -> Seq Scan on int8_tbl i8 -> Result One-Time Filter: false -> Materialize -> Seq Scan on int4_tbl i42 -(16 rows) +(15 rows) -- -- test for appropriate join order in the presence of lateral references @@ -6157,18 +6151,17 @@ FROM int4_tbl JOIN ((SELECT 42 AS x FROM int8_tbl LEFT JOIN innertab ON q1 = id) AS ss1 RIGHT JOIN tenk1 ON NULL) ON tenk1.unique1 = ss1.x OR tenk1.unique2 = ss1.x; - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- Nested Loop + -> Nested Loop Left Join + Join Filter: NULL::boolean + Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42))) + -> Seq Scan on tenk1 + -> Result + One-Time Filter: false -> Seq Scan on int4_tbl - -> Materialize - -> Nested Loop Left Join - Join Filter: NULL::boolean - Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42))) - -> Seq Scan on tenk1 - -> Result - One-Time Filter: false -(9 rows) +(8 rows) rollback; -- another join removal bug: we must clean up correctly when removing a PHV @@ -6566,28 +6559,26 @@ where t1.a = t2.a; explain (costs off) select * from sj x join sj y on x.a = y.a left join int8_tbl z on x.a = z.q1; - QUERY PLAN ------------------------------------- + QUERY PLAN +--------------------------------- Nested Loop Left Join Join Filter: (y.a = z.q1) -> Seq Scan on sj y Filter: (a IS NOT NULL) - -> Materialize - -> Seq Scan on int8_tbl z -(6 rows) + -> Seq Scan on int8_tbl z +(5 rows) explain (costs off) select * from sj x join sj y on x.a = y.a left join int8_tbl z on y.a = z.q1; - QUERY PLAN ------------------------------------- + QUERY PLAN +--------------------------------- Nested Loop Left Join Join Filter: (y.a = z.q1) -> Seq Scan on sj y Filter: (a IS NOT NULL) - -> Materialize - -> Seq Scan on int8_tbl z -(6 rows) + -> Seq Scan on int8_tbl z +(5 rows) explain (costs off) select * from ( @@ -6614,16 +6605,15 @@ left join (select coalesce(y.q1, 1) from int8_tbl y right join sj j1 inner join sj j2 on j1.a = j2.a on true) z on true; - QUERY PLAN ------------------------------------------- + QUERY PLAN +--------------------------------------- Nested Loop Left Join -> Result -> Nested Loop Left Join -> Seq Scan on sj j2 Filter: (a IS NOT NULL) - -> Materialize - -> Seq Scan on int8_tbl y -(7 rows) + -> Seq Scan on int8_tbl y +(6 rows) -- Test that references to the removed rel in lateral subqueries are replaced -- correctly after join removal @@ -6980,32 +6970,36 @@ select p.b from sj p join sj q on p.a = q.a group by p.b having sum(p.a) = 1; explain (verbose, costs off) select 1 from (select x.* from sj x, sj y where x.a = y.a) q, lateral generate_series(1, q.a) gs(i); - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Nested Loop Output: 1 -> Seq Scan on public.sj y Output: y.a, y.b, y.c Filter: (y.a IS NOT NULL) - -> Function Scan on pg_catalog.generate_series gs - Output: gs.i - Function Call: generate_series(1, y.a) -(8 rows) + -> Memoize + Cache Key: y.a + Cache Mode: binary + -> Function Scan on pg_catalog.generate_series gs + Function Call: generate_series(1, y.a) +(10 rows) explain (verbose, costs off) select 1 from (select y.* from sj x, sj y where x.a = y.a) q, lateral generate_series(1, q.a) gs(i); - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Nested Loop Output: 1 -> Seq Scan on public.sj y Output: y.a, y.b, y.c Filter: (y.a IS NOT NULL) - -> Function Scan on pg_catalog.generate_series gs - Output: gs.i - Function Call: generate_series(1, y.a) -(8 rows) + -> Memoize + Cache Key: y.a + Cache Mode: binary + -> Function Scan on pg_catalog.generate_series gs + Function Call: generate_series(1, y.a) +(10 rows) -- Test that a non-EC-derived join clause is processed correctly. Use an -- outer join so that we can't form an EC. diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out index 4fc34a0e72a..262fa71ed8d 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -508,17 +508,18 @@ set local hash_mem_multiplier = 1.0; set local enable_parallel_hash = on; explain (costs off) select count(*) from simple r join extremely_skewed s using (id); - QUERY PLAN ------------------------------------------------------------------ - Aggregate + QUERY PLAN +----------------------------------------------------------------------- + Finalize Aggregate -> Gather Workers Planned: 1 - -> Parallel Hash Join - Hash Cond: (r.id = s.id) - -> Parallel Seq Scan on simple r - -> Parallel Hash - -> Parallel Seq Scan on extremely_skewed s -(8 rows) + -> Partial Aggregate + -> Parallel Hash Join + Hash Cond: (r.id = s.id) + -> Parallel Seq Scan on simple r + -> Parallel Hash + -> Parallel Seq Scan on extremely_skewed s +(9 rows) select count(*) from simple r join extremely_skewed s using (id); count diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index d3f5d16a672..6289d3e03d1 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -647,27 +647,27 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g; SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day') g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1) (1 row) -- As above but with generate_series_timestamp SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMP '2024-02-01', TIMESTAMP '2024-03-01', INTERVAL '1 day') g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1) (1 row) -- As above but with generate_series_timestamptz_at_zone() SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day', 'UTC') g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1) (1 row) -- Ensure the estimated and actual row counts match when the range isn't @@ -675,36 +675,36 @@ true, true, false, true); SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '7 day') g(s);$$, true, true, false, true); - explain_mask_costs -------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=5 width=N) (actual rows=5.00 loops=1) + explain_mask_costs +---------------------------------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=5.00 width=N) (actual rows=5.00 loops=1) (1 row) -- Ensure the estimates match when step is decreasing SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '-1 day') g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1) (1 row) -- Ensure an empty range estimates 1 row SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '1 day') g(s);$$, true, true, false, true); - explain_mask_costs -------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=1 width=N) (actual rows=0.00 loops=1) + explain_mask_costs +---------------------------------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=1.00 width=N) (actual rows=0.00 loops=1) (1 row) -- Ensure we get the default row estimate for infinity values SELECT explain_mask_costs($$ SELECT * FROM generate_series(TIMESTAMPTZ '-infinity', TIMESTAMPTZ 'infinity', INTERVAL '1 day') g(s);$$, false, true, false, true); - explain_mask_costs -------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=1000 width=N) + explain_mask_costs +---------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=1000.00 width=N) (1 row) -- Ensure the row estimate behaves correctly when step size is zero. @@ -719,36 +719,36 @@ ERROR: step size cannot equal zero SELECT explain_mask_costs($$ SELECT * FROM generate_series(1.0, 25.0) g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=25 width=N) (actual rows=25.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=25.00 width=N) (actual rows=25.00 loops=1) (1 row) -- As above but with non-default step SELECT explain_mask_costs($$ SELECT * FROM generate_series(1.0, 25.0, 2.0) g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=13 width=N) (actual rows=13.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=13.00 width=N) (actual rows=13.00 loops=1) (1 row) -- Ensure the estimates match when step is decreasing SELECT explain_mask_costs($$ SELECT * FROM generate_series(25.0, 1.0, -1.0) g(s);$$, true, true, false, true); - explain_mask_costs ---------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=25 width=N) (actual rows=25.00 loops=1) + explain_mask_costs +------------------------------------------------------------------------------------------------ + Function Scan on generate_series g (cost=N..N rows=25.00 width=N) (actual rows=25.00 loops=1) (1 row) -- Ensure an empty range estimates 1 row SELECT explain_mask_costs($$ SELECT * FROM generate_series(25.0, 1.0, 1.0) g(s);$$, true, true, false, true); - explain_mask_costs -------------------------------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=1 width=N) (actual rows=0.00 loops=1) + explain_mask_costs +---------------------------------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=1.00 width=N) (actual rows=0.00 loops=1) (1 row) -- Ensure we get the default row estimate for error cases (infinity/NaN values @@ -756,25 +756,25 @@ true, true, false, true); SELECT explain_mask_costs($$ SELECT * FROM generate_series('-infinity'::NUMERIC, 'infinity'::NUMERIC, 1.0) g(s);$$, false, true, false, true); - explain_mask_costs -------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=1000 width=N) + explain_mask_costs +---------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=1000.00 width=N) (1 row) SELECT explain_mask_costs($$ SELECT * FROM generate_series(1.0, 25.0, 'NaN'::NUMERIC) g(s);$$, false, true, false, true); - explain_mask_costs -------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=1000 width=N) + explain_mask_costs +---------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=1000.00 width=N) (1 row) SELECT explain_mask_costs($$ SELECT * FROM generate_series(25.0, 2.0, 0.0) g(s);$$, false, true, false, true); - explain_mask_costs -------------------------------------------------------------------- - Function Scan on generate_series g (cost=N..N rows=1000 width=N) + explain_mask_costs +---------------------------------------------------------------------- + Function Scan on generate_series g (cost=N..N rows=1000.00 width=N) (1 row) -- Test functions for control data diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 6101c8c7cf1..fe960db944f 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -28,8 +28,8 @@ ANALYZE prt2; -- inner join EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Sort Sort Key: t1.a -> Append @@ -45,13 +45,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = -> Hash -> Seq Scan on prt1_p2 t1_2 Filter: (b = 0) - -> Hash Join - Hash Cond: (t2_3.b = t1_3.a) - -> Seq Scan on prt2_p3 t2_3 - -> Hash - -> Seq Scan on prt1_p3 t1_3 - Filter: (b = 0) -(21 rows) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + Filter: (b = 0) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) +(20 rows) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; a | c | b | c @@ -834,15 +833,14 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t Index Cond: (((a + b) / 2) = t2_2.b) -> Nested Loop Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2)) - -> Hash Join - Hash Cond: (t2_3.b = t1_3.a) - -> Seq Scan on prt2_p3 t2_3 - -> Hash - -> Seq Scan on prt1_p3 t1_3 - Filter: (b = 0) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + Filter: (b = 0) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3 Index Cond: (((a + b) / 2) = t2_3.b) -(33 rows) +(32 rows) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b; a | c | b | c | ?column? | c @@ -855,8 +853,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Sort Sort Key: t1.a, t2.b, ((t3.a + t3.b)) -> Append @@ -880,17 +878,15 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 -> Hash -> Seq Scan on prt1_p2 t1_2 Filter: (b = 0) - -> Hash Right Join - Hash Cond: (((t3_3.a + t3_3.b) / 2) = t1_3.a) - -> Seq Scan on prt1_e_p3 t3_3 - -> Hash - -> Hash Right Join - Hash Cond: (t2_3.b = t1_3.a) - -> Seq Scan on prt2_p3 t2_3 - -> Hash - -> Seq Scan on prt1_p3 t1_3 - Filter: (b = 0) -(33 rows) + -> Nested Loop Left Join + -> Nested Loop Left Join + -> Seq Scan on prt1_p3 t1_3 + Filter: (b = 0) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) + -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3 + Index Cond: (((a + b) / 2) = t1_3.a) +(31 rows) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; a | c | b | c | ?column? | c @@ -911,8 +907,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Sort Sort Key: t1.a, t2.b, ((t3.a + t3.b)) -> Append @@ -935,15 +931,14 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2 Index Cond: (b = t1_2.a) -> Nested Loop Left Join - -> Hash Right Join - Hash Cond: (t1_3.a = ((t3_3.a + t3_3.b) / 2)) - -> Seq Scan on prt1_p3 t1_3 - -> Hash - -> Seq Scan on prt1_e_p3 t3_3 - Filter: (c = 0) - -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 - Index Cond: (b = t1_3.a) -(30 rows) + -> Seq Scan on prt1_e_p3 t3_3 + Filter: (c = 0) + -> Nested Loop Left Join + -> Index Scan using iprt1_p3_a on prt1_p3 t1_3 + Index Cond: (a = ((t3_3.a + t3_3.b) / 2)) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) +(29 rows) SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; a | c | b | c | ?column? | c @@ -1749,8 +1744,8 @@ ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT; ANALYZE prt2; EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Sort Sort Key: t1.a -> Append @@ -1766,13 +1761,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = -> Hash -> Seq Scan on prt1_p2 t1_2 Filter: (b = 0) - -> Hash Join - Hash Cond: (t2_3.b = t1_3.a) - -> Seq Scan on prt2_p3 t2_3 - -> Hash - -> Seq Scan on prt1_p3 t1_3 - Filter: (b = 0) -(21 rows) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + Filter: (b = 0) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) +(20 rows) -- test default partition behavior for list ALTER TABLE plt1 DETACH PARTITION plt1_p3; @@ -5175,20 +5169,18 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2 Sort Sort Key: t1.a, t1.b -> Append - -> Hash Join - Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c)) + -> Nested Loop + Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c)) -> Seq Scan on alpha_neg_p1 t1_1 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))) - -> Hash - -> Seq Scan on beta_neg_p1 t2_1 - Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))) - -> Hash Join - Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c)) + -> Seq Scan on beta_neg_p1 t2_1 + Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))) + -> Nested Loop + Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c)) -> Seq Scan on alpha_neg_p2 t1_2 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))) - -> Hash - -> Seq Scan on beta_neg_p2 t2_2 - Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))) + -> Seq Scan on beta_neg_p2 t2_2 + Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))) -> Nested Loop Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c)) -> Seq Scan on alpha_pos_p2 t1_3 @@ -5201,7 +5193,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))) -> Seq Scan on beta_pos_p3 t2_4 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))) -(29 rows) +(27 rows) SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b; a | b | c | a | b | c diff --git a/src/test/regress/expected/select_views.out b/src/test/regress/expected/select_views.out index 1aeed8452bd..b3c0cbf0607 100644 --- a/src/test/regress/expected/select_views.out +++ b/src/test/regress/expected/select_views.out @@ -1461,18 +1461,17 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_normal ------------------------------------------------------------------------------ Nested Loop Join Filter: (l.cid = r.cid) + -> Subquery Scan on l + Filter: f_leak(l.cnum) + -> Hash Join + Hash Cond: (r_1.cid = l_1.cid) + -> Seq Scan on credit_card r_1 + -> Hash + -> Seq Scan on customer l_1 + Filter: (name = CURRENT_USER) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) - -> Materialize - -> Subquery Scan on l - Filter: f_leak(l.cnum) - -> Hash Join - Hash Cond: (r_1.cid = l_1.cid) - -> Seq Scan on credit_card r_1 - -> Hash - -> Seq Scan on customer l_1 - Filter: (name = CURRENT_USER) -(13 rows) +(12 rows) SELECT * FROM my_credit_card_usage_secure WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01'; diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 686d8c93aa8..5f17271202a 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -18,8 +18,8 @@ begin loop if first_row then first_row := false; - tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)'); - return query select tmp[1]::int, tmp[2]::int; + tmp := regexp_match(ln, 'rows=(\d+\.\d{2}) .* rows=(\d+\.\d{2})'); + return query select round(tmp[1]::numeric)::int, round(tmp[2]::numeric)::int; end if; end loop; end; @@ -1345,13 +1345,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE 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'')'); @@ -1687,13 +1687,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE 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'')'); diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index b71a6cd089f..f49a7b6a237 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -20,8 +20,8 @@ begin loop if first_row then first_row := false; - tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)'); - return query select tmp[1]::int, tmp[2]::int; + tmp := regexp_match(ln, 'rows=(\d+\.\d{2}) .* rows=(\d+\.\d{2})'); + return query select round(tmp[1]::numeric)::int, round(tmp[2]::numeric)::int; end if; end loop; end; -- 2.34.1