Subject: Re: explain analyze rows=%.0f

From: Ilia Evdokimov <[email-masked]>
Date: Mon, 13 Jan 2025 23:18:18 +0300

On 11.01.2025 14:10, Ilia Evdokimov wrote:
>
>
> On 11.01.2025 12:15, Guillaume Lelarge wrote:
>>
>>
>> Thanks for your patch, this looks like a very interesting feature 
>> that I'd like to see in a future release.
>>
>> It did a quick run: patch OK, make OK, make install OK, but make 
>> check fails quite a lot on partition_prune.sql.
>>
>> I guess it would need some work on partition_prune.sql tests and 
>> perhaps also on the docs.
>>
>> Thanks again.
>>
>>
>> -- 
>> Guillaume.
>
>
> Yes, certainly. I have fixed partition_prune.sql. In the documentation 
> example for EXPLAIN ANALYZE where loops is greater than one, I updated 
> how 'rows' and 'loops' values are displayed so they appear as decimal 
> fractions with two digits after the decimal point.
>
> I attached fixed patch.
>
> Any suggestions?
>
> --
> Best regards,
> Ilia Evdokimov,
> Tantor Labs LLC.
>

I guess, it's not ideal to modify the existing example in the 
documentation of the v5 patch because readers wouldn't immediately 
understand why decimal fractions appear there. Instead, I'll add a brief 
note in the documentation clarifying how rows and loops are displayed 
when the average row count is below one.

The changes the of documentation are attached v6 patch.

If you have any other suggestions or different opinions, I'd be happy to 
discuss them.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From 08f92c7e11829045014598e1dcc042f3e5a1e1a3 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Mon, 13 Jan 2025 23:01:44 +0300
Subject: [PATCH] Clarify display of rows and loops as decimal fractions

When the average number of rows is small compared to the number of loops,
both rows and loops are displayed as decimal fractions with two digits
after the decimal point in EXPLAIN ANALYZE.
---
 doc/src/sgml/perform.sgml                     |  6 ++-
 src/backend/commands/explain.c                | 49 +++++++++++++------
 src/test/regress/expected/partition_prune.out | 10 ++--
 src/test/regress/sql/partition_prune.sql      |  2 +-
 4 files changed, 44 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index a502a2aaba..3f13d17fe9 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -757,7 +757,11 @@ WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
     comparable with the way that the cost estimates are shown.  Multiply by
     the <literal>loops</literal> value to get the total time actually spent in
     the node.  In the above example, we spent a total of 0.030 milliseconds
-    executing the index scans on <literal>tenk2</literal>.
+    executing the index scans on <literal>tenk2</literal>.   If a subplan node
+    is executed multiple times and the average number of rows is less than one,
+    the rows and <literal>loops</literal> values are shown as a decimal fraction
+    (with two digits after the decimal point) to indicate that some rows
+    were actually processed rather than simply rounding down to zero.
    </para>
 
    <para>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c24e66f82e..200294b756 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1981,14 +1981,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 		if (es->format == EXPLAIN_FORMAT_TEXT)
 		{
+			appendStringInfo(es->str, " (actual");
 			if (es->timing)
-				appendStringInfo(es->str,
-								 " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
-								 startup_ms, total_ms, rows, nloops);
+				appendStringInfo(es->str, " time=%.3f..%.3f", startup_ms, total_ms);
+
+			if (nloops > 1 && planstate->instrument->ntuples < nloops)
+				appendStringInfo(es->str," rows=%.2f loops=%.2f)", rows, nloops);
 			else
-				appendStringInfo(es->str,
-								 " (actual rows=%.0f loops=%.0f)",
-								 rows, nloops);
+				appendStringInfo(es->str," rows=%.0f loops=%.0f)", rows, nloops);
 		}
 		else
 		{
@@ -1999,8 +1999,16 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				ExplainPropertyFloat("Actual Total Time", "ms", total_ms,
 									 3, es);
 			}
-			ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
-			ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+			if (nloops > 1 && planstate->instrument->ntuples < nloops)
+			{
+				ExplainPropertyFloat("Actual Rows", NULL, rows, 2, es);
+				ExplainPropertyFloat("Actual Loops", NULL, nloops, 2, es);
+			}
+			else
+			{
+				ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
+				ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+			}
 		}
 	}
 	else if (es->analyze)
