Re: [HACKERS] What does explain show ?

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>>>> select a.pkey, b.key2 from a, b
>>>> where b.key1 = 1369
>>>> and     a.pkey = b.key1;
>>>> 
>>>> NOTICE:  QUERY PLAN:
>>>> 
>>>> Nested Loop  (cost=6.19 rows=3 width=10)
>>>>   -> Index Scan using b_pkey on b on b  (cost=2.09 rows=2 width=6)
>>>>   -> Index Scan using a_pkey on a on a  (cost=2.05 rows=15905 width=4)

> Hmmm,I couldn't understand where does "rows=15905" come from.
> Shouldn't "rows" of InnerPlan be 1 ?

No, because that number is formed by considering just the available
restriction clauses on table A, and there aren't any --- so the system
uses the whole size of A as the rows count.  The fact that we are
joining against another table should be taken into account at the
next level up, ie the nested loop.

Actually the number that looks fishy to me for the innerplan is the cost
--- if the system thinks it will be visiting all 15905 rows each time,
it should be estimating a cost of more than 2.05 to do it.

> Is the caluculation "rows of Nested loop = rows of OuterPlan * rows of
> InnerPlan" wrong ?

Careful --- rows produced and cost are quite different things.  The
cost estimate for a nestloop is "cost of outerplan + rows of outerplan *
cost of innerplan", but we don't necessarily expect to get as many rows
out as the product of the row counts.  Typically, it'd be lower due to
join selectivity.  Above you see only 3 rows out, which is not too bad
a guess, certainly better than 2*15905 would be.

You raise a good point though.  That cost estimate is reasonable if
the inner plan is a sequential scan, since then the system will actually
have to visit each inner tuple on each iteration.  But if the inner plan
is an index scan then the outer tuple's key value could be used as an
index constraint, reducing the number of tuples visited by a lot.
I am not sure whether the executor is smart enough to do that --- there
are comments in nodeNestloop suggesting that it is, but I haven't traced
through it for sure.  I am fairly sure that the optimizer isn't figuring
the costs correctly, if that is how it's done :-(
        regards, tom lane