Is it worth us marking any system catalog indexes as clusterable by 
default for performance?

Chris


Wed, 28 Apr 2004 14:04:34 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Clustering system catalog indexes
Christopher Kings-Lynne wrote:
> Is it worth us marking any system catalog indexes as clusterable by 
> default for performance?

Not sure. Most of the system stuff is loaded in a pretty good order, and
cluster is only good if you are going after seveal rows of identical
value or similar value in the same table, and I can't think of a case
where this would help.  Can others?  It is a good question.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 19 May 2004 21:43:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Clustering system catalog indexes
On Wed, May 19, 2004 at 09:43:22PM -0400, Bruce Momjian wrote:
> Christopher Kings-Lynne wrote:
> > Is it worth us marking any system catalog indexes as clusterable by 
> > default for performance?
> 
> Not sure. Most of the system stuff is loaded in a pretty good order, and
> cluster is only good if you are going after seveal rows of identical
> value or similar value in the same table, and I can't think of a case
> where this would help.  Can others?  It is a good question.

pg_attribute maybe?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cï¿½mo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver quï¿½ formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)

Wed, 19 May 2004 22:02:17 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Clustering system catalog indexes
> Not sure. Most of the system stuff is loaded in a pretty good order, and
> cluster is only good if you are going after seveal rows of identical
> value or similar value in the same table, and I can't think of a case
> where this would help.  Can others?  It is a good question.

pg_attribute would commonly be fetched via the attrelid.  However, I 
guess it's all cached anyway...

Chris

Thu, 20 May 2004 10:09:53 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Clustering system catalog indexes
Alvaro Herrera wrote:
> On Wed, May 19, 2004 at 09:43:22PM -0400, Bruce Momjian wrote:
> > Christopher Kings-Lynne wrote:
> > > Is it worth us marking any system catalog indexes as clusterable by 
> > > default for performance?
> > 
> > Not sure. Most of the system stuff is loaded in a pretty good order, and
> > cluster is only good if you are going after seveal rows of identical
> > value or similar value in the same table, and I can't think of a case
> > where this would help.  Can others?  It is a good question.
> 
> pg_attribute maybe?

Ah, good point.  Because of vacuum reuse, it could grab rows in
different locations when creating a table.  Good point.

Added to TODO:

        o Add default clustering to system tables

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 19 May 2004 22:58:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Clustering system catalog indexes
Christopher Kings-Lynne wrote:
> > Not sure. Most of the system stuff is loaded in a pretty good order, and
> > cluster is only good if you are going after seveal rows of identical
> > value or similar value in the same table, and I can't think of a case
> > where this would help.  Can others?  It is a good question.
> 
> pg_attribute would commonly be fetched via the attrelid.  However, I 
> guess it's all cached anyway...

Yes, but that cache has to be loaded sometime.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 19 May 2004 22:58:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Clustering system catalog indexes