Re: Advice on defining indexes

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 <polobo(at)yahoo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>