Re: ToDo List Item - System Table Index Clustering

Followup on System Table Index clustering ToDo -

It looks like to implement this I need to do the following:

1 - Add statements to indexing.h to cluster the selected indexes.
A do-nothing define at the top to suppress warnings and then
lines below for perl to parse out.

#define DECLARE_CLUSTER_INDEX(table,index) ...
( add the defines under the index declarations ).

2 - Alter genbki.pl to produce the appropriate statements in
postgres.bki when it reads the new lines in indexing.h.
Will hold them in memory until the end of the file so they
will come in after 'Build Indices' is called.

CLUSTER tablename USING indexname

3 - Initdb will pipe the commands in postgres.bki to the
postgres executable running in --boot mode. Code
will need to be added to bootparse.y to recognize
this new command and resolve it into a call to

cluster_rel( tabOID, indOID, 0, 0, -1, -1 );

Speak now before I learn Bison ... actually I should probably
learn Bison anyway. After ProC other pre-compilation languages
can't be that bad.

Sound all right?

Thanks,

-Simone Aiken

On Jan 15, 2011, at 10:11 PM, Simone Aiken wrote:

>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers