Message-ID: <73559119-65b0-4989-b69c-53c81c65de79@tantorlabs.com>
Date: Mon, 10 Feb 2025 19:38:50 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: explain analyze rows=%.0f
To: Matheus Alcantara <matheusssilv97@gmail.com>
Cc: Guillaume Lelarge <guillaume@lelarge.info>,
 Daniel Gustafsson <daniel@yesql.se>, Ibrar Ahmed <ibrar.ahmad@gmail.com>,
 "Gregory Stark (as CFM)" <stark.cfm@gmail.com>, Tom Lane
 <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>,
 Peter Geoghegan <pg@bowt.ie>, vignesh C <vignesh21@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-hackers@lists.postgresql.org
References: <CA+TgmoZv8DvmeQHQAX+rkn68id2N+DoUhFZB36d-_w1tyAeqeA@mail.gmail.com>
 <2954811.1667711530@sss.pgh.pa.us>
 <CALtqXTfM3GJbFsB63ajfHcYvFhUA0ySX4f4MVS3Dau2vopU+Rw@mail.gmail.com>
 <CAM-w4HNLRO8QJvF3x13wjjdkJHobORxaKrcuyF1cKaTiXuvDbA@mail.gmail.com>
 <CALtqXTcZE6YW=m9ZmNU6sRJ+RTC15NFc__foP1S2KaWvGOf6JA@mail.gmail.com>
 <DBBD7E90-677A-4BF3-A5DD-B1A5D12F800C@yesql.se>
 <A141D263-CA95-4873-BCC7-2573C908DAED@yesql.se>
 <c54a600c-31f8-4db6-b049-e643ae812d38@tantorlabs.com>
 <CAECtzeVpocPwcQ0ccrXsmcvH_8YkJBdUfsJCzb8NKrEodmCg_w@mail.gmail.com>
 <55695d15-c738-4401-8883-58bc855c9b03@tantorlabs.com>
 <58bb3a5b-c92a-408c-b02a-f47aedc1c0e2@tantorlabs.com>
 <CAFY6G8f6Sdu9cvfF2NYWWZMHD0ecTCPuNT7VktPH5NwxY7hcog@mail.gmail.com>
 <70bc1af0-6910-4f71-84ba-fa02095fe9ba@tantorlabs.com>
 <CAFY6G8dgp494Xi2+VUUyM2CvuSQ+3nnzWi44gURWbCHjEWW89g@mail.gmail.com>
 <fc28108a-8e7e-4f7d-b8f4-0bcc1581650d@tantorlabs.com>
 <CAFY6G8cyeQp0Otn3UAA=R6GDufgn_cri+9YNO3ufDRtdSSynDA@mail.gmail.com>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <CAFY6G8cyeQp0Otn3UAA=R6GDufgn_cri+9YNO3ufDRtdSSynDA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/73559119-65b0-4989-b69c-53c81c65de79%40tantorlabs.com>
Precedence: bulk


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


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


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


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


>
> -    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.
>
> * I think that it would be good to mention what a 'row' value in
> decimal means. For
>   example, if its says "0.1 rows" the user should assume that typically 0 rows
>   will be returned but sometimes it can return 1 or more.
>
> * There are more spaces than necessary before "If a subplan node ..."
>
> * Maybe wrap 'rows' with <literal> </literal>?
>

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


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

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