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
