From: | "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Wrong plan for simple join with index on FK |
Date: | 2006-05-16 09:52:05 |
Message-ID: | BAY20-F3A17467DA67DDE336C842F9A00@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-05-16 10:00:05 | Re: Creating a Pseudocolumn |
Previous Message | Gurjeet Singh | 2006-05-16 09:48:19 | Re: Creating a Pseudocolumn |