@@ -2052,14 +2060,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			if (es->format == EXPLAIN_FORMAT_TEXT)
 			{
 				ExplainIndentText(es);
+				appendStringInfo(es->str, "actual");
 				if (es->timing)
-					appendStringInfo(es->str,
-									 "actual time=%.3f..%.3f rows=%.0f loops=%.0f\n",
-									 startup_ms, total_ms, rows, nloops);
+					appendStringInfo(es->str, " time=%.3f..%.3f", startup_ms, total_ms);
+
+				if (nloops > 1 && planstate->instrument->ntuples < nloops)
+					appendStringInfo(es->str," rows=%.2f loops=%.2f)", rows, nloops);
 				else
-					appendStringInfo(es->str,
-									 "actual rows=%.0f loops=%.0f\n",
-									 rows, nloops);
+					appendStringInfo(es->str," rows=%.0f loops=%.0f)", rows, nloops);
 			}
 			else
 			{
@@ -2070,8 +2078,17 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					ExplainPropertyFloat("Actual Total Time", "ms",
 										 total_ms, 3, es);
 				}
-				ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
-				ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+
+				if (nloops > 1 && planstate->instrument->ntuples < nloops)
+				{
+					ExplainPropertyFloat("Actual Rows", NULL, rows, 2, es);
+					ExplainPropertyFloat("Actual Loops", NULL, nloops, 2, es);
+				}
+				else
+				{
+					ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
+					ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+				}
 			}
 
 			ExplainCloseWorker(n, es);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index c52bc40e81..dc6a3cb54a 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2338,7 +2338,7 @@ begin
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
-        ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
+        ln := regexp_replace(ln, 'actual rows=\d+(?:\.\d+)? loops=\d+(?:\.\d+)?', 'actual rows=N loops=N');
         ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
         return next ln;
     end loop;
@@ -3041,16 +3041,16 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
 
 explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
-                                QUERY PLAN                                
---------------------------------------------------------------------------
+                                   QUERY PLAN                                   
+--------------------------------------------------------------------------------
  Nested Loop (actual rows=3 loops=1)
    ->  Seq Scan on tbl1 (actual rows=5 loops=1)
-   ->  Append (actual rows=1 loops=5)
+   ->  Append (actual rows=0.60 loops=5.00)
          ->  Index Scan using tprt1_idx on tprt_1 (never executed)
                Index Cond: (col1 = tbl1.col1)
          ->  Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
                Index Cond: (col1 = tbl1.col1)
-         ->  Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
+         ->  Index Scan using tprt3_idx on tprt_3 (actual rows=0.33 loops=3.00)
                Index Cond: (col1 = tbl1.col1)
          ->  Index Scan using tprt4_idx on tprt_4 (never executed)
                Index Cond: (col1 = tbl1.col1)
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d67598d5c7..bb1e09a536 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -571,7 +571,7 @@ begin
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
-        ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
+        ln := regexp_replace(ln, 'actual rows=\d+(?:\.\d+)? loops=\d+(?:\.\d+)?', 'actual rows=N loops=N');
         ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
         return next ln;
     end loop;
-- 
2.34.1

---

From: Matheus Alcantara <[email-masked]>
Date: Fri, 7 Feb 2025 16:59:48 -0300

Hi

Em seg., 13 de jan. de 2025 às 17:18, Ilia Evdokimov
<[email-masked]> escreveu:
> I guess, it's not ideal to modify the existing example in the documentation of the v5 patch because readers wouldn't immediately understand why decimal fractions appear there. Instead, I'll add a brief note in the documentation clarifying how rows and loops are displayed when the average row count is below one.
>
v6 is not applying on master, could you please rebase?

git apply v6-0001-Clarify-display-of-rows-and-loops-as-decimal-fraction.patch
error: patch failed: src/test/regress/expected/partition_prune.out:3041
error: src/test/regress/expected/partition_prune.out: patch does not apply

-- 
Matheus Alcantara

---

From: Ilia Evdokimov <[email-masked]>
Date: Fri, 7 Feb 2025 23:41:08 +0300

Hi

Strange, I don't have any problems to apply it on master.

postgres$ git branch
     * master
postgres$ git pull
     Already up to date.
postgres$ git apply 
v6-0001-Clarify-display-of-rows-and-loops-as-decimal-fraction.patch
postgres$

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

---

From: Matheus Alcantara <[email-masked]>
Date: Fri, 7 Feb 2025 18:01:36 -0300

> v6-0001-Clarify-display-of-rows-and-loops-as-decimal-fraction.patch

Just for reference I'm trying to apply based on commit fb056564ec5.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fb056564ec5bc1c18dd670c963c893cdb6de927e

-- 
Matheus Alcantara

---

From: Ilia Evdokimov <[email-masked]>
Date: Sat, 8 Feb 2025 00:28:28 +0300

> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fb056564ec5bc1c18dd670c963c893cdb6de927e

