Обсуждение: Question about CLUSTER
Hello, I'm planning to cluster a few large tables in our database but I'm unable to find any recommendations/documentation on best practices -- Mainly, whether it's better to use an index which has a higher idx_scan value, a higher idx_tup_read value, or the higest idx_tup_fetch value. I'm assuming that idx_tup_read would probably be the best choice, but want to get other opinions before proceeding. Can anyone point me to docs which explain this better? -salman
On Feb 11, 2008 2:03 PM, salman <salmanb@quietcaresystems.com> wrote: > Hello, > > I'm planning to cluster a few large tables in our database but I'm > unable to find any recommendations/documentation on best practices -- > Mainly, whether it's better to use an index which has a higher idx_scan > value, a higher idx_tup_read value, or the higest idx_tup_fetch value. > > I'm assuming that idx_tup_read would probably be the best choice, but > want to get other opinions before proceeding. If you've got two indexes that are both being hit a lot, it might be worth looking into their correlation, and if they get used a lot together, look at creating an index on both. But I'd guess that idx_tup_read would be a good bet.
On Mon, Feb 11, 2008 at 03:33:37PM -0600, Scott Marlowe wrote: > On Feb 11, 2008 2:03 PM, salman <salmanb@quietcaresystems.com> wrote: > > I'm planning to cluster a few large tables in our database but I'm > > unable to find any recommendations/documentation on best practices -- > > Mainly, whether it's better to use an index which has a higher idx_scan > > value, a higher idx_tup_read value, or the higest idx_tup_fetch value. > > > > I'm assuming that idx_tup_read would probably be the best choice, but > > want to get other opinions before proceeding. > > If you've got two indexes that are both being hit a lot, it might be > worth looking into their correlation, and if they get used a lot > together, look at creating an index on both. > > But I'd guess that idx_tup_read would be a good bet. You might also consider the ratio idx_tup_read::float8 / idx_scan to see which indexes access a lot of rows per scan. -- Michael Fuhr