v1-0002-Always-use-two-fractional-digits-for-estimated-rows_TESTS.patch
text/x-patch
Filename: v1-0002-Always-use-two-fractional-digits-for-estimated-rows_TESTS.patch
Type: text/x-patch
Part: 1
Message:
Re: explain analyze rows=%.0f
From a34fd63c47a87e79d6b0fe0a705c527a5b2b22bc Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <ilya.evdokimov@tantorlabs.com>
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
<Alias>i8</Alias> +
<Startup-Cost>N.N</Startup-Cost> +
<Total-Cost>N.N</Total-Cost> +
- <Plan-Rows>N</Plan-Rows> +
+ <Plan-Rows>N.N</Plan-Rows> +
<Plan-Width>N</Plan-Width> +
<Actual-Startup-Time>N.N</Actual-Startup-Time> +
<Actual-Total-Time>N.N</Actual-Total-Time> +
@@ -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