I really appreciate your fast and very complete answer. If a table has a foreign key on 2 fields, should I also create an index composed of such fields?
For example: ----------------------- Table Sources ----------------------- 1. src_id 2. src_date 3. Other fields . . . Here, the "primary key" is "src_id + src_date". One "src_id" can exist for several "src_date". ----------------------- Table Lists ----------------------- 1. lst_id 2. lst_source (points to src_id) 3. lst_date 4. Other fields . . . Here, the "foreign key" is "lst_source + lst_date". Regards, Jorge Maldonado On Fri, Oct 4, 2013 at 5:09 PM, David Johnston <pol...@yahoo.com> wrote: > 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. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
