Re: Indexes - Mailing list pgsql-general

From Christopher Browne
Subject Re: Indexes
Date
Msg-id m3wtgigzjg.fsf@mobile.int.cbbrowne.com
Whole thread Raw
In response to Indexes  ("Silas Justiniano" <silasju@gmail.com>)
List pgsql-general
> 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/>

pgsql-general by date:

Previous
From: "Silas Justiniano"
Date:
Subject: Indexes
Next
From: Michael Glaesemann
Date:
Subject: Re: Indexes