From: | "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org> |
---|---|
To: | "PostgreSQL pg-sql list" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Index wonder |
Date: | 2005-11-24 11:16:00 |
Message-ID: | 200511241216.01384.leif@solumslekt.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I just wanted to share my revelation on how an index can do wonders for
a query:
pgslekt=> explain select child_fk, get_coparent(570,child_fk),
get_pbdate(child_fk) as pbd from relations where parent_fk = 570 order
by pbd;
QUERY PLAN
-----------------------------------------------------------------
Sort (cost=378.26..378.27 rows=5 width=4)
Sort Key: get_pbdate(child_fk)
-> Seq Scan on relations (cost=0.00..378.20 rows=5 width=4)
Filter: (parent_fk = 570)
(4 rows)
pgslekt=> create index parent_key on relations(parent_fk);
CREATE INDEX
pgslekt=> create index child_key on relations(child_fk);
CREATE INDEX
pgslekt=> explain select child_fk, get_coparent(570,child_fk),
get_pbdate(child_fk) as pbd from relations where parent_fk = 570 order
by pbd;
QUERY PLAN
-----------------------------------------------------------------
Sort (cost=13.81..13.83 rows=5 width=4)
Sort Key: get_pbdate(child_fk)
-> Index Scan using parent_key on relations (cost=0.00..13.76
rows=5 width=4)
Index Cond: (parent_fk = 570)
(4 rows)
As a consequence, the time for generating a page listing the descendants
and their spouses for a singularly prodigius and well-researched family
- in total about 1100 persons - went down from 30 seconds to 3.
I am thoroughly amazed.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2005-11-24 13:57:03 | Re: |
Previous Message | Ken Winter | 2005-11-24 04:23:05 |