agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Advice on defining indexes
Date: Fri, 4 Oct 2013 15:09:03 -0700 (PDT)
Message-ID: <1380924543966-5773424.post@n5.nabble.com> (raw)
In-Reply-To: <CAAY=A7-0zDKj6diOa7i-jX+biriJ9z-XFjjw-wM6jLzSKQ3Y9A@mail.gmail.com>
References: <CAAY=A7-0zDKj6diOa7i-jX+biriJ9z-XFjjw-wM6jLzSKQ3Y9A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (4+ messages) latest in thread
Message-ID: <1380924543966-5773424.post@n5.nabble.com>
Permalink: ../1380924543966-5773424.post@n5.nabble.com/
Also on: postgresql.org/message-id/1380924543966-5773424.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: Advice on defining indexes
In-Reply-To: <1380924543966-5773424.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox