Wrong plan for simple join with index on FK

Hello

I test using index on foreign key. I found situation, when planner choose 
worse plan.

create table f1(pk serial primary key);
create table f2(fk integer references f1(pk));

insert into f1 select a from generate_series(1,10000) a;
insert into f2 select (random()*9999)::int+1 from generate_series(1,140000);
vacuum analyze;
create index xxx on f2(fk);
\timing
postgres=> select count(*) from f1 join f2 on pk=fk;
count
--------
140000
(1 row)
Time: 538,254 ms
drop index xxx;
postgres=> select count(*) from f1 join f2 on pk=fk;
count
--------
140000
(1 row)
Time: 311,580 ms


Plans:


postgres=> explain select count(*) from f1 join f2 on pk=fk;                               QUERY PLAN
--------------------------------------------------------------------------
Aggregate  (cost=7788.00..7788.01 rows=1 width=0)  ->  Hash Join  (cost=170.00..7438.00 rows=140000 width=0)
HashCond: (f2.fk = f1.pk)        ->  Seq Scan on f2  (cost=0.00..2018.00 rows=140000 width=4)        ->  Hash
(cost=145.00..145.00rows=10000 width=4)              ->  Seq Scan on f1  (cost=0.00..145.00 rows=10000 width=4)
 
(6 rows)
postgres=> explain select count(*) from f1 join f2 on pk=fk;                                    QUERY PLAN
------------------------------------------------------------------------------------
Aggregate  (cost=6631.75..6631.76 rows=1 width=0)  ->  Merge Join  (cost=0.00..6281.75 rows=140000 width=0)
MergeCond: (f1.pk = f2.fk)        ->  Index Scan using f1_pkey on f1  (cost=0.00..187.00 rows=10000 
 
width=4)        ->  Index Scan using xxx on f2  (cost=0.00..4319.77 rows=140000 
width=4)
(5 rows)

PostgreSQL 8.1, Linux

Regards
Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. 
http://www.msn.cz/