I spent the weekend fooling around trying to reduce the time needed to
start a fresh backend. Profiling seemed to indicate that much of the
time was going into loading entries into the relcache: relcache entry
setup normally requires fetching rows from several different system
catalogs. The obvious way to fix that is to preload entries somehow.
It turns out we already have a mechanism for this (the pg_internal.init
file), but it was only being used to preload entries for a few critical
system indexes --- "critical" meaning "relcache/catcache initialization
becomes an infinite recursion otherwise". I rearranged things so that
pg_internal.init could cache entries for both plain relations and
indexes, and then set it up to cache all the system catalogs and indexes
that are referenced by catalog caches. (This is a somewhat arbitrary
choice, but was easy to implement.)
As near as I can tell, this reduces the user-space CPU time involved in
a backend launch by about a factor of 5; and there's also a very
significant reduction in traffic to shared memory, which should reduce
contention problems when multiple backends are involved. It's difficult
to measure this stuff, however ... profiling is of limited reliability
when you can only get a few clock samples per process launch.
I'm planning to commit these changes when 7.3 opens, unless I hear
objections. A possible objection is that caching more system catalog
descriptors makes it more difficult to support user alterations to the
system catalogs; but we don't support those anyway, and I haven't heard
of anyone working to remove the other obstacles to it. (Note that this
wouldn't completely prevent such things; it would just be necessary to
figure out when to delete the pg_internal.init cache file when making
schema changes.)
regards, tom lane
