Thread: 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? Thank you very much. Bye!
> 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/>
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/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