Re: Advice on defining indexes
От | David Johnston |
---|---|
Тема | Re: Advice on defining indexes |
Дата | |
Msg-id | 1380924543966-5773424.post@n5.nabble.com обсуждение исходный текст |
Ответ на | Advice on defining indexes (JORGE MALDONADO <jorgemal1960@gmail.com>) |
Ответы |
Re: Advice on defining indexes
(JORGE MALDONADO <jorgemal1960@gmail.com>)
|
Список | pgsql-sql |
JORGE MALDONADO wrote > I have a table with fields that I guess would be a good idea to set as > indexes because users may query it to get results ordered by different > criteria. For example: > > ------------------ > Artists Table > ------------------ > 1. art_id > 2. art_name > 3. art_bday > 4. art_sex > 5. art_country (foreign key, there is a table of countries) > 6. art_type (foreign key, there is a table of types of artists) > 7. art_email > 8. art_comment > 9. art_ bio > > "art_id" is the primary key. > Users query the table to get results ordered by fields (2) to (6). Is it > wise to define such fields as indexes? > > I ask this question because our database has additional tables with the > same characteristics and maybe there would be many indexes. > > With respect, > Jorge Maldonado Some thoughts: Indexes for sorting are less useful than indexes for filtering. I probably would not create an index if it was only intended for sorting. Note that in many situations the number of ordered records will be fairly small so on-the-fly sorting is not going to be that expensive anyway. Indexes decrease insertion/update performance but generally improve selection performance. The relative volume of each is important. Index keys which contain a large number of rows are generally ignored in favor of a table scan. For this reason gender is seldom indexed. You have the option of a partial index if a single key contains a large number of records. Simply index everything but that key. Smaller indexes are better and any searches for the ignored key would end up skipping the index in many cases anyway. Consider create full-text search indexes on the comment/bio column and you can probably also add in the other fields into some form of functional index so that performing a search over that single field will in effect search all of the columns. I'd probably index country and type to make the foreign key lookups faster and then create a functional full-text index on the different text fields. I would then add an index on art_bday and call it done. You can then write a view/function that performs a full-text search against the functional index (or just create an actual column) for most text searches and have separate criteria filters for country/type/birthday. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773424.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
В списке pgsql-sql по дате отправления: