pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAdvice for index design
4+ messages / 4 participants
[nested] [flat]
* Advice for index design
@ 2013-04-10 22:30 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: JORGE MALDONADO @ 2013-04-10 22:30 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Advice for index design
@ 2013-04-11 00:00 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Gavin Flower @ 2013-04-11 00:00 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Advice for index design
@ 2013-04-11 23:36 Greg Sabino Mullane <greg@turnstep.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Greg Sabino Mullane @ 2013-04-11 23:36 UTC (permalink / raw)
To: pgsql-sql
-----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-----
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Advice for index design
@ 2013-04-12 13:29 Jasen Betts <jasen@xnet.co.nz>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jasen Betts @ 2013-04-12 13:29 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-04-12 13:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-10 22:30 Advice for index design JORGE MALDONADO <jorgemal1960@gmail.com>
2013-04-11 00:00 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2013-04-11 23:36 ` Greg Sabino Mullane <greg@turnstep.com>
2013-04-12 13:29 ` Jasen Betts <jasen@xnet.co.nz>
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