* 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.


> 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

src/backend/catalog/index.c:495 (or so)
 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).



Attachment: signature.asc
Description: Digital signature

Reply via email to