From: | Simone Aiken <saiken(at)ulfheim(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | ToDo List Item - System Table Index Clustering |
Date: | 2011-01-16 05:11:26 |
Message-ID: | F26BB991-244F-4425-808E-8921AFCFDD73@ulfheim.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2011-01-16 07:28:58 | Re: Spread checkpoint sync |
Previous Message | Robert Haas | 2011-01-16 04:14:34 | Re: Spread checkpoint sync |