Message-ID: <7ab512ac-eed1-4fc8-9c5f-b42c0b0d313e@tantorlabs.com>
Date: Wed, 19 Feb 2025 17:01:06 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: explain analyze rows=%.0f
To: Andrei Lepikhov <lepihov@gmail.com>, Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>,
 Matheus Alcantara <matheusssilv97@gmail.com>,
 Guillaume Lelarge <guillaume@lelarge.info>,
 Daniel Gustafsson <daniel@yesql.se>, Ibrar Ahmed <ibrar.ahmad@gmail.com>,
 "Gregory Stark (as CFM)" <stark.cfm@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>,
 Alena Rybakina <a.rybakina@postgrespro.ru>
References: <CA+TgmoZv8DvmeQHQAX+rkn68id2N+DoUhFZB36d-_w1tyAeqeA@mail.gmail.com>
 <70bc1af0-6910-4f71-84ba-fa02095fe9ba@tantorlabs.com>
 <CAFY6G8dgp494Xi2+VUUyM2CvuSQ+3nnzWi44gURWbCHjEWW89g@mail.gmail.com>
 <fc28108a-8e7e-4f7d-b8f4-0bcc1581650d@tantorlabs.com>
 <CA+Tgmobgkh5CW=4Xpejw9nR6Onv8SZh0nnr+=HmNAFyyHZb0eg@mail.gmail.com>
 <3212947.1739306803@sss.pgh.pa.us>
 <eb73d4e1-4235-41b5-90a8-b0196550f62b@gmail.com>
 <CA+TgmobT9gPQLtw143oGnKVNtaOrTDOy+ZbLfe18MVZ9POjaWg@mail.gmail.com>
 <3365160.1739385615@sss.pgh.pa.us>
 <0e4c813a-18eb-41e2-8edb-89b78d43c82d@gmail.com>
 <CA+TgmoYzawdq40C5cN==LRPBvQChZ7t5XxH6wv+zMxnyM7LjBQ@mail.gmail.com>
 <e434311c-0f84-4c2b-a10d-ecb67f1ec19d@tantorlabs.com>
 <CA+TgmobfeePej0uNfbLy0v6jUrB3D9m1Kh+QQ40_-A9KQjkqUw@mail.gmail.com>
 <f428e80d-8607-4a9d-950c-be9600f24904@gmail.com>
 <01ec86c1-e04b-4cbe-b641-22cae8b87604@tantorlabs.com>
 <CA+TgmobVWYnYDj1HZ--iqeKx+n7zTgtYCar+YfZ0V6wiX-t0Mg@mail.gmail.com>
 <73e2440e-7d8b-4860-974d-931a93326d95@gmail.com>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <73e2440e-7d8b-4860-974d-931a93326d95@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7ab512ac-eed1-4fc8-9c5f-b42c0b0d313e%40tantorlabs.com>
Precedence: bulk


On 18.02.2025 23:55, Andrei Lepikhov wrote:
> On 17/2/2025 15:19, Robert Haas wrote:
>> On Mon, Feb 17, 2025 at 3:08 AM Ilia Evdokimov
>> if (nloops > 1)
>>
>> Instead of:
>>
>> if (nloops > 1 && rows_is_fractonal)
>>
>> I don't think it's really safe to just cast a double back to int64. In
>> practice, the number of tuples should never be large enough to
>> overflow int64, but if it did, this result would be nonsense. Also, if
>> the double ever lost precision, the result would be nonsense. If we
>> want to have an exact count of tuples, we ought to change ntuples and
>> ntuples2 to be uint64. But I don't think we should do that in this
>> patch, because that adds a whole bunch of new problems to worry about
>> and might cause us to get nothing committed. Instead, I think we
>> should just always show two decimal digits if there's more than one
>> loop.
>>
>> That's simpler than what the patch currently does and avoids this
>> problem. Perhaps it's objectionable for some other reason, but if so,
>> can somebody please spell out what that reason is so we can talk about
>> it?
> I can understand two decimal places. You might be concerned about 
> potential issues with some codes that parse PostgreSQL explains.
> However, I believe it would be beneficial to display fractional parts 
> only when iterations yield different numbers of tuples. Given that I 
> often work with enormous explains, I think this approach would enhance 
> the readability and comprehension of the output. Frequently, I may see 
> only part of the EXPLAIN on the screen. A floating-point row number 
> format may immediately give an idea about parameterisation (or another 
> reason for the subtree's variability) and trace it down to the source.
>

The idea of indicating to the user that different iterations produced 
varying numbers of rows is quite reasonable. Most likely, this would 
require adding a new boolean field to the Instrumentation structure, 
which would track this information by comparing the rows value from the 
current and previous iterations.

However, there is a major issue: this case would be quite difficult to 
document clearly. Even with an example and explanatory text, users may 
still be confused about why rows=100 means the same number of rows on 
all iterations, while rows=100.00 indicates variation. Even if we 
describe this in the documentation, a user seeing rows=100.00 will most 
likely assume it represents an average of 100 rows per iteration and may 
still not realize that the actual number of rows varied.

If we want to convey this information more clearly, we should consider a 
more explicit approach. For example, instead of using a fractional 
value, we could display the minimum and maximum row counts observed 
during execution (e.g.,rows=10..20, formatting details could be 
discussed). However, in my opinion, this discussion is beyond the scope 
of this thread.

Any thoughts?

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