You are right, because two commits were appeared after creating v6-patch 
on partition_prune.out and patch v6 must not have applied on master. 
Then I created v7 patch rebased on fb056564ec5 . Thank for your remark!

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From d94d30618b6f363a54afe5457ef8fe89476e096e Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Sat, 8 Feb 2025 00:15:54 +0300
Subject: [PATCH] Clarify display of rows and loops as decimal fractions

 
    <para>

 			ExplainCloseWorker(n, es);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index e667503c96..b45bb83c0f 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2367,7 +2367,7 @@ begin
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
-        ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
+        ln := regexp_replace(ln, 'actual rows=\d+(?:\.\d+)? loops=\d+(?:\.\d+)?', 'actual rows=N loops=N');
         ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
         return next ln;
     end loop;
@@ -3070,16 +3070,16 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
 
index 730545e86a..ea38ae6990 100644
@@ -586,7 +586,7 @@ begin

---

From: Matheus Alcantara <[email-masked]>
Date: Mon, 10 Feb 2025 12:32:09 -0300

Thanks for the new patch version.

-- v7-0001-Clarify-display-of-rows-and-loops-as-decimal-fraction.patch
> +                    appendStringInfo(es->str," rows=%.2f loops=%.2f)", rows, nloops);
Sorry, but why do the 'loops' need to be changed? IIUC the issue is
only with the
rows field? When testing this patch I got some results similar with this:
    Index Scan using t_a_idx on t  (cost=0.14..0.28 rows=1 width=8)
(actual time=0.049..0.049 rows=0.50 loops=2.00)

> When the total number of returned tuples is less than the number of
> loops currently shows 'rows = 0'. This can mislead users into thinking
> that no rows were returned at all, even though some might have appeared
> occasionally.
>
I think that this can happen when the returned rows and the loops are small
enough to result in a 'row' value like 0.00045? I'm not sure if we have
"bigger" values (e.g 1074(ntuples) / 117(nloops) which would result in 9.17
rows) this would also be true, what do you think? If you could provide
an example of this case would be great!

+    the rows and <literal>loops</literal> values are shown as a
decimal fraction

* I think that it would be good to mention what a 'row' value in
decimal means. For
 example, if its says "0.1 rows" the user should assume that typically 0 rows
 will be returned but sometimes it can return 1 or more.

* There are more spaces than necessary before "If a subplan node ..."

* Maybe wrap 'rows' with <literal> </literal>?

-- 
Matheus Alcantara

---

From: Ilia Evdokimov <[email-masked]>
Date: Mon, 10 Feb 2025 19:38:50 +0300

> (actual time=0.049..0.049 rows=0.50 loops=2.00)

The only reason I added this was to make it clear to the user that loops 
 > 1, along with the 'rows' value. If no one finds it useful and it only 
raises more questions, I can remove it.

> an example of this case would be great!

Based on what was discussed earlier in the thread, there are cases with 
large loops [0]. However, I believe it's better not to display average 
rows with excessively long digits or in scientific notation. And, of 
course, I agree with you regarding small values. I think we should also 
add a check to ensure that the total rows is actually greater than zero. 
When the total rows is zero, we could simply display it as an integer 
without decimals. It could help users average rows is very small but not 
zero. What do you think about this approach?

> * Maybe wrap 'rows' with <literal> </literal>?

I agree with the last two points. As for the first one—maybe we could 
simply state that the average rows value can be decimal, especially for 
very small values?

[0]: 
https://www.postgresql.org/message-id/a9393107-6bb9-c835-50b7-c0f453a514b8%40postgrespro.ru

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

---

From: Matheus Alcantara <[email-masked]>
Date: Mon, 10 Feb 2025 17:43:22 -0300

> raises more questions, I can remove it.
Ok, I get it. IMHO the current behaviour of using %.0f for 'loops' would be
better, but let's see if anyone else has opinions about it.

> zero. What do you think about this approach?
Yeah, I agree with you about the long digits. My question is more about why do
we need the planstate->instrument->ntuples < nloops check? I tried to remove
this check and I got a lot of EXPLAIN output that shows 'rows' values with .00,
so I'm just trying to understand the reason. From what I've understood about
this thread is that just avoiding .00 decimals of 'rows' values that could be
just integers would be enough, is that right or I'm missing something here? I'm
just worried if we could have a scenario where nloops > 1 &&
planstate->instrument->ntuples < nloops which would make the 'rows' not be
formatted correctly.

> simply state that the average rows value can be decimal, especially for
I'm just not sure about the "small values"; the 'rows' in decimal will only
happen with small values? What would be a "small value" in this context? My main
point here is more that I think that it would be good to mention *why* the
'rows' can be decimal, not just describe that it could be decimal.

-- 
Matheus Alcantara

---

From: Ilia Evdokimov <[email-masked]>
Date: Tue, 11 Feb 2025 00:14:47 +0300

> planstate->instrument->ntuples < nloops which would make the 'rows' not be

Sorry for missing your question earlier. If you notice in the code 
above, the variable(average) 'rows' is defined as:

double rows = planstate->instrument->ntuples / nloops;

This represents the total rows divided by the number of loops. The 
condition means that variable 'rows' will always  between zero and one. 
Therefore, the average rows under such conditions cannot be greater than 
or even equal to one. I wrote this condition specifically to avoid the 
verbose expression 'rows > 0 && rows < 1'. However, since this might not 
be obvious to everyone, perhaps it'd be better to write is using 'rows' 
directly or add a comment explaining this logic.

> 'rows' can be decimal, not just describe that it could be decimal.

As for 'small values', it means that the average rows is between zero 
and one, to avoid rounding errors and misunderstanding. I think this 
would be ideal.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

---

From: Matheus Alcantara <[email-masked]>
Date: Tue, 11 Feb 2025 11:51:28 -0300

Em seg., 10 de fev. de 2025 às 18:14, Ilia Evdokimov
<[email-masked]> escreveu:
> Sorry for missing your question earlier. If you notice in the code above, the variable(average) 'rows' is defined as:
>
> This represents the total rows divided by the number of loops. The condition
> means that variable 'rows' will always  between zero and one. Therefore, the
> average rows under such conditions cannot be greater than or even equal to
> one. I wrote this condition specifically to avoid the verbose expression
> 'rows > 0 && rows < 1'. However, since this might not be obvious to everyone,
> perhaps it'd be better to write is using 'rows' directly or add a comment
> explaining this logic.
>
Thanks for the details! It makes sense to me now. I think that adding a comment
could be a good idea

> As for 'small values', it means that the average rows is between zero and
> one, to avoid rounding errors and misunderstanding. I think this would be
> ideal.
>
Get it, sounds reasonable to me.

-- 
Matheus Alcantara

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 11 Feb 2025 18:14:49 +0100

> Then I created v7 patch rebased on fb056564ec5 . Thank for your remark!
I support the idea in general, but I believe it should be expanded to 
cover all cases of parameterised plan nodes. Each rescan iteration may 
produce a different number of tuples, and rounding can obscure important 
data.

For example, consider five loops of a scan node: the first loop returns 
nine tuples, and each other - zero tuples. When we calculate the 
average, 9 divided by 5 equals 1.8. This results in an explanation that 
indicates "rows = 1," masking almost 40% of the data.

Now, if we apply the same two loops but have a total of 900,000 tuples, 
then 400,000 masked tuples represent a significant portion of the data.

Moreover, switching to a floating-point type for row explanations in 
each parameterised node would provide a more comprehensive view and add 
valuable information about the parameterisation of the node, which may 
not be immediately apparent.

-- 
regards, Andrei Lepikhov

---

From: Robert Haas <[email-masked]>
Date: Tue, 11 Feb 2025 12:41:26 -0500

> not be immediately apparent.

I agree strongly with all of this. I believe we should just implement
what was agreed here:

https://www.postgresql.org/message-id/21013.1243618236%40sss.pgh.pa.us

Let's just display 2 fractional digits when nloops>1, else 0, and call it good.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Ilia Evdokimov <[email-masked]>
Date: Tue, 11 Feb 2025 22:18:20 +0300

> Let's just display 2 fractional digits when nloops>1, else 0, and call it good.

Thank you for your review!

With such example, it's hard to disagree with it. This would really add 
valuable information. Taking all opinions into account, I have updated 
the patch v8. I have also included a check for the case where there are 
only zeros after the decimal point. We do not want to clutter the rows 
with unnecessary zeros.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From e1a46d3fa5a4d20b6ea4728b2a3955f375539295 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Tue, 11 Feb 2025 22:12:41 +0300
Subject: [PATCH] Clarify display of rows as decimal fractions

When loops > 1, the average rows value is shown as decimal fractions
with two digits after the decimal point in EXPLAIN ANALYZE.
---
 doc/src/sgml/perform.sgml                     |  2 +
 src/backend/commands/explain.c                | 55 +++++++++++++------
 src/test/regress/expected/partition_prune.out | 18 +++---
 src/test/regress/sql/partition_prune.sql      |  2 +-
 4 files changed, 51 insertions(+), 26 deletions(-)

diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index a502a2aaba..a01832040c 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -758,6 +758,8 @@ WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
     the <literal>loops</literal> value to get the total time actually spent in
     the node.  In the above example, we spent a total of 0.030 milliseconds
     executing the index scans on <literal>tenk2</literal>.
+    If <literal>loops</literal> is greater than 1,
+    the <literal>rows</literal> is shown as a decimal unless it's an integer.
    </para>
 
    <para>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c24e66f82e..ae81aeea13 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -13,6 +13,8 @@
  */
 #include "postgres.h"
 
+#include <math.h>
+
 #include "access/xact.h"
 #include "catalog/pg_type.h"
 #include "commands/createas.h"
