Indexes

Silas Justiniano
Jan 17, 5:53 pm show options
Newsgroups: pgsql.general
From: "Silas Justiniano" <sila...@gmail.com> - Find messages by this
author
Date: 17 Jan 2006 11:53:37 -0800
Local: Tues, Jan 17 2006 5:53 pm
Subject: Indexes
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

hi.

I've already asked that in #postgresql at freenode, but I didn't
understand well.

I have two tables:

Books
- book_id
- name

Authors
- author_id
- name

One book can have many authors and one author can have many books. To
make that possible, I need a third table:

Intermediate
- book_id
- author_id

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);

too?

It is fairly normal for intermediate tables of this sort to just need
the first of the four indexes that you indicate, e.g.
CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);

The other two indexes would rule out having either:

a) An author that writes more than one book, or
b) A book with multiple co-authors.

That makes them both poor ideas, I'd think...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://linuxfinances.info/info/
Rules of the Evil Overlord #80. "If my weakest troops fail to
eliminate a hero, I will send out my best troops instead of wasting
time with progressively stronger ones as he gets closer and closer to
my fortress." <http://www.eviloverlord.com/&gt;

On Jan 30, 2006, at 3:03 , Silas Justiniano wrote:

My question is about the indexes in Intermediate table. Is the
following index:

Was my response[1]http://archives.postgresql.org/pgsql-general/2006-01/msg00939.php to your original message unclear? If you have any
further questions, please be more specific.

[1]: http://archives.postgresql.org/pgsql-general/2006-01/msg00939.php

Michael Glaesemann
grzm myrealbox com

Silas Justiniano wrote:

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);

If you'd use plain indexes for the last two (without the UNIQUE part),
queries that would need to lookup only 1 of the columns in this table
could be faster (depending on which version of postgres you run - I
don't think it'll make any difference in 8 and up).
The same thing goes for all tables that have a foreign key to another
table; an index on those columns may help.

Mind you, this is more about optimization, not so much about database
design. It depends on your queries whether you're going to have any
benefit from this.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

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

"Silas Justiniano" <silasju@gmail.com> writes:

I normally create these two indexes:

CREATE UNIQUE INDEX foo ON Intermediate(book_id, author_id);
CREATE INDEX baz ON Intermediate(author_id);

Note that the second one isn't unique.

Or you can go the other way (<author_id,book_id> and <book_id>). The only
difference would be on queries like "WHERE author_id = ? and book_id BETWEEN ?
AND ?". If you never do range scans then it will hardly matter which way you
go. I tend to do it the way above just so it matches the column order in the
table.

--
greg