Re: 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(at)timbira(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joshua Tolley <eggyknap(at)gmail(dot)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(at)2ndQuadrant(dot)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(at)gmail(dot)com> writes:
> On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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

On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:

> On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> > On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com>
> wrote:
>
>> On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> > On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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 nloops
> > 1 overcomes any objection I have on those grounds.
>
> David J.
>
>

--
Ibrar Ahmed

On Thu, Jun 23, 2022 at 2:25 AM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
>
> On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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(dot)ahmad(at)gmail(dot)com> wrote:
>
>
>
> On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>> On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
>>>
>>> On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>
>>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> > On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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(at)mit(dot)edu> wrote:
> > - -> 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.

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(at)gmail(dot)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

On Thu, Jul 7, 2022 at 2:41 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Thu, Jun 23, 2022 at 2:25 AM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
> >
> > On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)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.
>
> Agreed removed.

> Few miscellaneous comments:
> ========================
> *
> static FullTransactionId XactTopFullTransactionId =
> {InvalidTransactionId};
> -static int nParallelCurrentXids = 0;
> +static int nParallelCurrentXids = 0;
>
> Removed.

> 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?
>

Done

>
> * 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?
>
> I think there are tests, and I have updated the results accordingly.

> --
> With Regards,
> Amit Kapila.
>

--
Ibrar Ahmed

On Thu, Jul 7, 2022 at 3:14 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:

> On Thu, Jun 23, 2022 at 2:25 AM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
> >
> >
> >
> > On Thu, Jun 23, 2022 at 1:04 AM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >>
> >> On Wed, Jun 22, 2022 at 12:11 PM Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com>
> wrote:
> >>>
> >>> On Thu, Jun 23, 2022 at 12:01 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>>>
> >>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >>>> > On Jun 2, 2009, at 9:41 AM, Simon Riggs <simon(at)2ndQuadrant(dot)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.
>

Thanks for the review.

>
> 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;
>
>
I have fixed the regression and removed non-related code.

> Regards,
> Vignesh
>

--
Ibrar Ahmed

On Thu, Jul 7, 2022 at 10:53 PM Greg Stark <stark(at)mit(dot)edu> wrote:

> > - -> 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
>

+ -> Parallel Seq Scan on tenk1 (actual rows=1960.00

I have added a new check to remove any ".00" from the output because in
the case of parallel queries we are getting that. Secondly, it is
disturbing many test case outputs.

--
Ibrar Ahmed

On Fri, Jul 8, 2022 at 3:50 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
> 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.
>

I feel the discussion has slightly deviated which makes it unclear
whether this patch is required or not?

--
With Regards,
Amit Kapila.

On Fri, Jul 22, 2022 at 6:47 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> I feel the discussion has slightly deviated which makes it unclear
> whether this patch is required or not?

My opinion is that showing some fractional digits at least when
loops>1 would be better than what we have now. It might not be the
best thing we could do, but it would be better than doing nothing.

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

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested

Verified patch 'explain_float_row_v3.patch' on master & REL_15_STABLE branches.

The new status of this patch is: Ready for Committer

The following review has been posted through the commitfest application:
make installcheck-world: tested, failed
Implements feature: tested, failed
Spec compliant: not tested
Documentation: not tested

LGTM

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested

The previous review was incorrectly posted. Updating the pat.ch review

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jul 22, 2022 at 6:47 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wr=
ote:
>> I feel the discussion has slightly deviated which makes it unclear
>> whether this patch is required or not?

> My opinion is that showing some fractional digits at least when
> loops>1 would be better than what we have now. It might not be the
> best thing we could do, but it would be better than doing nothing.

Yeah, I think that is a reasonable compromise.

I took a brief look through the patch, and I have some review
comments:

* Code like this is pretty awful:

appendStringInfo(es->str,
(nloops =3D=3D 1 || !HAS_DECIMAL(rows)) ?
" rows=3D%.0f loops=3D%.0f)" : " rows=3D%=
.2f loops=3D%.0f)",
rows, nloops);

Don't use variable format strings. They're hard to read and they
probably defeat compile-time checks that the arguments match the
format string. You could use a "*" field width instead, ie

appendStringInfo(es->str,
" rows=3D%.*f loops=3D%.0f)",
(nloops =3D=3D 1 || !HAS_DECIMAL(rows)) ?=
2 : 0,
rows, nloops);

That'd also allow you to reduce the code churn you've added by
splitting some appendStringInfo calls.

* I'm fairly concerned about how stable this'll be in the buildfarm,
in particular I fear HAS_DECIMAL() is not likely to give consistent
results across platforms. I gather that an earlier version of the patch
tried to check whether the fractional part would be zero to two decimal
places, rather than whether it's exactly zero. Probably want to put
back something like that.

* Another thought is that the non-text formats tend to prize output
consistency over readability, so maybe we should just always use 2
fractional digits there, rather than trying to minimize visible changes.

* We need some doc adjustments, surely, to explain what the heck this
means.

regards, tom lane

On 22/7/2022 16:47, Amit Kapila wrote:
> I feel the discussion has slightly deviated which makes it unclear
> whether this patch is required or not?

After quick review I want to express my thoughts.
At first, We have been waiting for this feature for years. Often clients
give an explain to us where we see something like:
"rows=0, loops=1000000".
Without verbose mode, I can't even understand whether this node produces
any rows or not.
So, I think this feature is useful for parameterized plans mostly.
Also, printing two decimal digits or even three isn't meaningful -
sometimes we have a plan where number of loops is about 1E6 or even 1E7,
but number of real rows is equal 100 or 1000.
To overcome this issue, I see two options:
1. Show the exact number of tuples without division by loops (fair case
but invasive and bad for automation tools).
2. Show rows in scientific format like X.XXEXX.
I vote for second option.

--
regards,
Andrey Lepikhov
Postgres Professional

On Sun, Nov 6, 2022 at 10:12 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Fri, Jul 22, 2022 at 6:47 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wr=
> ote:
> >> I feel the discussion has slightly deviated which makes it unclear
> >> whether this patch is required or not?
>
> > My opinion is that showing some fractional digits at least when
> > loops>1 would be better than what we have now. It might not be the
> > best thing we could do, but it would be better than doing nothing.
>
> Yeah, I think that is a reasonable compromise.
>
>
Thanks, I have modified everything as suggested, except one point

> I took a brief look through the patch, and I have some review
> comments:
>
> * Code like this is pretty awful:
>
> appendStringInfo(es->str,
> (nloops =3D=3D 1 || !HAS_DECIMAL(rows)) ?
> " rows=3D%.0f loops=3D%.0f)" : " rows=3D%=
> .2f loops=3D%.0f)",
> rows, nloops);
>
> Don't use variable format strings. They're hard to read and they
> probably defeat compile-time checks that the arguments match the
> format string. You could use a "*" field width instead, ie
>
> appendStringInfo(es->str,
> " rows=3D%.*f loops=3D%.0f)",
> (nloops =3D=3D 1 || !HAS_DECIMAL(rows)) ?=
> 2 : 0,
> rows, nloops);
>
> That'd also allow you to reduce the code churn you've added by
> splitting some appendStringInfo calls.
>
> * I'm fairly concerned about how stable this'll be in the buildfarm,
> in particular I fear HAS_DECIMAL() is not likely to give consistent
> results across platforms. I gather that an earlier version of the patch
> tried to check whether the fractional part would be zero to two decimal
> places, rather than whether it's exactly zero. Probably want to put
> back something like that.
>
> * Another thought is that the non-text formats tend to prize output
> consistency over readability, so maybe we should just always use 2
> fractional digits there, rather than trying to minimize visible changes.
>
> In that, we need to adjust a lot of test case outputs.

* We need some doc adjustments, surely, to explain what the heck this
> means.
>

> regards, tom lane
>

--
Ibrar Ahmed

On Wed, 4 Jan 2023 at 10:05, Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
>
> Thanks, I have modified everything as suggested, except one point
>
> > Don't use variable format strings. They're hard to read and they
> > probably defeat compile-time checks that the arguments match the
> > format string. You could use a "*" field width instead, ie
...
> > * Another thought is that the non-text formats tend to prize output
> > consistency over readability, so maybe we should just always use 2
> > fractional digits there, rather than trying to minimize visible changes.
>
> In that, we need to adjust a lot of test case outputs.

> > * We need some doc adjustments, surely, to explain what the heck this
> > means.

That sounds like three points :) But they seem like pretty good
arguments to me and straightforward if a little tedious to adjust.

