public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ilia Evdokimov <[email protected]>
To: Robert Haas <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Guillaume Lelarge <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: Ibrar Ahmed <[email protected]>
Cc: Gregory Stark (as CFM) <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: vignesh C <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected]
Subject: Re: explain analyze rows=%.0f
Date: Tue, 11 Feb 2025 22:18:20 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+Tgmobgkh5CW=4Xpejw9nR6Onv8SZh0nnr+=HmNAFyyHZb0eg@mail.gmail.com>
References: <CA+TgmoZv8DvmeQHQAX+rkn68id2N+DoUhFZB36d-_w1tyAeqeA@mail.gmail.com>
<CALtqXTfM3GJbFsB63ajfHcYvFhUA0ySX4f4MVS3Dau2vopU+Rw@mail.gmail.com>
<CAM-w4HNLRO8QJvF3x13wjjdkJHobORxaKrcuyF1cKaTiXuvDbA@mail.gmail.com>
<CALtqXTcZE6YW=m9ZmNU6sRJ+RTC15NFc__foP1S2KaWvGOf6JA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAECtzeVpocPwcQ0ccrXsmcvH_8YkJBdUfsJCzb8NKrEodmCg_w@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAFY6G8f6Sdu9cvfF2NYWWZMHD0ecTCPuNT7VktPH5NwxY7hcog@mail.gmail.com>
<[email protected]>
<CAFY6G8dgp494Xi2+VUUyM2CvuSQ+3nnzWi44gURWbCHjEWW89g@mail.gmail.com>
<[email protected]>
<[email protected]>
<CA+Tgmobgkh5CW=4Xpejw9nR6Onv8SZh0nnr+=HmNAFyyHZb0eg@mail.gmail.com>
On 11.02.2025 20:41, Robert Haas wrote:
> On Tue, Feb 11, 2025 at 12:14 PM Andrei Lepikhov <[email protected]> wrote:
>> 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.
> 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.
>
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.
Attachments:
[text/x-patch] v8-0001-Clarify-display-of-rows-as-decimal-fractions.patch (8.4K, ../[email protected]/2-v8-0001-Clarify-display-of-rows-as-decimal-fractions.patch)
download | inline diff:
From e1a46d3fa5a4d20b6ea4728b2a3955f375539295 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email protected]>
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 < 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
view thread (26+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: explain analyze rows=%.0f
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox