Re: Explaining an EXPLAIN.

Matthew Wakeling <matthew(at)flymine(dot)org> writes:
> If I look at the actual results of the outer-most join, the nested loop,
> then I can take the number rows=344491124 and divide it by loops=361427 to
> get rows=953. Clearly this means that on average each index scan on a3
> returned 953 rows.

Right.

> However, if I apply the same logic to the estimated results, it all falls
> apart. The total estimated number of rows is remarkably accurate, as is
> the estimated number of loops (results from the merge join). However the
> average number of rows expected to be returned from the index scan is only
> 47. I don't know how the planner is getting its accurate final estimate of
> rows=330437962, because it is not from multiplying rows=361427 by rows=47.

No, it isn't. The rowcount estimate for an inner indexscan is derived
based on the index conditions that are assigned to the scan. It's not
used for anything except estimating the cost of that indexscan; in
particular, the size of the join relation was estimated long before we
even started to think about nestloop-with-inner-indexscan plans.
I don't have time to look right now, but I seem to recall there are some
constraints that mean it's often not a very good estimate.

regards, tom lane