From: "Leif B. Kristensen" <leif@solumslekt.org>
To: "PostgreSQL pg-sql list" <pgsql-sql@postgresql.org>
Subject: Index wonder
Date: Thu, 24 Nov 2005 12:16:00 +0100
User-Agent: KMail/1.8.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200511241216.01384.leif@solumslekt.org>

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