Re: Advice on defining indexes - Mailing list 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.