Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1thb7P-004SMP-8q for pgsql-hackers@arkaria.postgresql.org; Mon, 10 Feb 2025 21:15:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1thb7M-004chr-2l for pgsql-hackers@arkaria.postgresql.org; Mon, 10 Feb 2025 21:15:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1thb7L-004chj-HZ for pgsql-hackers@lists.postgresql.org; Mon, 10 Feb 2025 21:14:59 +0000 Received: from forward501a.mail.yandex.net ([2a02:6b8:c0e:500:1:45:d181:d501]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1thb7H-00064h-2s for pgsql-hackers@lists.postgresql.org; Mon, 10 Feb 2025 21:14:58 +0000 Received: from mail-nwsmtp-smtp-production-main-24.iva.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-24.iva.yp-c.yandex.net [IPv6:2a02:6b8:c0c:e88:0:640:d96b:0]) by forward501a.mail.yandex.net (Yandex) with ESMTPS id EA41E617E3; Tue, 11 Feb 2025 00:14:51 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-24.iva.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id mE09H35Oq4Y0-yIAOgSFk; Tue, 11 Feb 2025 00:14:51 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tantorlabs.com; s=mail; t=1739222091; bh=gXCu2au0PJYJ2L49YX05al9xVQIArg4sGTYsLp/RM7o=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=sEuftVYf9yGdQ2lyxFpffqhRxHHzgRw/zRrl9ED0ROQWmrMMskgySopc3a1nmFboW 0R4ixDfDQ00coRT7Tn5LiBtaHKfcrhLwx/0iOvPBN3dQ3O5OGfZvXZ6/pPoRPckHrD btPnBSijb0uHEcwcNOnU6nop+bkv/YkCJJf93GdU= Authentication-Results: mail-nwsmtp-smtp-production-main-24.iva.yp-c.yandex.net; dkim=pass header.i=@tantorlabs.com Content-Type: multipart/alternative; boundary="------------nSWHgS6AsC8yHOBjUggKJmqJ" Message-ID: <6acda85e-7af0-49fb-a4b1-13cd9f584f99@tantorlabs.com> Date: Tue, 11 Feb 2025 00:14:47 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: explain analyze rows=%.0f To: Matheus Alcantara Cc: Guillaume Lelarge , Daniel Gustafsson , Ibrar Ahmed , "Gregory Stark (as CFM)" , Tom Lane , Robert Haas , Amit Kapila , Justin Pryzby , Peter Geoghegan , vignesh C , "David G. Johnston" , pgsql-hackers@lists.postgresql.org References: <55695d15-c738-4401-8883-58bc855c9b03@tantorlabs.com> <58bb3a5b-c92a-408c-b02a-f47aedc1c0e2@tantorlabs.com> <70bc1af0-6910-4f71-84ba-fa02095fe9ba@tantorlabs.com> <73559119-65b0-4989-b69c-53c81c65de79@tantorlabs.com> Content-Language: en-US From: Ilia Evdokimov In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------nSWHgS6AsC8yHOBjUggKJmqJ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10.02.2025 23:43, Matheus Alcantara wrote: >>>> 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? >> > Yeah, I agree with you about the long digits. My question is more about why do > we need the planstate->instrument->ntuples < nloops check? I tried to remove > this check and I got a lot of EXPLAIN output that shows 'rows' values with .00, > so I'm just trying to understand the reason. From what I've understood about > this thread is that just avoiding .00 decimals of 'rows' values that could be > just integers would be enough, is that right or I'm missing something here? I'm > just worried if we could have a scenario where nloops > 1 && > planstate->instrument->ntuples < nloops which would make the 'rows' not be > formatted correctly. Sorry for missing your question earlier. If you notice in the code above, the variable(average) 'rows' is defined as: double rows = planstate->instrument->ntuples / nloops; This represents the total rows divided by the number of loops. The condition means that variable 'rows' will always  between zero and one. Therefore, the average rows under such conditions cannot be greater than or even equal to one. I wrote this condition specifically to avoid the verbose expression 'rows > 0 && rows < 1'. However, since this might not be obvious to everyone, perhaps it'd be better to write is using 'rows' directly or add a comment explaining this logic. >>> - executing the index scans on tenk2. >>> + executing the index scans on tenk2. If a subplan node >>> + is executed multiple times and the average number of rows is less than one, >>> + the rows and loops 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 ? >>> >> 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? >> > I'm just not sure about the "small values"; the 'rows' in decimal will only > happen with small values? What would be a "small value" in this context? My main > point here is more that I think that it would be good to mention *why* the > 'rows' can be decimal, not just describe that it could be decimal. > As for 'small values', it means that the average rows is between zero and one, to avoid rounding errors and misunderstanding. I think this would be ideal. -- Best regards, Ilia Evdokimov, Tantor Labs LLC. --------------nSWHgS6AsC8yHOBjUggKJmqJ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 10.02.2025 23:43, Matheus Alcantara wrote:
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?


Yeah, I agree with you about the long digits. My question is more about why do
we need the planstate->instrument->ntuples < nloops check? I tried to remove
this check and I got a lot of EXPLAIN output that shows 'rows' values with .00,
so I'm just trying to understand the reason. From what I've understood about
this thread is that just avoiding .00 decimals of 'rows' values that could be
just integers would be enough, is that right or I'm missing something here? I'm
just worried if we could have a scenario where nloops > 1 &&
planstate->instrument->ntuples < nloops which would make the 'rows' not be
formatted correctly.


Sorry for missing your question earlier. If you notice in the code above, the variable(average) 'rows' is defined as:

double rows = planstate->instrument->ntuples / nloops;

This represents the total rows divided by the number of loops. The condition means that variable 'rows' will always  between zero and one. Therefore, the average rows under such conditions cannot be greater than or even equal to one. I wrote this condition specifically to avoid the verbose expression 'rows > 0 && rows < 1'. However, since this might not be obvious to everyone, perhaps it'd be better to write is using 'rows' directly or add a comment explaining this logic.



      

        

        

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



      

      
I'm just not sure about the "small values"; the 'rows' in decimal will only
happen with small values? What would be a "small value" in this context? My main
point here is more that I think that it would be good to mention *why* the
'rows' can be decimal, not just describe that it could be decimal.



    

    


    

    
As for 'small values', it means that the average rows is between
      zero and one, to avoid rounding errors and misunderstanding. I
      think this would be ideal.

    
--

      Best regards,

      Ilia Evdokimov,

      Tantor Labs LLC.

    

  


--------------nSWHgS6AsC8yHOBjUggKJmqJ--