Re: plan_rows confusion with parallel queries

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> On 11/02/2016 11:56 PM, Tomas Vondra wrote:
>> On 11/02/2016 09:00 PM, Tom Lane wrote:
>>> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>>>> 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.

>>> I got confused by that a minute ago, so no you're not alone. The problem
>>> is even worse in join cases. For example:
>>> Gather (cost=34332.00..53265.35 rows=100 width=8)
>>> Workers Planned: 2
>>> -> Hash Join (cost=33332.00..52255.35 rows=100 width=8)
>>> Hash Cond: ((pp.f1 = cc.f1) AND (pp.f2 = cc.f2))
>>> -> Append (cost=0.00..8614.96 rows=417996 width=8)
>>> -> Parallel Seq Scan on pp (cost=0.00..8591.67 rows=416667 width=8)
>>> -> Parallel Seq Scan on pp1 (cost=0.00..23.29 rows=1329 width=8)
>>> -> Hash (cost=14425.00..14425.00 rows=1000000 width=8)
>>> -> Seq Scan on cc (cost=0.00..14425.00 rows=1000000 width=8)
>>> There are actually 1000000 rows in pp, and none in pp1. I'm not bothered
>>> particularly by the nonzero estimate for pp1, because I know where that
>>> came from, but I'm not very happy that nowhere here does it look like
>>> it's estimating a million-plus rows going into the join.

> Although - it is estimating 1M rows, but only "per worker" estimates are
> shown, and because there are 2 workers planned it says 1M/2.4 which is
> the 416k. I agree it's a bit unclear, but at least it's consistent with
> how we treat loops (i.e. that the numbers are per loop).

Well, it's not *that* consistent. If we were estimating all the numbers
underneath the Gather as being per-worker numbers, that would make some
amount of sense. But neither the other seqscan, nor the hash on it, nor
the hashjoin's output count are scaled that way. It's very hard to call
the above display anything but flat-out broken.

> But there's more fun with joins - consider for example this simple join:
> ...
> Suddenly we don't show per-worker estimates for the hash join - both the
> Hash Join and the Gather have exactly the same cardinality estimate.

Yeah. That doesn't seem to be quite the same problem as in my example,
but it's about as confused.

Maybe we need to bite the bullet and add a "number of workers" field
to the estimated and actual counts. Not sure how much that helps for
the partial-count-for-the-leader issue, though.

regards, tom lane