Hi,
while eye-balling some explain plans for parallel queries, I got a bit
confused by the row count estimates. I wonder whether I'm alone.
Consider for example a simple seq scan query, which in non-parallel
explain looks like this:
QUERY PLAN
--------------------------------------------------------------------- Seq Scan on tables t (cost=0.00..16347.60
rows=317160width=356) (actual rows=317160 loops=1) Planning time: 0.173 ms Execution time: 47.707
ms
(3 rows)
but a parallel plan looks like this:
QUERY PLAN
--------------------------------------------------------------------- Gather (cost=0.00..14199.10 rows=317160
width=356) (actual rows=317160 loops=1) Workers Planned: 3 Workers Launched: 3 -> Parallel Seq Scan on
tablest (cost=... rows=102310 width=356) (actual rows=79290 loops=4) Planning
time:0.209 ms Execution time: 150.812 ms
(6 rows)
Now, for actual rows we can simply do 79290 * 4 = 317160, and we get the
correct number of rows produced by the plan (i.e. matching the
non-parallel query).
But for the estimate, it doesn't work like that:
102310 * 4 = 409240
which is ~30% above the actual estimate. It's clear why this is
happening - when computing plan_rows, we don't count the leader as a
full worker, but use this:
leader_contribution = 1.0 - (0.3 * path->parallel_workers);
so with 3 workers, the leader is only worth ~0.1 of a worker:
102310 * 3.1 = 317161
It's fairly easy to spot this here, because the Gather node is right
above the Parallel Seq Scan, and the values in the Gather accurate. But
in many plans the Gather will not be immediately above the node (e.g.
there may be parallel aggregate in between).
Of course, the fact that we use planned number of workers when computing
plan_rows but actual number of workers for actually produced rows makes
this even more confusing.
BTW is it really a good idea to use nloops to track the number of
workers executing a given node? How will that work if once we get
parallel nested loops and index scans?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
