Why is that so slow? - Mailing list pgsql-hackers

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 indexnewcode char(7),    -- has an btree indexpid int2,
  -- has an btree indexkana_city text,        -- has an btree indexkana_town text,        -- has an btree indexcity
text,       -- has an btree indextown text        -- has an btree index
 
);

(has 119479 records)

create table prefecture (pid int2,        -- has an btree indexpref 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) ->
IndexScan 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
usingpidindex 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