Re: Indexes

> Silas Justiniano
> Jan 17, 5:53 pm show options
> Newsgroups: pgsql.general
> From: "Silas Justiniano" <sila(dot)(dot)(dot)(at)gmail(dot)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(at)~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/>