Postgres Pro
Downloads
Home   >   mailing lists

Thread: Advice for index design

Advice for index design

From
JORGE MALDONADO
Date:
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

Re: Advice for index design

From
Gavin Flower
Date:
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

Re: Advice for index design

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Jorge Maldonado wrote:

...
> 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.

Yes, just create separate indexes and you will be fine, especially given 
the very small size of the table. If you find your queries going slow, you 
could start investigating compound indexes (or in this case, partial 
indexes).

- -- 
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201304111933
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlFnSPYACgkQvJuQZxSWSshm6wCggdl2FyowAbca93hYKXGgcXoE
iN0AniOL8OS3teTgk6thxkJjUGqEf15k
=1Eb6
-----END PGP SIGNATURE-----

Re: Advice for index design

From
Jasen Betts
Date:
On 2013-04-10, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

> 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? 

the best approach depends on where you need the most improvement.

For your example query an undex on (sex,birthday,name) would be best.


-- 
⚂⚃ 100% natural