EXPLAIN understanding? (restarted from Censorship)

On Wed, 10 Jun 2009, Richard Huxton wrote:
> Send it to the list again, and cc: me directly if you like. If it doesn't
> show up in the next 20 minutes, I'll try sending it.

Okay, here we go. I have (per Tom's advice) found some acknowledgement
knobs on Majordomo. Here follows my original rejected mail:

Hi. I thought by now I would be fairly good at understanding EXPLAIN
ANALYSE results, but I can't quite figure this one out. Perhaps someone
could help me.

EXPLAIN ANALYSE SELECT *
FROM GeneGoAnnotation a1, GOAnnotation a2, OntologyTermRelations a3
WHERE a1.GoAnnotation = a2.id AND a2.ontologyTermId = a3.OntologyTerm;

QUERY PLAN
---------------------------------------------------------------------------------------
Nested Loop (cost=0.00..673587.67 rows=330437962 width=95)
(actual time=0.056..1924645.797 rows=344491124 loops=1)
-> Merge Join (cost=0.00..28369.58 rows=361427 width=87)
(actual time=0.039..4620.912 rows=361427 loops=1)
Merge Cond: (a1.goannotation = a2.id)
-> Index Scan using genegoannotation__goannotation on
genegoannotation a1
(cost=0.00..9710.32 rows=361427 width=8)
(actual time=0.015..840.547 rows=361427 loops=1)
-> Index Scan using goannotation_pkey on goannotation a2
(cost=0.00..13133.12 rows=403323 width=79)
(actual time=0.014..1427.179 rows=403323 loops=1)
-> Index Scan using ontologytermrelations__ontologyterm on
ontologytermrelations a3
(cost=0.00..1.20 rows=47 width=8)
(actual time=0.022..1.908 rows=953 loops=361427)
Index Cond: (a3.ontologyterm = a2.ontologytermid)
Total runtime: 2524647.064 ms
(8 rows)

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.

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.
That would only give 16987069 rows.

Any ideas/explanations?

Matthew

--
Now, you would have thought these coefficients would be integers, given that
we're working out integer results. Using a fraction would seem really
stupid. Well, I'm quite willing to be stupid here - in fact, I'm going to
use complex numbers. -- Computer Science Lecturer

--
Richard Huxton
Archonet Ltd