Re: Understanding EXPLAIN ANALYZE estimates when loops != 1

On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk
<philip(at)americanefficient(dot)com> wrote:
> I could use some help interpreting EXPLAIN ANALYZE output.
>
> -> Index Scan using ix_foo on t1 (cost=0.69..68.57 rows=3283 width=105) (actual time=0.006..0.918 rows=3760 loops=94)
>
> The actual rows returned by this plan node ~= 3760 * 94 = 353,440.

Yes. It's total rows / loops rounded to the nearest integer number.

> Did postgres expect (estimate) 3283 rows from this join, or 3283 * 94 = 308,602?

Yes, that's the case at least when the node is not a Parallel node.
If this index scan was part of a parameterized nested loop, then
you'll see the estimate of the number of expected loops from the outer
side of the join.

> Same question for this node.
>
> -> Parallel Index Scan using pk_xyz on xyz (cost=0.29..2354.67 rows=54285 width=25) (actual time=0.049..6.326 rows=14864 loops=5)
>
> Actual rows ~= 14864 * 5 = 74,320, estimate = 54285 or 54285 * 5?

So parallel plans are a bit more complex. The row estimates are the
total estimated rows / the amount of workers we expect to do useful
work. You might expect the divisor there to be an integer number
since you can't really have 0.5 workers. However, it's more complex
than that since the leader has other tasks to take care of such as
pulling tuples from workers, it's not dedicated to helping out.

If you're into reading C code, then there's more information in
https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/costsize.c#L5699
, if you hunt around for usages of that function then you'll see the
estimated row counts are divided by the return value of that function.

David