pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
best way to choose index to cluster on?
3+ messages / 3 participants
[nested] [flat]

* best way to choose index to cluster on?
@ 2006-09-25 20:57 Chris Hoover <revoohc@gmail.com>
  2006-09-25 22:27 ` Re: best way to choose index to cluster on? adey <adey11@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Chris Hoover @ 2006-09-25 20:57 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: best way to choose index to cluster on?
  2006-09-25 20:57 best way to choose index to cluster on? Chris Hoover <revoohc@gmail.com>
@ 2006-09-25 22:27 ` adey <adey11@gmail.com>
  2006-09-25 22:31   ` Re: best way to choose index to cluster on? Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 1 reply; 3+ messages in thread

From: adey @ 2006-09-25 22:27 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: best way to choose index to cluster on?
  2006-09-25 20:57 best way to choose index to cluster on? Chris Hoover <revoohc@gmail.com>
  2006-09-25 22:27 ` Re: best way to choose index to cluster on? adey <adey11@gmail.com>
@ 2006-09-25 22:31   ` Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Scott Marlowe @ 2006-09-25 22:31 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin

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 
> 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-09-25 22:31 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-25 20:57 best way to choose index to cluster on? Chris Hoover <revoohc@gmail.com>
2006-09-25 22:27 ` adey <adey11@gmail.com>
2006-09-25 22:31   ` Scott Marlowe <smarlowe@g2switchworks.com>

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