Re: Index on System Table

Cody Cutrer <cody(at)instructure(dot)com> writes:
> On Tue, Mar 20, 2012 at 6:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There's not really support for adding indexes to system catalogs
>> on-the-fly. I think it would work (barring concurrency issues)
>> for most catalogs, but pg_class has special limitations due to
>> the "relmapping" infrastructure. It's not something I'd particularly
>> care to try on a production database.

BTW, I experimented with that a little bit and found that the relmapper
is not really the stumbling block, at least not after applying this
one-line patch:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f70f095c9096d5e2689e8d79172b37b57a84e51b

It seemed to work for me to do this:

stop postmaster
start a standalone backend with -c allow_system_table_mods=1
create index pg_class_relnamespace_index on pg_class(relnamespace);
stop standalone backend, restart postmaster

There are a lot of gotchas here, notably that the session in which you
create the index won't know it's there (so in this case, a reindex on
pg_class would likely be advisable afterwards). I still think you'd be
nuts to try it on a production database, but ...

regards, tom lane