explain analyze rows=%.0f
I have always assumed that there is some very good reason why EXPLAIN
ANALYZE reports the number of rows as an integer rather than a
floating point value, but in reading explain.c it seems that the
reason is just that we decided to round to zero decimal places. Any
chance we could reconsider this decision? I often find myself wanting
to know the value that is here called ntuples, but rounding
ntuples/nloops off to the nearest integer loses too much precision.
(Before someone mentions it, yes that would be a good thing to include
in XML-formatted explain output. But I don't see that including a
couple of decimal places would hurt the text output format either.)
...Robert
Robert Haas escreveu:
I have always assumed that there is some very good reason why EXPLAIN
ANALYZE reports the number of rows as an integer rather than a
floating point value, but in reading explain.c it seems that the
reason is just that we decided to round to zero decimal places. Any
chance we could reconsider this decision? I often find myself wanting
to know the value that is here called ntuples, but rounding
ntuples/nloops off to the nearest integer loses too much precision.
Don't you think is too strange having, for example, 6.67 rows? I would confuse
users and programs that parses the EXPLAIN output. However, I wouldn't object
to add ntuples to an extended explain output (as discussed in the other thread).
--
Euler Taveira de Oliveira
http://www.timbira.com/
On Thu, May 28, 2009 at 11:00 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:
Robert Haas escreveu:
I have always assumed that there is some very good reason why EXPLAIN
ANALYZE reports the number of rows as an integer rather than a
floating point value, but in reading explain.c it seems that the
reason is just that we decided to round to zero decimal places. Any
chance we could reconsider this decision? I often find myself wanting
to know the value that is here called ntuples, but rounding
ntuples/nloops off to the nearest integer loses too much precision.Don't you think is too strange having, for example, 6.67 rows?
No stranger than having it say 7 when it's really not. Actually mine
mostly come out 1 when the real value is somewhere between 0.5 and
1.49. :-(
...Robert
On Thu, May 28, 2009 at 11:12:42PM -0400, Robert Haas wrote:
On Thu, May 28, 2009 at 11:00 PM, Euler Taveira de Oliveira
Don't you think is too strange having, for example, 6.67 rows?
No stranger than having it say 7 when it's really not. Actually mine
mostly come out 1 when the real value is somewhere between 0.5 and
1.49. :-(
+1. It would help users realize more quickly that some of the values in the
EXPLAIN output are, for instance, *average* number of rows *per iteration* of a
nested loop, say, rather than total rows found in all loops. That's an
important distinction that isn't immediately clear to the novice EXPLAIN
reader, but would become so very quickly as users tried to figure out how a
scan could come up with a fractional row.
- Josh / eggyknap
Joshua Tolley <eggyknap@gmail.com> writes:
On Thu, May 28, 2009 at 11:12:42PM -0400, Robert Haas wrote:
On Thu, May 28, 2009 at 11:00 PM, Euler Taveira de Oliveira
Don't you think is too strange having, for example, 6.67 rows?
No stranger than having it say 7 when it's really not. Actually mine
mostly come out 1 when the real value is somewhere between 0.5 and
1.49. :-(
+1. It would help users realize more quickly that some of the values in the
EXPLAIN output are, for instance, *average* number of rows *per iteration* of a
nested loop, say, rather than total rows found in all loops.
I think it would only be sensible to show fractional digits if nloops is
greater than 1. Otherwise the value must in fact be an integer, and
you're just going to confuse people more by suggesting that it might not
be.
regards, tom lane
On Fri, May 29, 2009 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Joshua Tolley <eggyknap@gmail.com> writes:
On Thu, May 28, 2009 at 11:12:42PM -0400, Robert Haas wrote:
On Thu, May 28, 2009 at 11:00 PM, Euler Taveira de Oliveira
Don't you think is too strange having, for example, 6.67 rows?
No stranger than having it say 7 when it's really not. Actually mine
mostly come out 1 when the real value is somewhere between 0.5 and
1.49. :-(+1. It would help users realize more quickly that some of the values in the
EXPLAIN output are, for instance, *average* number of rows *per iteration* of a
nested loop, say, rather than total rows found in all loops.I think it would only be sensible to show fractional digits if nloops is
greater than 1. Otherwise the value must in fact be an integer, and
you're just going to confuse people more by suggesting that it might not
be.
That might be over-engineering, but I'll take it.
...Robert
Euler Taveira de Oliveira wrote:
Robert Haas escreveu:
...EXPLAIN ANALYZE reports the number of rows as an integer... Any
chance we could reconsider this decision? I often find myself wanting
to know the value that is here called ntuples, but rounding
ntuples/nloops off to the nearest integer loses too much precision.Don't you think is too strange having, for example, 6.67 rows? I would confuse
users and programs that parses the EXPLAIN output. However, I wouldn't object
I don't think it's that confusing. If it says "0.1 rows", I imagine most
people would infer that this means "typically 0, but sometimes 1 or a few" rows.
What I'd find strange about "6.67 rows" in your example is more that on
the estimated rows side, it seems to imply an unrealistically precise estimate
in the same way that "667 rows" would seem unrealistically precise to me.
Maybe rounding to 2 significant digits would reduce confusion?
On Mon, 2009-06-01 at 20:30 -0700, Ron Mayer wrote:
What I'd find strange about "6.67 rows" in your example is more that on
the estimated rows side, it seems to imply an unrealistically precise estimate
in the same way that "667 rows" would seem unrealistically precise to me.
Maybe rounding to 2 significant digits would reduce confusion?
You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
On Mon, 2009-06-01 at 20:30 -0700, Ron Mayer wrote:
What I'd find strange about "6.67 rows" in your example is more
that on
the estimated rows side, it seems to imply an unrealistically
precise estimate
in the same way that "667 rows" would seem unrealistically precise
to me.
Maybe rounding to 2 significant digits would reduce confusion?You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.
Bingo.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.
Bingo.
Uh, the planner's estimate *is* an integer. What was under discussion
(I thought) was showing some fractional digits in the case where EXPLAIN
ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer. In that
case the measured value can be considered arbitrarily precise --- though
I think in practice one or two fractional digits would be plenty.
regards, tom lane
...Robert
On Jun 2, 2009, at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com>
wrote:You're right that the number of significant digits already exceeds
the
true accuracy of the computation. I think what Robert wants to see
is
the exact value used in the calc, so the estimates can be checked
more
thoroughly than is currently possible.Bingo.
Uh, the planner's estimate *is* an integer. What was under discussion
(I thought) was showing some fractional digits in the case where
EXPLAIN
ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer. In that
case the measured value can be considered arbitrarily precise ---
though
I think in practice one or two fractional digits would be plenty.
We're in violent agreement here.
...Robert
On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.Bingo.
Uh, the planner's estimate *is* an integer. What was under discussion
(I thought) was showing some fractional digits in the case where EXPLAIN
ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer. In that
case the measured value can be considered arbitrarily precise --- though
I think in practice one or two fractional digits would be plenty.regards, tom lane
Hi,
I was looking at the TODO list and found that the issue requires
a quick fix. Attached is a patch which shows output like this. It shows the
fraction digits in case of loops > 1
postgres=# explain analyze select * from foo;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..64414.79 rows=2326379 width=8) (actual
time=0.025..277.096 rows=2344671 loops=1
Planning Time: 0.516 ms
Execution Time: 356.993 ms
(3 rows)
postgres=# explain analyze select * from foo where b = (select c from
bar where c = 1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=8094.37..78325.11 rows=2326379 width=8)
(actual time=72.352..519.159 rows=2344671 loops=1
Filter: (b = $1)
InitPlan 1 (returns $1)
-> Gather (cost=1000.00..8094.37 rows=1 width=4) (actual
time=0.872..72.434 rows=1 loops=1
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on bar (cost=0.00..7094.27 rows=1
width=4) (actual time=41.931..65.382 rows=0.33 loops=3)
Filter: (c = 1)
Rows Removed by Filter: 245457
Planning Time: 0.277 ms
Execution Time: 597.795 ms
(11 rows)
--
Ibrar Ahmed
Attachments:
explain_float_row.patchapplication/octet-stream; name=explain_float_row.patchDownload+28-13
On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.Bingo.
Uh, the planner's estimate *is* an integer. What was under discussion
(I thought) was showing some fractional digits in the case where EXPLAIN
ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer. In that
case the measured value can be considered arbitrarily precise --- though
I think in practice one or two fractional digits would be plenty.regards, tom lane
Hi,
I was looking at the TODO list and found that the issue requires
a quick fix. Attached is a patch which shows output like this.
Quick code review:
+ "actual rows=%.0f loops=%.0f": " rows=%.2f loops=%.0f",
The leading space before the else block "rows" does not belong.
There should be a space after the colon.
The word "actual" that you are dropping in the else block seems like it
should belong - it is a header for the entire section not just a modifier
for the word "rows". This is evidenced by the timing block verbiage where
rows is standalone and the word actual comes before time. In short, only
the format specifier should change under the current scheme. Both sections.
- WRITE_FLOAT_FIELD(rows, "%.0f");
+ WRITE_FLOAT_FIELD(rows, "%.2f");This one looks suspicious, though I haven't dug into the code to see
exactly what all is being touched. That it doesn't have an nloops
condition like everything else stands out.
Tooling that expects an integer is the only downside I see here, but I
concur that the usability of always showing two decimal places when nloops
1 overcomes any objection I have on those grounds.
David J.
On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar.ahmad@gmail.com>
wrote:On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.Bingo.
Uh, the planner's estimate *is* an integer. What was under discussion
(I thought) was showing some fractional digits in the case where EXPLAIN
ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer. In that
case the measured value can be considered arbitrarily precise --- though
I think in practice one or two fractional digits would be plenty.regards, tom lane
Hi,
I was looking at the TODO list and found that the issue requires
a quick fix. Attached is a patch which shows output like this.Quick code review:
+ "actual rows=%.0f loops=%.0f": " rows=%.2f loops=%.0f",
The leading space before the else block "rows" does not belong.
There should be a space after the colon.
Thanks, David for your quick response. I have updated the patch.
The word "actual" that you are dropping in the else block seems like it
should belong - it is a header for the entire section not just a modifier
for the word "rows". This is evidenced by the timing block verbiage where
rows is standalone and the word actual comes before time. In short, only
the format specifier should change under the current scheme. Both sections.- WRITE_FLOAT_FIELD(rows, "%.0f"); + WRITE_FLOAT_FIELD(rows, "%.2f");This one looks suspicious, though I haven't dug into the code to see
exactly what all is being touched. That it doesn't have an nloops
condition like everything else stands out.I was also thinking about that, but I don't see any harm when we
ultimately truncating that decimal
at a latter stage of code in case of loop = 1.
Tooling that expects an integer is the only downside I see here, but I
concur that the usability of always showing two decimal places when nloops1 overcomes any objection I have on those grounds.
David J.
--
Ibrar Ahmed
Attachments:
explain_float_row_v2.patchapplication/octet-stream; name=explain_float_row_v2.patchDownload+24-11
On Thu, Jun 23, 2022 at 2:25 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
- WRITE_FLOAT_FIELD(rows, "%.0f"); + WRITE_FLOAT_FIELD(rows, "%.2f");This one looks suspicious, though I haven't dug into the code to see exactly what all is being touched. That it doesn't have an nloops condition like everything else stands out.
I was also thinking about that, but I don't see any harm when we ultimately truncating that decimal
at a latter stage of code in case of loop = 1.
That change is in the path node which we anyway not going to target as
part of this change. We only want to change the display for actual
rows in Explain Analyze. So, I can't see how the quoted change can
help in any way.
Few miscellaneous comments:
========================
*
static FullTransactionId XactTopFullTransactionId = {InvalidTransactionId};
-static int nParallelCurrentXids = 0;
+static int nParallelCurrentXids = 0;
I don't see why this change is required.
* Can you please add a comment explaining why we are making this
change for actual rows?
* Can you please write a test case unless there is some existing test
that covers the change by displaying actual rows values in decimal but
in that case patch should have that changed output test? If you don't
think we can reliably write such a test then please let me know the
reason?
--
With Regards,
Amit Kapila.
On Thu, Jun 23, 2022 at 2:25 AM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar.ahmad@gmail.com> wrote:
On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon@2ndQuadrant.com> wrote:
You're right that the number of significant digits already exceeds the
true accuracy of the computation. I think what Robert wants to see is
the exact value used in the calc, so the estimates can be checked more
thoroughly than is currently possible.Bingo.
Uh, the planner's estimate *is* an integer. What was under discussion
(I thought) was showing some fractional digits in the case where EXPLAIN
ANALYZE is outputting a measured row count that is an average over
multiple loops, and therefore isn't necessarily an integer. In that
case the measured value can be considered arbitrarily precise --- though
I think in practice one or two fractional digits would be plenty.regards, tom lane
Hi,
I was looking at the TODO list and found that the issue requires
a quick fix. Attached is a patch which shows output like this.Quick code review:
+ "actual rows=%.0f loops=%.0f": " rows=%.2f loops=%.0f",
The leading space before the else block "rows" does not belong.
There should be a space after the colon.
Thanks, David for your quick response. I have updated the patch.
The word "actual" that you are dropping in the else block seems like it should belong - it is a header for the entire section not just a modifier for the word "rows". This is evidenced by the timing block verbiage where rows is standalone and the word actual comes before time. In short, only the format specifier should change under the current scheme. Both sections.
- WRITE_FLOAT_FIELD(rows, "%.0f"); + WRITE_FLOAT_FIELD(rows, "%.2f");This one looks suspicious, though I haven't dug into the code to see exactly what all is being touched. That it doesn't have an nloops condition like everything else stands out.
I was also thinking about that, but I don't see any harm when we ultimately truncating that decimal
at a latter stage of code in case of loop = 1.
Thanks for the patch.
1) There are some existing regression tests that are failing, you
should update the expect files accordingly for the same:
--- /home/vignesh/postgres/src/test/regress/expected/select_parallel.out
2022-05-18 20:51:46.874818044 +0530
+++ /home/vignesh/postgres/src/test/regress/results/select_parallel.out
2022-07-07 15:27:34.450440922 +0530
@@ -545,17 +545,17 @@
explain (analyze, timing off, summary off, costs off)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1
and tenk2.thousand=0;
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
-> Nested Loop (actual rows=98000 loops=1)
-> Seq Scan on tenk2 (actual rows=10 loops=1)
Filter: (thousand = 0)
Rows Removed by Filter: 9990
- -> Gather (actual rows=9800 loops=10)
+ -> Gather (actual rows=9800.00 loops=10)
Workers Planned: 4
Workers Launched: 4
- -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50)
+ -> Parallel Seq Scan on tenk1 (actual rows=1960.00 loops=50)
Filter: (hundred > 1) test select_parallel ... FAILED 744 ms
partition_prune ... FAILED 861 ms
explain ... FAILED 134 ms
memoize ... FAILED 250 ms
2) This change is not required as part of this patch:
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -122,7 +122,7 @@ bool bsysscan = false;
* lookups as fast as possible.
*/
static FullTransactionId XactTopFullTransactionId = {InvalidTransactionId};
-static int nParallelCurrentXids = 0;
+static int nParallelCurrentXids = 0;
static TransactionId *ParallelCurrentXids;Regards,
Vignesh
- -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) + -> Parallel Seq Scan on tenk1 (actual rows=1960.00
At the not inconsiderable risk of bike-shedding....
I'm wondering if printing something like 0.00 will be somewhat
deceptive when the real value is non-zero but less than 1 row per 200
loops. I wonder if the number of decimal places should be calculated
to produce a minimum of one non-zero digit for non-zero values.
--
greg
On Thu, Jul 7, 2022 at 1:53 PM Greg Stark <stark@mit.edu> wrote:
- -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) + -> Parallel Seq Scan on tenk1 (actual rows=1960.00At the not inconsiderable risk of bike-shedding....
I'm wondering if printing something like 0.00 will be somewhat
deceptive when the real value is non-zero but less than 1 row per 200
loops. I wonder if the number of decimal places should be calculated
to produce a minimum of one non-zero digit for non-zero values.
I mean, what I really want here if I'm honest is to not have the
system divide the number of rows by the loop count. And it sort of
sounds like maybe that's what you want, too. You want to know whether
the loop count is actually zero, not whether it's close to zero when
you divide it by some number that might be gigantic.
Parallel query's treatment of this topic has come in for some
criticism, but I don't know what else it could really do: there could
be any number of loops in each backend, and it need not be the same
across all backends, so all it can do is add up the loop counts just
like it adds up the row counts and times. And if we just printed out
those totals, the result would be understandable by everyone. But we
insist on dividing it by the loop count, and then things get really
obscure. Consider this example, which does not involve parallel query:
Nested Loop (actual time=TIME FOR THIS AND ALL CHILDREN rows=THE REAL
ROW COUNT loops=1)
-> Seq Scan on something (actual time=THE TIME IT REALLY TOOK rows=THE
REAL ROW COUNT loops=1)
-> Index Scan using someidx on somethingelse (actual time=NOT REALLY
HOW LONG IT TOOK rows=NOT REALLY HOW MANY ROWS WE GOT loops=HUGE
NUMBER)
If I'm looking at this plan and trying to find out what's gone wrong,
I want to know how much time got spent in the nested loop, how much
time got spent in the Seq Scan, and how much time got spent in the
Index Scan. It's easy to figure out how much time got spent in the Seq
Scan, but to find out how much time got spent in the Index Scan, I
have to multiply the time by the loop count. Then, I have to add that
number to the time spent in the Seq Scan and subtract that from the
time from the nested loop to find the time spent on the nested loop
itself. This is quite a lot of computation, especially if the plan
involves a dozen or two different nested loops, and if we didn't
insist on dividing the time by the loop count, it would be MUCH EASIER
to figure out whether the time spent in the Index Scan is a
significant percentage of the total time or not.
And likewise, if you're trying to understand the row count for the
nested loop, it would be a heck of a lot simpler if you could see the
*raw* row count for the index scan. It's unclear to me what value
there ever is in knowing that the number of rows per iteration was
about 0 or about 1 or about 2. The only thing I'm ever going to do
with the row count that gets printed here is multiply it by the loop
count and then try to figure out how much precision I've lost because
of limits on the number of decimal places. Right now that's basically
all of it because nearly every case ends up with the index scan having
rows=1, so even just adding 2 decimal places will help a lot. But I'm
still just going to be reverse engineering what I really want to know,
which is the original number, from what the system gives me, which is
a needlessly-obfuscated version of that value.
Grumble, grumble. It's sad that it's been 13 years and we haven't done
anything about this.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Thu, Jul 7, 2022 at 1:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
Nested Loop (actual time=TIME FOR THIS AND ALL CHILDREN rows=THE REAL
ROW COUNT loops=1)
-> Seq Scan on something (actual time=THE TIME IT REALLY TOOK rows=THE
REAL ROW COUNT loops=1)
-> Index Scan using someidx on somethingelse (actual time=NOT REALLY
HOW LONG IT TOOK rows=NOT REALLY HOW MANY ROWS WE GOT loops=HUGE
NUMBER)If I'm looking at this plan and trying to find out what's gone wrong,
I want to know how much time got spent in the nested loop, how much
time got spent in the Seq Scan, and how much time got spent in the
Index Scan. It's easy to figure out how much time got spent in the Seq
Scan, but to find out how much time got spent in the Index Scan, I
have to multiply the time by the loop count.
I agree that this general state of affairs is very confusing, and
seems like something that we should still improve. Just because it's a
very old way of presenting the information doesn't mean that it's the
best one, or even a particularly good one. Plus you could probably
make some kind of concession in the direction of maintaining
compatibility with the current approach if you had to. Right?
--
Peter Geoghegan
On Thu, Jul 07, 2022 at 04:21:37PM -0400, Robert Haas wrote:
I mean, what I really want here if I'm honest is to not have the
system divide the number of rows by the loop count. And it sort of
sounds like maybe that's what you want, too. You want to know whether
the loop count is actually zero, not whether it's close to zero when
you divide it by some number that might be gigantic.
...
involves a dozen or two different nested loops, and if we didn't
insist on dividing the time by the loop count, it would be MUCH EASIER
to figure out whether the time spent in the Index Scan is a
significant percentage of the total time or not.
I think the guiding princible for what to do should be to reduce how much is
needed to explain about how to interpret what explain is showing...
The docs say this:
| In such cases, the loops value reports the total number of executions of the
| node, and the actual time and rows 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 loops value to get the total time
| actually spent in the node.
On Thu, Jul 07, 2022 at 01:45:19PM -0700, Peter Geoghegan wrote:
Plus you could probably
make some kind of concession in the direction of maintaining
compatibility with the current approach if you had to. Right?
The minimum would be to show the information in a way that makes it clear that
it's "new style" output showing a total and not an average, so that a person
who sees it knows how to interpret it (same for the web "explain tools")
A concession would be to show the current information *plus* total/raw values.
This thread is about how to display the existing values. But note that there's
a CF entry for also collecting more values to show things like min/max rows per
loop.
https://commitfest.postgresql.org/38/2765/
Add extra statistics to explain for Nested Loop
--
Justin
