Re: Relation locking and relcache load (was Re: Going for "all green" buildfarm results) - Mailing list pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) 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.59rows=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