This patch was marked Returned with Feedback and then later Waiting on
Author. And it hasn't had any updates since January. What is the state
on this feedback? If it's already done we can set the patch to Ready
for Commit and if not do you disagree with the proposed changes?

It's actually the kind of code cleanup changes I'm reluctant to bump a
patch for. It's not like a committer can't make these kinds of changes
when committing. But there are so many patches they're likely to just
focus on a different patch when there are adjustments like this
pending.

--
Gregory Stark
As Commitfest Manager

On Mon, Mar 20, 2023 at 7:56 PM Gregory Stark (as CFM) <stark(dot)cfm(at)gmail(dot)com>
wrote:

> On Wed, 4 Jan 2023 at 10:05, Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
> >
> > Thanks, I have modified everything as suggested, except one point
> >
> > > Don't use variable format strings. They're hard to read and they
> > > probably defeat compile-time checks that the arguments match the
> > > format string. You could use a "*" field width instead, ie
> ...
> > > * Another thought is that the non-text formats tend to prize output
> > > consistency over readability, so maybe we should just always use 2
> > > fractional digits there, rather than trying to minimize visible
> changes.
> >
> > In that, we need to adjust a lot of test case outputs.
>
> > > * We need some doc adjustments, surely, to explain what the heck this
> > > means.
>
> That sounds like three points :) But they seem like pretty good
> arguments to me and straightforward if a little tedious to adjust.
>
> This patch was marked Returned with Feedback and then later Waiting on
> Author. And it hasn't had any updates since January. What is the state
> on this feedback? If it's already done we can set the patch to Ready
> for Commit and if not do you disagree with the proposed changes?
>
> If there is a consensus to modify the test cases' output, I am willing to
make the necessary changes and adjust the patch accordingly. However,
if there is a preference to keep the output of certain test cases unchanged,
I can rebase and modify the patch accordingly to accommodate those
preferences.

> It's actually the kind of code cleanup changes I'm reluctant to bump a
> patch for. It's not like a committer can't make these kinds of changes
> when committing. But there are so many patches they're likely to just
> focus on a different patch when there are adjustments like this
> pending.
>
> --
> Gregory Stark
> As Commitfest Manager
>

--
Ibrar Ahmed

> On 8 Jun 2023, at 19:49, Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
> On Mon, Mar 20, 2023 at 7:56 PM Gregory Stark (as CFM) <stark(dot)cfm(at)gmail(dot)com <mailto:stark(dot)cfm(at)gmail(dot)com>> wrote:

> This patch was marked Returned with Feedback and then later Waiting on
> Author. And it hasn't had any updates since January. What is the state
> on this feedback? If it's already done we can set the patch to Ready
> for Commit and if not do you disagree with the proposed changes?
>
> If there is a consensus to modify the test cases' output, I am willing to
> make the necessary changes and adjust the patch accordingly. However,
> if there is a preference to keep the output of certain test cases unchanged,
> I can rebase and modify the patch accordingly to accommodate those preferences.

As there hasn't been any other comments I suggest updating your patch to
address Tom's comments to see if we can make progress here.

--
Daniel Gustafsson

> On 3 Jul 2023, at 18:34, Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
>
>> On 8 Jun 2023, at 19:49, Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com> wrote:
>> On Mon, Mar 20, 2023 at 7:56 PM Gregory Stark (as CFM) <stark(dot)cfm(at)gmail(dot)com <mailto:stark(dot)cfm(at)gmail(dot)com>> wrote:
>
>> This patch was marked Returned with Feedback and then later Waiting on
>> Author. And it hasn't had any updates since January. What is the state
>> on this feedback? If it's already done we can set the patch to Ready
>> for Commit and if not do you disagree with the proposed changes?
>>
>> If there is a consensus to modify the test cases' output, I am willing to
>> make the necessary changes and adjust the patch accordingly. However,
>> if there is a preference to keep the output of certain test cases unchanged,
>> I can rebase and modify the patch accordingly to accommodate those preferences.
>
> As there hasn't been any other comments I suggest updating your patch to
> address Tom's comments to see if we can make progress here.

Since there hasn't been any updates here, and the thread has been stalled, I'm
marking this returned with feedback. Please feel free to resubmit a version of
the patch addressing comments to a future CF.

--
Daniel Gustafsson