Advice for index design

From: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Advice for index design
Date: 2013-04-10 22:30:47
Message-ID: CAAY=A78-djZ+T5TVmzN817tS31_E_RseWJvzJpAkDajprJDffg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table of artists with fields like the ones below:

* Name
* Birthday
* Sex (male/female)

Our application offers a catalog of artists where a user can select a range
of birthdays and/or sex. For example, a user can get an artists catalog for
those male artists who were born between May 1, 1970 and May 1, 1990
ordered by birthday and, within each birthday date, ordered by name. I can
think of defining one index for birthday, one index for name, and one index
for sex. Also, I can think of defining a compound index for birthday +
name. Also there could be a compound index for sex + name. Another option
could be a compound index for birthday + sex + name. There are many
possible combinations. What is a good index design approach? Maybe, setting
simple separate indexes (one for each field) would work fine if I need to
retrieve data in different combinatios, but I am not sure. Maybe compound
indexes is better. I will very much appreciate your advice.

Respectfully,
Jorge Maldonado

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gavin Flower 2013-04-11 00:00:20 Re: Advice for index design
Previous Message ktm@rice.edu 2013-04-10 18:16:56 Re: [SQL] Encrypting PGBouncer to Postgres DB connections