@@ -69,6 +71,9 @@ typedef struct SerializeMetrics
  */
 #define BYTES_TO_KILOBYTES(b) (((b) + 1023) / 1024)
 
+/* Check if float/double has any decimal number */
+#define HAS_DECIMAL(x) (floor(x) != x)
+
 static void ExplainOneQuery(Query *query, int cursorOptions,
 							IntoClause *into, ExplainState *es,
 							ParseState *pstate, ParamListInfo params);
@@ -1981,14 +1986,15 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 		if (es->format == EXPLAIN_FORMAT_TEXT)
 		{
+			appendStringInfo(es->str, " (actual ");
+
 			if (es->timing)
-				appendStringInfo(es->str,
-								 " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
-								 startup_ms, total_ms, rows, nloops);
+				appendStringInfo(es->str, "time=%.3f..%.3f ", startup_ms, total_ms);
+
+			if (nloops > 1 && HAS_DECIMAL(rows))
+				appendStringInfo(es->str, "rows=%.2f loops=%.0f)", rows, nloops);
 			else
-				appendStringInfo(es->str,
-								 " (actual rows=%.0f loops=%.0f)",
-								 rows, nloops);
+				appendStringInfo(es->str, "rows=%.0f loops=%.0f)", rows, nloops);
 		}
 		else
 		{
@@ -1999,8 +2005,16 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				ExplainPropertyFloat("Actual Total Time", "ms", total_ms,
 									 3, es);
 			}
-			ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
-			ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+			if (nloops > 1 && HAS_DECIMAL(rows))
+			{
+				ExplainPropertyFloat("Actual Rows", NULL, rows, 2, es);
+				ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+			}
+			else
+			{
+				ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
+				ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+			}
 		}
 	}
 	else if (es->analyze)
@@ -2052,14 +2066,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			if (es->format == EXPLAIN_FORMAT_TEXT)
 			{
 				ExplainIndentText(es);
+				appendStringInfo(es->str, "actual ");
 				if (es->timing)
-					appendStringInfo(es->str,
-									 "actual time=%.3f..%.3f rows=%.0f loops=%.0f\n",
-									 startup_ms, total_ms, rows, nloops);
+					appendStringInfo(es->str, "time=%.3f..%.3f", startup_ms, total_ms);
+
+				if (nloops > 1 && HAS_DECIMAL(rows))
+					appendStringInfo(es->str, "rows=%.2f loops=%.0f\n", rows, nloops);
 				else
-					appendStringInfo(es->str,
-									 "actual rows=%.0f loops=%.0f\n",
-									 rows, nloops);
+					appendStringInfo(es->str, "rows=%.0f loops=%.0f\n", rows, nloops);
 			}
 			else
 			{
@@ -2070,8 +2084,17 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					ExplainPropertyFloat("Actual Total Time", "ms",
 										 total_ms, 3, es);
 				}
-				ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
-				ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+
+				if (nloops > 1 && HAS_DECIMAL(rows))
+				{
+					ExplainPropertyFloat("Actual Rows", NULL, rows, 2, es);
+					ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+				}
+				else
+				{
+					ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
+					ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
+				}
 			}
 
 			ExplainCloseWorker(n, es);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index e667503c96..fde44db1a0 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2367,7 +2367,7 @@ begin
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
-        ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=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');
         return next ln;
     end loop;
@@ -3049,14 +3049,14 @@ order by tbl1.col1, tprt.col1;
 insert into tbl1 values (1001), (1010), (1011);
 explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
-                                QUERY PLAN                                
---------------------------------------------------------------------------
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
  Nested Loop (actual rows=23 loops=1)
    ->  Seq Scan on tbl1 (actual rows=5 loops=1)
-   ->  Append (actual rows=5 loops=5)
+   ->  Append (actual rows=4.60 loops=5)
          ->  Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
                Index Cond: (col1 < tbl1.col1)
-         ->  Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
+         ->  Index Scan using tprt2_idx on tprt_2 (actual rows=2.75 loops=4)
                Index Cond: (col1 < tbl1.col1)
          ->  Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
                Index Cond: (col1 < tbl1.col1)
@@ -3070,16 +3070,16 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
 
 explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
-                                QUERY PLAN                                
---------------------------------------------------------------------------
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
  Nested Loop (actual rows=3 loops=1)
    ->  Seq Scan on tbl1 (actual rows=5 loops=1)
-   ->  Append (actual rows=1 loops=5)
+   ->  Append (actual rows=0.60 loops=5)
          ->  Index Scan using tprt1_idx on tprt_1 (never executed)
                Index Cond: (col1 = tbl1.col1)
          ->  Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
                Index Cond: (col1 = tbl1.col1)
