pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
indexing 3+ messages / 2 participants [nested] [flat]
* indexing @ 2003-02-24 20:38 James Cooper <jim@luckydigital.com> 0 siblings, 1 reply; 3+ messages in thread From: James Cooper @ 2003-02-24 20:38 UTC (permalink / raw) To: pgsql-sql Hi all, I've been doing a little reading on indexing in prelude to indexing my db. I have the following to ask: if I had three tables for a many to many relationship say A, B, AND C B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id). is it best to create two non-unique indexes or one unique index on both fields? Please advise Ps I also attempted creating an index on a table i have called person on person_id and ran Explain select person_id from person where person_id < n but saw no results of my created index being used - am i doing something incorrectly Pps When indexing if searching tables is more important than concurrency - which type of index is best? ^ permalink raw reply [nested|flat] 3+ messages in thread
* indexing @ 2003-02-24 22:38 James Cooper <jim@luckydigital.com> 0 siblings, 0 replies; 3+ messages in thread From: James Cooper @ 2003-02-24 22:38 UTC (permalink / raw) To: pgsql-sql Hi all, I've been doing a little reading on indexing in prelude to indexing my db. I have the following to ask: if I had three tables for a many to many relationship say A, B, AND C B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id). is it best to create two non-unique indexes or one unique index on both fields? Please advise Ps I also attempted creating an index on a table i have called person on person_id and ran Explain select person_id from person where person_id < n but saw no results of my created index being used - am i doing something incorrectly Pps When indexing if searching tables is more important than concurrency - which type of index is best? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: indexing @ 2003-02-25 01:16 Josh Berkus <josh@agliodbs.com> parent: James Cooper <jim@luckydigital.com> 0 siblings, 0 replies; 3+ messages in thread From: Josh Berkus @ 2003-02-25 01:16 UTC (permalink / raw) To: James Cooper <jim@luckydigital.com>; pgsql-sql James, > if I had three tables for a many to many relationship say A, B, AND C > B being the lookup. B being a huge 50k rows plus column and made just two forigen keys(b.a_id,b.c_id). > is it best to create two non-unique indexes or one unique index on both fields? That depends on whether all three tables are usually queried toghether. If yes, then a 2-column index is probably better. If not, use single-column indexes. If you want more specific advice, post your table structures. > I also attempted creating an index on a table i have called person on person_id and > ran > Explain > select person_id from person where person_id < n > > but saw no results of my created index being used - am i doing something incorrectly Not a surprise. If your the planner expects person_id < n to return a significant portion of the table, a table scan is faster than an index scan. > Pps > When indexing if searching tables is more important than concurrency - which type of index is best? You want to use a B-tree index for anything other than statistical and/or geometic data. You are unlikely to need any other kind of index. -- -Josh Berkus Aglio Database Solutions San Francisco ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-02-25 01:16 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-02-24 20:38 indexing James Cooper <jim@luckydigital.com> 2003-02-25 01:16 ` Josh Berkus <josh@agliodbs.com> 2003-02-24 22:38 indexing James Cooper <jim@luckydigital.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox