We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence the process much short of deleting unnecessary schemas every now and then. Perhaps there were 5000+ schemas during the lifetime of this database, of which 4500- are gone now.
Opening the database using pgAdmin3 takes ages. Opening a schema takes ages. Autocompletion in
psql is horribly slow. This does not happen with "small" databases. I have tried
VACUUM ANALYZE pg_tables, but this is not supported.
Are there some cleanup tasks that need to be carried out to handle this situation? Or perhaps there is a tuning parameter that can be set? An index on a system table missing or outdated?
As a side question: Is it possible to group several schemas so that there is yet another hierarchy level between "database" and "table"?