-         ->  Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
+         ->  Index Scan using tprt3_idx on tprt_3 (actual rows=0.33 loops=3)
                Index Cond: (col1 = tbl1.col1)
          ->  Index Scan using tprt4_idx on tprt_4 (never executed)
                Index Cond: (col1 = tbl1.col1)
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 730545e86a..58756e0b18 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -586,7 +586,7 @@ begin
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
-        ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=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');
         return next ln;
     end loop;
-- 
2.34.1

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 11 Feb 2025 22:43:30 +0300

Hi! Thank you for your valuable work on this!

On 11.02.2025 22:18, Ilia Evdokimov wrote:
> With such example, it's hard to disagree with it. This would really 
> add valuable information. Taking all opinions into account, I have 
> updated the patch v8. I have also included a check for the case where 
> there are only zeros after the decimal point. We do not want to 
> clutter the rows with unnecessary zeros.
>
I looked at the patch and agree with them. I would suggest adding a 
description of how this can help in analyzing the query plans -
I think there is a lack of a description of the reason why this is done 
in the commit message.
I would also add the same to the documentation with an example.

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 11 Feb 2025 21:19:28 +0100

> Let's just display 2 fractional digits when nloops>1, else 0, and call it good.
Why are there only two fractional digits?

I reviewed the user reports where we identified issues without 
sufficient data, based on explains only, and typical examples included 
loop numbers ranging from 1E5 to 1E7 and tuples from 1E2 to 1E5. 
Therefore, it may make sense to display fractional digits up to two 
meaningful (non-zero) digits.

-- 
regards, Andrei Lepikhov

---

From: Tom Lane <[email-masked]>
Date: Tue, 11 Feb 2025 15:46:43 -0500

> Let's just display 2 fractional digits when nloops>1, else 0, and call it good.

Note that that formulation has nothing especially to do with
parameterized plan nodes.  Any nestloop inner side would end up
getting shown with fractional rowcounts.  Maybe that's fine.

I suggest that when thinking about what to change here,
you start by considering how you'd adjust the docs at
https://www.postgresql.org/docs/devel/using-explain.html
to explain the new behavior.  If you can't explain it
clearly for users, then maybe it's not such a great idea.

			regards, tom lane

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 12 Feb 2025 09:54:33 +0100

> getting shown with fractional rowcounts.  Maybe that's fine.
I partly agree with this approach. Playing around a bit, I couldn't 
invent a case where we have different numbers of tuples without 
parameters. But I can imagine it is possible or may be possible in 
future. So, it is not necessary to tangle fractional output with a 
parameterised node.
I'm unsure about the inner subtree of a JOIN - subplan may refer to the 
upper query and process a different number of tuples for every 
evaluation without any JOIN operator.
May we agree on a more general formula to print at least two meaningful 
digits if we have a fractional part?

Examples:
- actual rows = 2, nloops = 2 -> rows = 1
- actual rows = 9, nloops = 5 -> rows = 1.8
- actual rows = 101, nloops = 100 -> rows = 1.0
- actual rows = 99, nloops = 1000000 -> rows = 0.000099

It may guarantee that an EXPLAIN exposes most of the data passed the 
node, enough to tangle it with actual timing and tuples at the upper 
levels of the query.

> clearly for users, then maybe it's not such a great idea.
Agree

-- 
regards, Andrei Lepikhov

---

From: Ilia Evdokimov <[email-masked]>
Date: Wed, 12 Feb 2025 13:10:34 +0300

On 12.02.2025 11:54, Andrei Lepikhov wrote:
> May we agree on a more general formula to print at least two 
> meaningful digits if we have a fractional part?
>
> Examples:

I think the idea of keeping two significant digits after the decimal 
point is quite reasonable. The thing is, rows=0.000001 or something 
similar can only occur when loops is quite large. If we show the order 
of magnitude in rows, it will be easier for the user to estimate the 
order of total rows. For example, if we see this:

rows=0.000056 loops=4718040

the user can quickler approximate the order of total rows for analyzing 
the upper levels of the query.

However, keep in mind that I am against using the E notation, as many 
users have mentioned that they are not mathematicians and are not 
familiar with the concept of "E".

>> clearly for users, then maybe it's not such a great idea.
So do I. Firstly, I'll think how to explain it.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

---

From: Robert Haas <[email-masked]>
Date: Wed, 12 Feb 2025 13:11:07 -0500

> only zeros after the decimal point. We do not want to clutter the rows

I disagree. We don't do this for any other fractional value we print
in any other part of the system, and I do not think this case should
be some kind of weird exception.

---

From: Robert Haas <[email-masked]>
Date: Wed, 12 Feb 2025 13:19:11 -0500

> the upper levels of the query.

