Re: explain analyze rows=%.0f

On 15.02.2025 11:46, Andrei Lepikhov wrote:
> On 13/2/2025 21:42, Robert Haas wrote:
>> On Thu, Feb 13, 2025 at 4:05 AM Ilia Evdokimov
>> <ilya.evdokimov@tantorlabs.com> wrote:
>>> 1. Documentation 
>>> (v9-0001-Clarify-display-of-rows-as-decimal-fractions-DOC.patch)
>>>
>>> One thing that bothers me is that the documentation explains how to 
>>> compute total time, but it does not clarify how to compute total 
>>> rows. Maybe this was obvious to others before, but now that we are 
>>> displaying rows as a fraction, we should explicitly document how to 
>>> interpret it alongside total time.
>>>
>>> I believe it would be helpful to show a non-integer rows value in an 
>>> example query. However, to achieve this, we need the index scan 
>>> results to vary across iterations. One way to accomplish this is by 
>>> using the condition t1.unique2 > t2.unique2. Additionally, I suggest 
>>> making loops a round number (e.g., 100) for better readability, 
>>> which can be achieved using t1.thousand < 10. The final query would 
>>> look like this:
>>>
>>> EXPLAIN ANALYZE SELECT *
>>> FROM tenk1 t1, tenk2 t2
>>> WHERE t1.thousand < 10 AND t1.unique2 > t2.unique2;
>>>
>>> I believe this is an ideal example for the documentation because it 
>>> not only demonstrates fractional rows, but also keeps the execution 
>>> plan nearly unchanged. While the estimated and actual average row 
>>> counts become slightly rounded, I don't see another way to ensure 
>>> different results for each index scan.
>>
>> Anyone else have an opinion on this? I see Ilia's point, but a
>> non-equality join is probably an atypical case.
> For me, it is ok: it demonstrates the feature and may legally happen. 
> But this patch should be the second one, isn't it?


I didn't want to introduce a new table instead of tenk1 and tenk2, as 
the entire page already uses them. Moreover, if we use = in the join 
condition, the number of rows will always be the same. So, I couldn't 
find a better option than t1.unique > t2.unique.

Regarding the order of the patches, I made this one first because, as 
Tom mentioned earlier, we need to finalize the documentation first. 
However, in order to check tests in CirrusCI, it should be really the 
second.



>>
>>> 2. Code and tests 
>>> (v9-0002-Clarify-display-of-rows-as-decimal-fractions.patch)
>>>
>>> I left the code and tests unchanged since we agreed on a fixed 
>>> format of two decimal places.
>>
>> This still has the HAS_DECIMAL() thing to which I objected.
> I don't understand why using math.h and the floor() routine is 
> necessary. I personally prefer x%y!=0 expression.
>


You're right—floor shouldn't be used since it behaves differently across 
platforms, as Tom also pointed out earlier. I like the idea of using %, 
but since the compiler doesn't allow this operation with double, we need 
to cast it to int64. I checked how nloops and ntuples are modified - 
they are only incremented by 1. So that the casting might be safe. If I 
missed anything or if there's a reason why this casting wouldn't be 
safe, please let me know.

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