RE: [HACKERS] What does explain show ?

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, January 05, 2000 9:31 AM
> 
> Quite some time ago, "Hiroshi Inoue" <Inoue@tpf.co.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@tpf.co.jp