Re: explain analyze rows=%.0f

On 31.03.2025 23:59, Ilia Evdokimov wrote:
>
> We definitely shouldn’t remove the row counts < 1 check, since there
> are many places in the planner where we divide by rows. This mechanism
> was added specifically to prevent division by zero. Also, allowing
> rows estimates below 1 can sometimes make the planner overly
> optimistic, leading it to prefer cheaper-looking plans that may not
> perform well in practice. For example, choosing a Nested Loop instead
> of a more appropriate Hash Join.
>
> Allowing fractional rows > 1 might help improve planner accuracy in
> some cases, but this needs further study to fully understand the impact.
>

I've been investigating whether it's worth removing rounding in row
estimates - and I believe it is.

[ v1-0001-Always-use-two-fractional-digits-for-estimated-rows_SRC.patch ]

Currently, we round most row estimates using rint() inside
clamp_row_est(). However, this function is also used for rounding tuples
and page counts. These should remain integral, but row estimates can and
should remain fractional for better precision. To address this, I
introduced a new function clamp_tuple_est() which retains the existing
rounding behavior (via rint()), while clamp_tuple_est() no longer
rounds. I use clamp_tuple_est() only for row estimates and
clamp_tuple_est() for tuples and pages elsewhere.

After removing rounding, two small issues needed fixing. First, there
was a check rows > 1 in the cost estimation for Nested Loop joins, which
is no longer reliable for values like 1.3. I updated it to rows >= 2 to
retain the original behavior. This can be refined further, but, in my
opinion, it's a practical compromise. Second, there is still a call to
rint() in cost of mergejoin which likely should be removed too - though
I haven’t included that here yet.

Also, if we're no longer rounding estimates, EXPLAIN should display them
with two decimal digits, just like it already does for actual rows.

[ v1-0002-Always-use-two-fractional-digits-for-estimated-rows_TESTS.patch ]

So, what does this change improve? Here are some of the observed plan
improvements:

* Previously, a Parallel Aggregate was chosen. With slightly more
accurate estimation, the planner switches to FinalizeAggregate,
which can be more efficient in distributed plans.
* In certain nested joins with constant subqueries, the planner
previously inserted an unnecessary Materialize. With improved
estimates, it now skips that step, reducing memory usage and latency.
* When the estimated number of iterations becomes non-integer but
still justifies caching, the planner adds Memoize instead of
re-running a function like generate_series(). This can speed up
execution significantly.
* In one case involving partitioned tables and filter conditions like
t1.b = 0, the planner now chooses an index-based nested loop join
instead of a hash join. This results in a more efficient plan with
fewer memory and CPU costs.

I know this patch still needs documentation updates to describe the new
estimation display and behaviors. But before that, I’d like to gather
feedback: does community agree that more precise estimates and
fractional values are better than always rounding?

If anyone would like to see the EXPLAIN ANALYZE VERBOSE output of
changes in regression tests, I’d be happy to share them.

All feedback and suggestions welcome!

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