Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> Something is fishy here. Have you done a "vacuum analyze" since loading
>> the data in these tables?
> Oh, I never thought about that.
Ah. OK, that explains the system's poor choice of plan --- it was
effectively operating on the assumption that these tables were small.
(Note to hackers: maybe a freshly created table should be given dummy
statistics, say having 1000 rows instead of 0 rows? That would help
to prevent the optimizer from making really foolish choices when no
vacuum's been done yet for the table. But I dunno whether we could
invent plausible default values for all the stats...)
> After re-made the index I removed in
> the next letter and did vacuum analyze, I got:
> Hash Join (cost=951.50 size=19 width=100)
> -> Index Scan using cityindex on postal (cost=944.77 size=19 width=74)
> -> Hash (cost=0.00 size=0 width=0)
> -> Seq Scan on prefecture (cost=2.55 size=47 width=26)
> This plan looks good(and actually as fast as the previous
> one). However, the cost estimate for prefecture is again 47?
No, that looks OK in this context: it's proposing to load the whole
prefecture table into an internal hashtable, so it will have to scan
all 47 prefecture rows to do it. The only guesstimating in this plan
is the "size=19" for the index scan, ie, an estimated 19 hits from the
match on city name. That seems fairly reasonable, although of course
it could be badly off depending on your match pattern.
regards, tom lane