Re: Index on System Table

Cody Cutrer <cody(at)instructure(dot)com> writes:
> I've got a SaaS situation where I'm using 1000+ schemas in a single
> database (each schema contains the same tables, just different data
> per tenant). ...
> if I add "nspname = ANY(current_schemas(true))" to the query psql is
> using, and an index to pg_class on relnamespace, the query optimizer
> is able to do an index scan, and the queries return in milliseconds
> instead of minutes. However, I can't actually create an index on
> pg_class because it is a system table (I was able to test by copying
> it to a temporary table and adding the index there). My question is if
> there is a way to create the index on the system table somehow for
> just my database,

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.

> and if not how would the developer community react
> to the suggestion of adding an index to a system table in the default
> postgres distro.

In many (probably most) databases, an index on pg_class.relnamespace
wouldn't be selective enough to justify its update costs. I'd want
to see a lot more than one request for this before considering it.

If you're correct that the main costs come from the pg_table_is_visible
tests, it should be possible to dodge that without an extra index.
I'd suggest making a function similar to current_schemas() except it
returns an OID array instead of names (this should be cheaper anyway)
and just putting the relnamespace = ANY(current_schema_oids()) condition
in front of the visibility test. Or maybe you could dispense with the
visibility test altogether, depending on your usage patterns.

(BTW, I think that "\d schemaname.*" doesn't involve any visibility
tests, in case that helps.)

regards, tom lane