Re: ToDo List Item - System Table Index Clustering

Hello Postgres Hackers,

In reference to this todo item about clustering system table indexes,           
( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php )
I have been studying the system tables to see which would benefit  from
clustering.  I have some index suggestions and a question if you have a
moment.

Cluster Candidates:

pg_attribute:  Make the existing index ( attrelid, attnum ) clustered to
order it by table and column.

pg_attrdef:  Existing index ( adrelid, adnum ) clustered to order it
by table and column.

pg_constraint:  Existing index ( conrelid ) clustered to get table
constraints contiguous.

pg_depend: Existing Index (refclassid, refobjid, refobjsubid) clustered
to so that when the referenced object is changed its dependencies
arevcontiguous.

pg_description: Make the existing index ( Objoid, classoid, objsubid )
clustered to order it by entity, catalog, and optional column.  
* reversing the first two columns makes more sense to me ...
catalog, object, column or since object implies catalog ( right? )
just dispensing with catalog altogether, but that would mean
creating a new index.

pg_shdependent: Existing index (refclassid, refobjid) clustered for
same reason as pg_depend.

pg_statistic: Existing index (starelid, staattnum) clustered to order
it by table and column.

pg_trigger:  Make the existing index ( tgrelid, tgname ) clustered to
order it by table then name getting all the triggers on a table together.

Maybe Cluster:

pg_rewrite: Not sure about this one ... The existing index ( ev_class,
rulename ) seems logical to cluster to get all the rewrite rules for a
given table contiguous but in the db's available to me virtually every
table only has one rewrite rule.  

pg_auth_members:  We could order it by role or by member of
that role.  Not sure which would be more valuable.


Stupid newbie question:


is there a way to make queries on the system tables show me what
is actually there when I'm poking around?  So for example:

Select * from pg_type limit 1;

tells me that the typoutput is 'boolout'.  An english string rather than
a number.  So even though the documentation says that column
maps to pg_proc.oid I can't then write:

Select * from pg_proc where oid = 'boolout';

It would be very helpful if I wasn't learning the system but since I
am I'd like to turn it off for now.  Fewer layers of abstraction.


Thanks,

Simone Aiken

303-956-7188
Quietly Competent Consulting





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers