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
