Why is that so slow?

Hi,

I have a 2 tables and in some cases joining them are very slow.

Here are details.

create table postal (
oldcode varchar(5), -- has an btree index
newcode char(7), -- has an btree index
pid int2, -- has an btree index
kana_city text, -- has an btree index
kana_town text, -- has an btree index
city text, -- has an btree index
town text -- has an btree index
);

(has 119479 records)

create table prefecture (
pid int2, -- has an btree index
pref char(8),
kana_pref char(16)
);

(has 47 records)

My question is:

This is fast as I expected.

postal=> explain select * from postal,prefecture where city = 'aaa' and postal.pid = prefecture.pid;
NOTICE: QUERY PLAN:

Nested Loop (cost=4.10 size=1 width=100)
-> Index Scan using cityindex on postal (cost=2.05 size=1 width=74)
-> Index Scan using prefpidindex on prefecture (cost=2.05 size=47 width=26)

But:

postal=> explain select * from postal,prefecture where city ~ '^aaa' and postal.pid = prefecture.pid;
NOTICE: QUERY PLAN:

Nested Loop (cost=98.90 size=1 width=100)
-> Seq Scan on prefecture (cost=2.55 size=47 width=26)
-> Index Scan using pidindex on postal (cost=2.05 size=1 width=74)

This is so slooow. Can anybody explain this? Am I missing something?

Note that 6.4.x and current show same behavior.
---
Tatsuo Ishii