Hi Aaron,
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> Aaron Werman
> Sent: vrijdag 2 april 2004 13:57
>
>
> another thing that I have all over the place is a hierarchy:
> index on grandfather_table(grandfather)
> index on father_table(grandfather, father)
> index on son_table(grandfather, father, son)
>
It depends on your data-distribution, but I find that in almost all cases it's beneficial to have your indexes the
otherway round in such cases:
index on grandfather_table(grandfather)
index on father_table(father, grandfather)
index on son_table(son, father, grandfather)
That usually gives a less common, more selective value at the start of the index, making the initial selection in the
indexsmaller.
And AFAIK I don't have to rewrite my queries for that; the planner doesn't care about the order of expressions in the
querythat are on the same level.
That said, I tend to use 'surrogate keys'; keys generated from sequences or auto-number columns for my tables. It makes
thetables less readable, but the indexes remain smaller.
Greetings,
--Tim
