Re: Indexes

On Sunday 29 January 2006 19:03, Silas Justiniano wrote:
>My question is about the indexes in Intermediate table. Is the
>following index:
>
>CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);
>
>enough for every query I want to perform? Or should I need
>
>CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);
>CREATE UNIQUE INDEX bar ON Intermediate(book_id);
>CREATE UNIQUE INDEX baz ON Intermediate(author_id);

Running an EXPLAIN SELECT on your actual queries gives a very good
indication of whether such an index could be useful. I had a similar
experience with the 'relations' table of my genealogy database; that is
a table that stores child and parent id's:

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.

So, anywhere that the query optimizer must revert to a sequential scan,
performance may be greatly enhanced by applying an index or two.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE