RE: [HACKERS] What does explain show ?

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, January 05, 2000 9:31 AM
>
> Quite some time ago, "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> wrote:
> > I have a question about "explain" output.
> > Table a has 15905 rows and table b has 25905 rows.
> > For the following query
> > select a.pkey, b.key2 from a, b
> > where b.key1 = 1369
> > and a.pkey = b.key1;
> > "explain" shows
>
> > 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)
>
> > What does "rows=15905" of InnerPlan mean ?
>
> I have finally traced through enough of the optimizer logic that I
> understand where these numbers are coming from. A nestloop with an
> inner index scan is a slightly unusual beast, because the cost of the
> inner scan can often be reduced by using the join conditions as index
> restrictions. For example, if we have "outer.a = inner.b" and the
> inner scan is an indexscan on b, then during the inner scan that's
> done for an outer tuple with a = 42 we'd use "b = 42" as an indexqual.
> This makes the inner scan much cheaper than it would be if we had to
> scan the whole table.
>
> Now the problem is that the "rows=" numbers come from the RelOptInfo
> nodes for each relation, and they are set independently of the context
> that the relation is used in. For any context except an inner
> indexscan, we would indeed have to scan all 15905 rows of a, because
> we have no pure-restriction WHERE clauses that apply to a. So that's
> why rows says 15905. The cost is being estimated correctly for the
> context, though --- an indexscan across 15905 rows would take a lot more
> than 2 disk accesses.
>
> This is just a cosmetic bug since it doesn't affect the planner's cost
> estimate; still, it makes the EXPLAIN output confusing. I think the
> output for a nestloop should probably show the estimated number of rows
> that will be scanned during each pass of the inner indexscan, which
> would be about 1 in the above example. This could be done by saving the
> estimated row count (or just the selectivity) in IndexScan path nodes.
>
> Comments? Does anyone think we should show some other number?
>

I agree with you.
The rows should show some kind of average number of rows,because
the cost of innerplan seems to mean average cost.

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp