Re: Understanding EXPLAIN ANALYZE estimates when loops != 1

On Fri, 21 Aug 2020 at 03:21, Philip Semanchuk
<philip(at)americanefficient(dot)com> wrote:
>
>
>
> > On Aug 19, 2020, at 6:24 PM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> >
> > On Thu, 20 Aug 2020 at 09:55, Philip Semanchuk
> > <philip(at)americanefficient(dot)com> wrote:
> >> -> 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.
>
> Sounds like it help to set max_parallel_workers = 1 before running EXPLAIN ANALYZE in order to simplify the numbers, yes? Or is there a possibility that doing so would send the planner down an entirely different path?

Since the costs are also divided (see costsize.c) and the path costs
are the basis on which paths the planner will choose to use, you're
likely to see the plan changing. max_parallel_workers set to 1
wouldn't have been very helpful anyway since that's the leader process
+ 1 parallel worker resulting in the divisor of 1.7.

If you need to, you can just reverse engineer the costs from assuming
what get_parallel_divisor() will have returned. You can see it expects
each worker to take up 30% of its time. leader + 1 worker = 1.7,
leader + 2 workers = 2.4, leader + 3 workers = 3.1, leader + 4 workers
= 4. You'll know the number of workers from "Workers Planned" in the
EXPLAIN output. You'd need to do something else if you happen to run
with parallel_leader_participation = off.

David