Re: Index on System Table - Mailing list pgsql-general

From Tom Lane
Subject Re: Index on System Table
Date
Msg-id 18130.1332288382@sss.pgh.pa.us
Whole thread Raw
In response to Index on System Table  (Cody Cutrer <cody@instructure.com>)
Responses Re: Index on System Table  (Cody Cutrer <cody@instructure.com>)
List pgsql-general
Cody Cutrer <cody@instructure.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

pgsql-general by date:

Previous
From: Michael Nolan
Date:
Subject: Re: huge price database question..
Next
From: Steve Crawford
Date:
Subject: Re: huge price database question..