Search Postgresql Archives

Re: Index on System Table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux