PostgreSQL Discussion

Author

Indexes

Silas Justiniano

2006-01-29, 1:23 pm

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?

Thank you very much. Bye!

Christopher Browne

2006-01-30, 3:23 am

> 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/>

Michael Glaesemann

2006-01-30, 3:23 am

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] to your original message unclear? If you have any
further questions, please be more specific.

[1] http://archives.postgresql.org/pgsq...01/msg00939.php

Michael Glaesemann
grzm myrealbox com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Alban Hertroys

2006-01-30, 7:23 am

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//

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Leif B. Kristensen

2006-01-30, 7:23 am

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,chi
ld_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,chi
ld_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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Greg Stark

2006-01-30, 9:25 am

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links