Thanks for the tips. I spent some more time investigating. It's definitely pg_table_is_visible that's causing the problem. A \dt <schema>.* is fairly fast (like you said, it doesn't apply pg_table_is_visible at all). I tried adjusting the query in several ways. Adding either nspname=ANY(current_schemas(true)) or relnamespace=ANY(<precalculated list of the oids>) didn't help, because the query planner still applied pg_table_is_visible to every row in pg_class. Doing either of those and *removing* the pg_table_is_visible query gave the best results. That may be a good solution, since for tab complete you don't really care which schema and object is coming from, just that there is an object. I'm not sure about for /dt, though. Anyhow, I've found a workaround with acceptable (still not "snappy") performance for all the queries, though. By running "alter function pg_table_is_visible (rel oid) cost 50;" the query planner is now avoiding that function, and doing other filtering first. The queries are all a few seconds now, but not multiple minutes. Cody Cutrer On Tue, Mar 20, 2012 at 6:06 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > 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
