Обсуждение: best way to choose index to cluster on?
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
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
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
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 >