[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: [ADMIN] best way to choose index to cluster on?
From:       Scott Marlowe <smarlowe () g2switchworks ! com>
Date:       2006-09-25 22:31:44
Message-ID: 1159223503.26848.46.camel () state ! g2switchworks ! com
[Download RAW message or body]

Note that PostgreSQL doesn't auto-update clustered indexes.  i.e. they
deteriorate over time, and require being re-clustered after a while.

On Mon, 2006-09-25 at 17:27, adey wrote:
> A good guide for a clustered index is:-
> Will the index be unique, static and narrow.
> If not, avoid clustered indexing as they require extra work on disk to
> maintain their sequence.
> 
>  
> On 9/26/06, Chris Hoover <revoohc@gmail.com> wrote: 
>         I am looking to squeeze a bit more speed out of my database by
>         clustering most of my tables.  However, on tables with
>         multiple indexes, how is the best way to choose the index.  I
>         am thinking I want to use the index with the largest
>         pg_stat_user_indexes.idx_tup_read.  Would this be correct?  If
>         not, what column(s) and views should I be looking at to find
>         the most popular index? 
>         
>         Secondly, I have some partial indexes on a very active table,
>         and it tends to have the highest idx_tup_read for that table. 
>         Is is possible to cluster on a partial index?
>         
>         Thanks,
>         
>         Chris 
> 

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]