Cody Cutrer <cody@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
