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 <ilya.evdoki...@tantorlabs.com>
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 < 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