I agree that showing 2 digits after the decimal point in all cases is
not ideal, but I suggest that we take a practical approach. Figuring
out dynamically what number of decimal digits to display in each case
sounds complicated and we may spend a bunch of time arguing about the
details of that and get nothing committed. If we just show 2 digits
after the decimal point, it will not be perfect, but it will be 10^2
times better than what we have now.

If I'm honest, what I actually think we should do is stop dividing
values by nloops before printing them out. Every time I'm looking at a
quantity that has been divided by nloops, the very first thing I do is
try to figure out what the original value was. The whole reason I want
to display at least a couple of decimal digits here is so that I can
do that more accurately, but of course the dream would be not having
to reverse engineer it like that at all. However, I expect fierce
opposition to that idea, and no matter how misguided I may think that
opposition might be, a patch in the tree is worth two in the
CommitFest.

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Feb 2025 13:40:15 -0500

> times better than what we have now.

I was idly speculating yesterday about letting the Ryu code print
the division result, so that we get a variable number of digits.
Realistically, that'd probably result in many cases in more digits
than anybody wants, so it's not a serious proposal.  I'm cool with
the fixed-two-digits approach to start with.

			regards, tom lane

---

From: Andrei Lepikhov <[email-masked]>
Date: Wed, 12 Feb 2025 20:55:53 +0100

> the fixed-two-digits approach to start with.
Okay, since no one else voted for the meaningful-numbers approach, I 
would say that fixed size is better than nothing. It may cover some of 
my practical cases, but unfortunately, not the most problematic ones.

-- 
regards, Andrei Lepikhov

---

From: Robert Haas <[email-masked]>
Date: Wed, 12 Feb 2025 14:56:58 -0500

> my practical cases, but unfortunately, not the most problematic ones.

I don't love it either, but I do think it is significantly better than nothing.

---

From: Ilia Evdokimov <[email-masked]>
Date: Thu, 13 Feb 2025 12:05:55 +0300

On 12.02.2025 22:56, Robert Haas wrote:
> On Wed, Feb 12, 2025 at 2:55 PM Andrei Lepikhov<[email-masked]>  wrote:

I'm in favor of having some improvement rather than nothing at 
all—otherwise, we might never reach a consensus.

1. Documentation 
(v9-0001-Clarify-display-of-rows-as-decimal-fractions-DOC.patch)

One thing that bothers me is that the documentation explains how to 
compute total time, but it does not clarify how to compute total rows. 
Maybe this was obvious to others before, but now that we are displaying 
|rows| as a fraction, we should explicitly document how to interpret it 
alongside total time.

I believe it would be helpful to show a non-integer rows value in an 
example query. However, to achieve this, we need the index scan results 
to vary across iterations. One way to accomplish this is by using the 
condition t1.unique2 > t2.unique2. Additionally, I suggest making loops 
a round number (e.g., 100) for better readability, which can be achieved 
using t1.thousand < 10. The final query would look like this:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.thousand < 10 AND t1.unique2 > t2.unique2;

I believe this is an ideal example for the documentation because it not 
only demonstrates fractional rows, but also keeps the execution plan 
nearly unchanged. While the estimated and actual average row counts 
become slightly rounded, I don't see another way to ensure different 
results for each index scan.

I'm open to any feedback or suggestions for a better example to use in 
the documentation or additional explaining fractional rows in the text.

2. Code and tests 
(v9-0002-Clarify-display-of-rows-as-decimal-fractions.patch)

I left the code and tests unchanged since we agreed on a fixed format of 
two decimal places.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From 789d98383988ef6d3b0bc2c6b9b5c73a83ffd6d4 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Thu, 13 Feb 2025 11:19:03 +0300
Subject: [PATCH v9] Clarify display of rows as decimal fractions

When loops > 1, the average rows value is now displayed as a decimal fraction
with two digits after the decimal point in EXPLAIN ANALYZE.

Previously, the average rows value was always rounded to an integer,
which could lead to misleading results when estimating total rows
by multiplying rows by loop. This change ensures that users
get a more accurate representation of the data flow through execution nodes.
---
 doc/src/sgml/perform.sgml | 41 ++++++++++++++++++++-------------------
 1 file changed, 21 insertions(+), 20 deletions(-)

diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index a502a2aaba..dd61ae4507 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -717,26 +717,26 @@ FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
 <screen>
 EXPLAIN ANALYZE SELECT *
 FROM tenk1 t1, tenk2 t2
-WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
-
-                                                           QUERY PLAN
--------------------------------------------------------------------&zwsp;--------------------------------------------------------------
- Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1)
-   Buffers: shared hit=36 read=6
-   -&gt;  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1)
-         Recheck Cond: (unique1 &lt; 10)
-         Heap Blocks: exact=10
-         Buffers: shared hit=3 read=5 written=4
-         -&gt;  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1)
-               Index Cond: (unique1 &lt; 10)
+WHERE t1.thousand &lt; 10 AND t1.unique2 &gt; t2.unique2;
+
+                                                                 QUERY PLAN
+-------------------------------------------------------------------&zwsp;-------------------------------------------------------------------------
+ Nested Loop  (cost=5.40..11571.44 rows=356667 width=488) (actual time=0.042..117.205 rows=513832 loops=1)
+   Buffers: shared hit=19377 read=29
+   -&gt;  Bitmap Heap Scan on tenk1 t1  (cost=5.11..233.60 rows=107 width=244) (actual time=0.021..0.103 rows=100 loops=1)
+         Recheck Cond: (thousand &lt; 10)
+         Heap Blocks: exact=90
+         Buffers: shared hit=92
+         -&gt;  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..5.09 rows=107 width=0) (actual time=0.010..0.010 rows=100 loops=1)
+               Index Cond: (thousand &lt; 10)
                Buffers: shared hit=2
-   -&gt;  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10)
-         Index Cond: (unique2 = t1.unique2)
-         Buffers: shared hit=24 read=6
+   -&gt;  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..72.63 rows=3333 width=244) (actual time=0.006..0.410 rows=5138.32 loops=100)
+         Index Cond: (unique2 &lt; t1.unique2)
+         Buffers: shared hit=19285 read=29
  Planning:
-   Buffers: shared hit=15 dirtied=9
- Planning Time: 0.485 ms
- Execution Time: 0.073 ms
+   Buffers: shared hit=258 read=9 dirtied=2
+ Planning Time: 0.519 ms
+ Execution Time: 131.378 ms
 </screen>
 
     Note that the <quote>actual time</quote> values are in milliseconds of
@@ -756,8 +756,9 @@ WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
     values shown are averages per-execution.  This is done to make the numbers
     comparable with the way that the cost estimates are shown.  Multiply by
     the <literal>loops</literal> value to get the total time actually spent in
-    the node.  In the above example, we spent a total of 0.030 milliseconds
-    executing the index scans on <literal>tenk2</literal>.
+    the node, and to get the total rows processed in the node. In the above example,
+    we spent a total of 41 milliseconds executing the index scans,
+    and the node processed a total of 513832 rows on <literal>tenk2</literal>.
    </para>
 
    <para>
-- 
2.34.1

From a30ee71240d731c386f9dd4eb37b2817e3238807 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email-masked]>
Date: Thu, 13 Feb 2025 11:24:03 +0300
Subject: [PATCH v9] Clarify display of rows as decimal fractions

When loops > 1, the average rows value is now displayed as a decimal fraction
with two digits after the decimal point in EXPLAIN ANALYZE.

Previously, the average rows value was always rounded to an integer,
which could lead to misleading results when estimating total rows
by multiplying rows by loop. This change ensures that users
get a more accurate representation of the data flow through execution nodes.
---
 src/backend/commands/explain.c                | 55 +++++++++++++------
 src/test/regress/expected/partition_prune.out | 18 +++---
 src/test/regress/sql/partition_prune.sql      |  2 +-
 3 files changed, 49 insertions(+), 26 deletions(-)

+#include <math.h>
+
 #include "access/xact.h"

---

From: Robert Haas <[email-masked]>
Date: Thu, 13 Feb 2025 15:42:32 -0500

On Thu, Feb 13, 2025 at 4:05 AM Ilia Evdokimov
<[email-masked]> wrote:
> 1. Documentation (v9-0001-Clarify-display-of-rows-as-decimal-fractions-DOC.patch)
>
> One thing that bothers me is that the documentation explains how to compute total time, but it does not clarify how to compute total rows. Maybe this was obvious to others before, but now that we are displaying rows as a fraction, we should explicitly document how to interpret it alongside total time.
>
> I believe it would be helpful to show a non-integer rows value in an example query. However, to achieve this, we need the index scan results to vary across iterations. One way to accomplish this is by using the condition t1.unique2 > t2.unique2. Additionally, I suggest making loops a round number (e.g., 100) for better readability, which can be achieved using t1.thousand < 10. The final query would look like this:
>
> EXPLAIN ANALYZE SELECT *
> FROM tenk1 t1, tenk2 t2
> I believe this is an ideal example for the documentation because it not only demonstrates fractional rows, but also keeps the execution plan nearly unchanged. While the estimated and actual average row counts become slightly rounded, I don't see another way to ensure different results for each index scan.

Anyone else have an opinion on this? I see Ilia's point, but a
non-equality join is probably an atypical case.

> 2. Code and tests (v9-0002-Clarify-display-of-rows-as-decimal-fractions.patch)
>
> I left the code and tests unchanged since we agreed on a fixed format of two decimal places.

This still has the HAS_DECIMAL() thing to which I objected.