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
Message-ID: <200511241216.01384.leif@solumslekt.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: leif@solumslekt.org
Subject: Re: Index wonder
In-Reply-To: <200511241216.01384.leif@solumslekt.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox