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). I used schemas so that the shared app servers could
share a connection to the single database for all schemas. Things are
working fine. However, when using psql, doing \d or trying to use tab
complete takes FOREVER, because it's doing a sequence scan against
pg_class (which has over a million rows), and relying on
pg_table_is_visible to do search_path filtering. I've figured out that
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, 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.
Thanks,
Cody Cutrer