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(dot)evdokimov(at)tantorlabs(dot)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.