What does explain show ?

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 ?
Is "rows=3" of Nested Loop irrelevant to "rows=15905" ?

It means it thinks it is going to access X rows in that pass, but end
up with 3 joined rows as a result of the nested loop. It is only an
estimate, based on table size and column uniqueness from vacuum analyze.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-----Original Message-----
From: Bruce Momjian [mailto:maillist@candle.pha.pa.us]
Sent: Wednesday, July 14, 1999 11:57 AM
To: Hiroshi Inoue
Cc: pgsql-hackers
Subject: Re: [HACKERS] What does explain show ?

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 ?
Is "rows=3" of Nested Loop irrelevant to "rows=15905" ?

It means it thinks it is going to access X rows in that pass, but end
up with 3 joined rows as a result of the nested loop. It is only an
estimate, based on table size and column uniqueness from vacuum analyze.

Hmmm,I couldn't understand where does "rows=15905" come from.
Shouldn't "rows" of InnerPlan be 1 ?
Is the caluculation "rows of Nested loop = rows of OuterPlan * rows of
InnerPlan"
wrong ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

"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

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.

I see. rows of Join = rows of outerplan * rows of innerplan * "join
selectity".
and "join selectivity" is calcutated by eqjoinsel() etc.

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp