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)
        Hash Cond: (f2.fk = f1.pk)
        ->  Seq Scan on f2  (cost=0.00..2018.00 rows=140000 width=4)
        ->  Hash  (cost=145.00..145.00 rows=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)
        Merge Cond: (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/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to