* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > * Tom Lane ([EMAIL PROTECTED]) wrote: > >> I think the best solution for this might be to put the responsibility > >> for creating system catalogs' toast tables into the bootstrap phase > >> instead of making initdb do it afterwards. > > > Would this make it much more difficult to support user-defined indexes > > on system catalogs? > > AFAICS the problems with that are orthogonal to this. You'll never have > user-defined (as in "added after initdb") indexes on shared catalogs, > because there is no way to update their pg_class descriptions in all > databases at once.
Ok.
> For non-shared catalogs there's nothing except
> access permissions stopping you from adding ordinary indexes now.
I had thought this might be the case since I had some recollection of
indexes on catalogs either being speculated about or suggested on
-perform. The error-message isn't entirely clear about this fact
though:
src/backend/catalog/index.c:495 (or so)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("user-defined indexes on system catalog tables are not supported")));
> And are you seeing any performance issues related to lack of indexes?
Depends on the eye of the beholder to some extent I suppose.
> For the system catalogs we understand the access patterns pretty well
> (I think), and I thought we pretty much had the right indexes on them
> already.
The case that I was specifically thinking about was the relowner in
pg_class not being indexed.
tsf=> explain analyze select cl.relname from pg_authid a join pg_class
cl on (a.oid = cl.relowner) where a.rolname = 'postgres';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2.54..1970.25 rows=383 width=64) (actual
time=0.113..77.950 rows=223 loops=1)
Hash Cond: ("outer".relowner = "inner".oid)
-> Seq Scan on pg_class cl (cost=0.00..1881.59 rows=16459 width=68)
(actual time=0.036..46.607 rows=17436 loops=1)
-> Hash (cost=2.54..2.54 rows=1 width=4) (actual time=0.057..0.057
rows=1 loops=1)
-> Seq Scan on pg_authid a (cost=0.00..2.54 rows=1 width=4)
(actual time=0.047..0.050 rows=1 loops=1)
Filter: (rolname = 'postgres'::name)
Total runtime: 78.358 ms
(7 rows)
It's not exactly *slow* but an index might speed it up. I was trying to
create one and couldn't figure out the right incantation to make it
happen. 'allow_system_table_mods = true' wasn't working in
postgresql.conf (it wouldn't start) for some reason...
Other system-catalog queries that I've been a little unhappy about the
performance of (though I don't know if indexes would help, so this is
really just me complaining) are: initial table list in ODBC w/ Access
(takes *forever* when you have alot of tables...); schema/table lists in
phppgadmin when there are alot of schemas/tables; information_schema
queries (try looking at information_schema.columns for a given table
when you've got alot of tables... over 10x slower than looking at
pg_class/pg_attribute directly, 3 seconds vs. 200ms, or so).
Thanks,
Stephen
signature.asc
Description: Digital signature
