From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Advice for index design
Date: Thu, 11 Apr 2013 12:00:20 +1200
Message-ID: <5165FD14.5010104@archidevsys.co.nz> (raw)
In-Reply-To: <CAAY=A78-djZ+T5TVmzN817tS31_E_RseWJvzJpAkDajprJDffg@mail.gmail.com>
References: <CAAY=A78-djZ+T5TVmzN817tS31_E_RseWJvzJpAkDajprJDffg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 11/04/13 10:30, JORGE MALDONADO wrote:
> 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
>
W.r.t. sex what about those people who:
1. are neither
2. are both
3. not specified
4. don't want to tell you
5. have changed their gender mid career
About 0.5% children are born in the folowing categories:
1. ambiguous genitalia
2. both
3. none
4. genitalia that doesn't match their brain wiring
5. born looking like a female, but change to male at puberty
I once saw an article about an island were about 10% of males were born
looking like a female, but changed to male at puberty. It was so common
and well known that parents simply changed their clothes renamed them,
and started treating them as male. So I did a bit of research, exact
percentages depend on definitions & fashions at the time of birth and
what research you read. Fortunately, as far as I know, no one in my
immediate family falls into this group.
Cheers,
Gavin
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: GavinFlower@archidevsys.co.nz, jorgemal1960@gmail.com
Subject: Re: Advice for index design
In-Reply-To: <5165FD14.5010104@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox