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/
