Hi,
Since ddb17e387aa, EXPLAIN reports per-loop actual rows with two decimal digits.
The neighbouring counters - Rows Removed by Filter, by Join Filter - are divided
by nloops too, but are still printed with zero digits. In a parameterised inner
side this makes the two numbers contradict each other, for example:
-> Index Scan using tenk1_unique1 on tenk1 b (actual rows=0.90 loops=10)
Index Cond: (unique1 = g.g)
Filter: (ten <> 0)
Rows Removed by Filter: 0
The scan returned 0.90 rows per loop out of 1.00 fetched, yet the filter claims
it removed nothing. See the attached patch for complete examples.
The patch is a single-line change in show_instrumentation_count() plus two
tests. It is a concept, not a finished patch: I have not regenerated the other
expected outputs, where many values will change from "N" to "N.00" - you can
find a sketch for that in 0002-* file. Before going through that churn, I would
like to hear whether people agree that these counters should follow the same
rule as actual rows. The options I see are:
1. Always print two digits, as actual rows do now.
2. Leave the math to users and print exact totals without dividing by nloops.
The second approach is a much bigger change, with obvious pros and cons.
The first one is simpler and more conservative, and it is what the attached
patch does. In [1], depesz describes how the switch to fractional actual rows
affected his explain parser, and it needed only a small fix. So I expect this
change to be similarly harmless for tools.
Thoughts?
[1]
https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-allow-explain-to-indicate-fractional-rows/
--
regards, Andrei Lepikhov,
pgEdge
From 57d3d8d243dc18fa14f7d88679ba7670dfe5efd9 Mon Sep 17 00:00:00 2001
From: Andrei Lepikhov <[email protected]>
Date: Thu, 5 Nov 2026 19:47:17 +0100
Subject: [PATCH 1/2] Switch JOIN and SCAN filter explain output to floating
point format
---
src/backend/commands/explain.c | 2 +-
src/test/regress/expected/explain.out | 37 +++++++++++++++++++++++++++
src/test/regress/sql/explain.sql | 9 +++++++
3 files changed, 47 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 4b1ad4227ac..ace016637af 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4222,7 +4222,7 @@ show_instrumentation_count(const char *qlabel, int which,
if (nfiltered > 0 || es->format != EXPLAIN_FORMAT_TEXT)
{
if (nloops > 0)
- ExplainPropertyFloat(qlabel, NULL, nfiltered / nloops,
0, es);
+ ExplainPropertyFloat(qlabel, NULL, nfiltered / nloops,
2, es);
else
ExplainPropertyFloat(qlabel, NULL, 0.0, 0, es);
}
diff --git a/src/test/regress/expected/explain.out
b/src/test/regress/expected/explain.out
index 74a4d87801e..914e5fdcbc4 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -832,3 +832,40 @@ select explain_filter('explain (analyze,buffers off,costs
off) select sum(n) ove
(9 rows)
reset work_mem;
+explain (analyze, costs off, timing off, summary off, buffers off)
+select count(*) from generate_series(0, 9) g join tenk1 b on b.unique1 = g
+where b.ten <> 0;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Aggregate (actual rows=1.00 loops=1)
+ -> Nested Loop (actual rows=9.00 loops=1)
+ -> Function Scan on generate_series g (actual rows=10.00 loops=1)
+ -> Index Scan using tenk1_unique1 on tenk1 b (actual rows=0.90
loops=10)
+ Index Cond: (unique1 = g.g)
+ Filter: (ten <> 0)
+ Rows Removed by Filter: 0.10
+ Index Searches: 10
+(8 rows)
+
+-- Join Filter of a join that is itself rescanned
+explain (analyze, costs off, timing off, summary off, buffers off)
+select count(*) from generate_series(0, 9) g,
+ lateral (select 1 from tenk1 b join tenk1 c
+ on c.unique2 = b.unique1 and b.ten <> c.ten
+ where b.unique1 = g offset 0) s;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate (actual rows=1.00 loops=1)
+ -> Nested Loop (actual rows=7.00 loops=1)
+ -> Function Scan on generate_series g (actual rows=10.00 loops=1)
+ -> Nested Loop (actual rows=0.70 loops=10)
+ Join Filter: (b.ten <> c.ten)
+ Rows Removed by Join Filter: 0.30
+ -> Index Scan using tenk1_unique1 on tenk1 b (actual rows=1.00
loops=10)
+ Index Cond: (unique1 = g.g)
+ Index Searches: 10
+ -> Index Scan using tenk1_unique2 on tenk1 c (actual rows=1.00
loops=10)
+ Index Cond: (unique2 = g.g)
+ Index Searches: 10
+(12 rows)
+
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 2f163c64bf6..213ab2cbcc7 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -191,3 +191,12 @@ select explain_filter('explain (analyze,buffers off,costs
off) select sum(n) ove
-- Test tuplestore storage usage in Window aggregate (memory and disk case,
final result is disk)
select explain_filter('explain (analyze,buffers off,costs off) select sum(n)
over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2500)
a(n))');
reset work_mem;
+
+explain (analyze, costs off, timing off, summary off, buffers off)
+select count(*) from generate_series(0, 9) g join tenk1 b on b.unique1 = g
+where b.ten <> 0;
+explain (analyze, costs off, timing off, summary off, buffers off)
+select count(*) from generate_series(0, 9) g,
+ lateral (select 1 from tenk1 b join tenk1 c
+ on c.unique2 = b.unique1 and b.ten <> c.ten
+ where b.unique1 = g offset 0) s;
--
2.56.0
From e97fcce76070b7ded53681adefa307f3ee5866c4 Mon Sep 17 00:00:00 2001
From: Andrei Lepikhov <[email protected]>
Date: Fri, 6 Nov 2026 08:44:45 +0000
Subject: [PATCH 2/2] Adjust regression tests to fractional "Rows Removed by"
counters
---
.../postgres_fdw/expected/postgres_fdw.out | 6 ++---
src/test/regress/expected/explain.out | 4 ++++
src/test/regress/expected/gin.out | 20 ++++++++---------
src/test/regress/expected/memoize.out | 18 +++++++--------
src/test/regress/expected/merge.out | 2 +-
src/test/regress/expected/partition_prune.out | 22 +++++++++----------
src/test/regress/expected/select_parallel.out | 4 ++--
src/test/regress/sql/explain.sql | 5 +++++
src/test/regress/sql/partition_prune.sql | 2 +-
9 files changed, 46 insertions(+), 37 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index b946a6416a0..cb2801978f9 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -12836,7 +12836,7 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a =
async_pt.a AND local_tbl.c
Nested Loop (actual rows=1.00 loops=1)
-> Seq Scan on local_tbl (actual rows=1.00 loops=1)
Filter: (c = 'bar'::text)
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Append (actual rows=1.00 loops=1)
-> Async Foreign Scan on async_p1 async_pt_1 (never executed)
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1.00
loops=1)
@@ -13081,7 +13081,7 @@ SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT
count(*) FROM async_pt W
--------------------------------------------------------------------------------------------
Nested Loop Left Join (actual rows=1.00 loops=1)
Join Filter: (t1.a = async_pt.a)
- Rows Removed by Join Filter: 399
+ Rows Removed by Join Filter: 399.00
InitPlan expr_1
-> Aggregate (actual rows=1.00 loops=1)
-> Append (actual rows=400.00 loops=1)
@@ -13131,7 +13131,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
Filter: (b === 505)
-> Seq Scan on async_p3 t1_3 (actual rows=1.00 loops=1)
Filter: (b === 505)
- Rows Removed by Filter: 101
+ Rows Removed by Filter: 101.00
(9 rows)
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
diff --git a/src/test/regress/expected/gin.out
b/src/test/regress/expected/gin.out
index 118bc26d8cd..43bf8cc78f8 100644
--- a/src/test/regress/expected/gin.out
+++ b/src/test/regress/expected/gin.out
@@ -187,16 +187,16 @@ from
lateral execute_text_query_heap($$select string_agg((i, j)::text, ' ') from
t_gin_test_tbl where $$ || query) res_heap;
query | return by index | removed by
recheck | match
-------------------------------------------+-----------------+--------------------+-------
- i @> '{}' | 7.00 | 0
| t
- j @> '{}' | 6.00 | 0
| t
- i @> '{}' and j @> '{}' | 4.00 | 0
| t
- i @> '{1}' | 5.00 | 0
| t
- i @> '{1}' and j @> '{}' | 3.00 | 0
| t
- i @> '{1}' and i @> '{}' and j @> '{}' | 3.00 | 0
| t
- j @> '{10}' | 4.00 | 0
| t
- j @> '{10}' and i @> '{}' | 3.00 | 0
| t
- j @> '{10}' and j @> '{}' and i @> '{}' | 3.00 | 0
| t
- i @> '{1}' and j @> '{10}' | 2.00 | 0
| t
+ i @> '{}' | 7.00 | 0.00
| t
+ j @> '{}' | 6.00 | 0.00
| t
+ i @> '{}' and j @> '{}' | 4.00 | 0.00
| t
+ i @> '{1}' | 5.00 | 0.00
| t
+ i @> '{1}' and j @> '{}' | 3.00 | 0.00
| t
+ i @> '{1}' and i @> '{}' and j @> '{}' | 3.00 | 0.00
| t
+ j @> '{10}' | 4.00 | 0.00
| t
+ j @> '{10}' and i @> '{}' | 3.00 | 0.00
| t
+ j @> '{10}' and j @> '{}' and i @> '{}' | 3.00 | 0.00
| t
+ i @> '{1}' and j @> '{10}' | 2.00 | 0.00
| t
(10 rows)
reset enable_seqscan;
diff --git a/src/test/regress/expected/memoize.out
b/src/test/regress/expected/memoize.out
index 30bf5f5667e..7d50ce553e4 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -43,7 +43,7 @@ WHERE t2.unique1 < 1000;', false);
-> Nested Loop (actual rows=1000.00 loops=N)
-> Seq Scan on tenk1 t2 (actual rows=1000.00 loops=N)
Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
+ Rows Removed by Filter: 9000.00
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t2.twenty
Cache Mode: logical
@@ -75,7 +75,7 @@ WHERE t1.unique1 < 1000;', false);
-> Nested Loop (actual rows=1000.00 loops=N)
-> Seq Scan on tenk1 t1 (actual rows=1000.00 loops=N)
Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
+ Rows Removed by Filter: 9000.00
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t1.twenty
Cache Mode: binary
@@ -117,7 +117,7 @@ WHERE t1.unique1 < 10;', false);
Hits: 8 Misses: 2 Evictions: Zero Overflows: 0 Memory
Usage: NkB
-> Subquery Scan on t2 (actual rows=2.00 loops=N)
Filter: (t1.two = t2.two)
- Rows Removed by Filter: 2
+ Rows Removed by Filter: 2.00
-> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual
rows=4.00 loops=N)
Index Cond: (unique1 < 4)
Index Searches: N
@@ -146,14 +146,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
-> Nested Loop (actual rows=1000.00 loops=N)
-> Seq Scan on tenk1 t1 (actual rows=1000.00 loops=N)
Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
+ Rows Removed by Filter: 9000.00
-> Memoize (actual rows=1.00 loops=N)
Cache Key: (t1.two + 1)
Cache Mode: binary
Hits: 998 Misses: 2 Evictions: Zero Overflows: 0 Memory
Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual
rows=1.00 loops=N)
Filter: ((t1.two + 1) = unique1)
- Rows Removed by Filter: 9999
+ Rows Removed by Filter: 9999.00
Heap Fetches: N
Index Searches: N
(14 rows)
@@ -179,14 +179,14 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
-> Nested Loop (actual rows=1000.00 loops=N)
-> Seq Scan on tenk1 t1 (actual rows=1000.00 loops=N)
Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
+ Rows Removed by Filter: 9000.00
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t1.two, t1.twenty
Cache Mode: binary
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory
Usage: NkB
-> Seq Scan on tenk1 t2 (actual rows=1.00 loops=N)
Filter: ((t1.twenty = unique1) AND (t1.two = two))
- Rows Removed by Filter: 9999
+ Rows Removed by Filter: 9999.00
(12 rows)
-- And check we get the expected results.
@@ -246,7 +246,7 @@ WHERE t2.unique1 < 1200;', true);
-> Nested Loop (actual rows=1200.00 loops=N)
-> Seq Scan on tenk1 t2 (actual rows=1200.00 loops=N)
Filter: (unique1 < 1200)
- Rows Removed by Filter: 8800
+ Rows Removed by Filter: 8800.00
-> Memoize (actual rows=1.00 loops=N)
Cache Key: t2.thousand
Cache Mode: logical
@@ -698,7 +698,7 @@ WHERE t2.a IS NULL;', false);
Hits: 97 Misses: 3 Evictions: Zero Overflows: 0 Memory
Usage: NkB
-> Subquery Scan on t2 (actual rows=0.67 loops=N)
Filter: ((t1.a + 1) = t2.a)
- Rows Removed by Filter: 2
+ Rows Removed by Filter: 2.00
-> Unique (actual rows=2.67 loops=N)
-> Sort (actual rows=67.33 loops=N)
Sort Key: t2_1.a
diff --git a/src/test/regress/expected/merge.out
b/src/test/regress/expected/merge.out
index 9cb1d87066a..000c2b19e34 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1801,7 +1801,7 @@ WHEN MATCHED AND t.a < 10 THEN
Sort Method: quicksort Memory: xxx
-> Seq Scan on ex_mtarget t (actual rows=0.00 loops=1)
Filter: (a < '-1000'::integer)
- Rows Removed by Filter: 54
+ Rows Removed by Filter: 54.00
-> Sort (never executed)
Sort Key: s.a
-> Seq Scan on ex_msource s (never executed)
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 3506ecec477..caa046b36f8 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2535,16 +2535,16 @@ explain (analyze, costs off, summary off, timing off,
buffers off) select * from
Append (actual rows=0.00 loops=1)
-> Seq Scan on list_part1 list_part_1 (actual rows=0.00 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Seq Scan on list_part2 list_part_2 (actual rows=0.00 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Seq Scan on list_part3 list_part_3 (actual rows=0.00 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Seq Scan on list_part4 list_part_4 (actual rows=0.00 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
(13 rows)
rollback;
@@ -2570,7 +2570,7 @@ begin
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched:
N');
ln := regexp_replace(ln, 'actual rows=\d+(?:\.\d+)? loops=\d+',
'actual rows=N loops=N');
- ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed
by Filter: N');
+ ln := regexp_replace(ln, 'Rows Removed by Filter: \d+(?:\.\d+)?',
'Rows Removed by Filter: N');
perform regexp_matches(ln, 'Index Searches: \d+');
if found then
continue;
@@ -3068,7 +3068,7 @@ explain (analyze, costs off, summary off, timing off,
buffers off) execute ab_q6
Filter: ((a = $1) AND (b = (InitPlan expr_1).col1))
-> Seq Scan on xy_1 (actual rows=0.00 loops=1)
Filter: ((x = $1) AND (y = (InitPlan expr_1).col1))
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Seq Scan on ab_a1_b1 ab_4 (never executed)
Filter: ((a = $1) AND (b = (InitPlan expr_1).col1))
-> Seq Scan on ab_a1_b2 ab_5 (never executed)
@@ -3732,7 +3732,7 @@ select * from boolp where a = (select value from
boolvalues where value);
InitPlan expr_1
-> Seq Scan on boolvalues (actual rows=1.00 loops=1)
Filter: value
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Seq Scan on boolp_f boolp_1 (never executed)
Filter: (a = (InitPlan expr_1).col1)
-> Seq Scan on boolp_t boolp_2 (actual rows=0.00 loops=1)
@@ -3747,7 +3747,7 @@ select * from boolp where a = (select value from
boolvalues where not value);
InitPlan expr_1
-> Seq Scan on boolvalues (actual rows=1.00 loops=1)
Filter: (NOT value)
- Rows Removed by Filter: 1
+ Rows Removed by Filter: 1.00
-> Seq Scan on boolp_f boolp_1 (actual rows=0.00 loops=1)
Filter: (a = (InitPlan expr_1).col1)
-> Seq Scan on boolp_t boolp_2 (never executed)
@@ -3776,11 +3776,11 @@ explain (analyze, costs off, summary off, timing off,
buffers off) execute mt_q1
Subplans Removed: 1
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual
rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
- Rows Removed by Filter: 9
+ Rows Removed by Filter: 9.00
Index Searches: 1
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual
rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
- Rows Removed by Filter: 9
+ Rows Removed by Filter: 9.00
Index Searches: 1
(11 rows)
@@ -3799,7 +3799,7 @@ explain (analyze, costs off, summary off, timing off,
buffers off) execute mt_q1
Subplans Removed: 2
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual
rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
- Rows Removed by Filter: 9
+ Rows Removed by Filter: 9.00
Index Searches: 1
(7 rows)
diff --git a/src/test/regress/expected/select_parallel.out
b/src/test/regress/expected/select_parallel.out
index e1344215644..a71f453a5a3 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -589,13 +589,13 @@ explain (analyze, timing off, summary off, costs off,
buffers off)
-> Nested Loop (actual rows=98000.00 loops=1)
-> Seq Scan on tenk2 (actual rows=10.00 loops=1)
Filter: (thousand = 0)
- Rows Removed by Filter: 9990
+ Rows Removed by Filter: 9990.00
-> Gather (actual rows=9800.00 loops=10)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on tenk1 (actual rows=1960.00 loops=50)
Filter: (hundred > 1)
- Rows Removed by Filter: 40
+ Rows Removed by Filter: 40.00
(11 rows)
alter table tenk2 reset (parallel_workers);
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index d6e096dc565..b642690061f 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -680,7 +680,7 @@ begin
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched:
N');
ln := regexp_replace(ln, 'actual rows=\d+(?:\.\d+)? loops=\d+',
'actual rows=N loops=N');
- ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed
by Filter: N');
+ ln := regexp_replace(ln, 'Rows Removed by Filter: \d+(?:\.\d+)?',
'Rows Removed by Filter: N');
perform regexp_matches(ln, 'Index Searches: \d+');
if found then
continue;
--
2.56.0
