Re: single index on more than two coulumns a bad thing? - Mailing list pgsql-performance

From Leeuw van der, Tim
Subject Re: single index on more than two coulumns a bad thing?
Date
Msg-id DD0DC14935B1D211981A00105A1B28DB0C912743@NL-ASD-EXCH-1
Whole thread Raw
In response to single index on more than two coulumns a bad thing?  (Palle Girgensohn <girgen@pingpong.net>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: "Gary Doades"
Date:
Subject: Re: PostgreSQL and Linux 2.6 kernel.
Next
From: "Heiko Kehlenbrink"
Date:
Subject: performance comparission postgresql/ms-sql server