Re: Databases vs Schemas - Mailing list pgsql-performance

From Tom Lane
Subject Re: Databases vs Schemas
Date
Msg-id 9580.1255144295@sss.pgh.pa.us
Whole thread Raw
In response to Re: Databases vs Schemas  (Scott Carey <scott@richrelevance.com>)
Responses Re: Databases vs Schemas  (Chris Kratz <chris.kratz@vistashare.com>)
List pgsql-performance
Scott Carey <scott@richrelevance.com> writes:
> I've got 200,000 tables in one db (8.4), and some tools barely work.  The
> system catalogs get inefficient when large and psql especially has trouble.
> Tab completion takes forever, even if I make a schema "s" with one table in
> it and type "s." and try and tab complete -- its as if its scanning all
> without a schema qualifier or using an index.

The tab-completion queries have never been vetted for performance
particularly :-(

Just out of curiosity, how much does this help?

alter function pg_table_is_visible(oid) cost 10;

(You'll need to do it as superuser --- if it makes things worse, just
set the cost back to 1.)

> Sometimes it does not match
> valid tables at all, and sometimes regex matching fails too ('\dt
> schema.*_*_*' intermittently flakes out if it returns a lot of matches).

There are some arbitrary "LIMIT 1000" clauses in those queries, which
probably explains this ... but taking them out would likely cause
libreadline to get indigestion ...

            regards, tom lane

pgsql-performance by date:

Previous
From: Scott Carey
Date:
Subject: Re: Databases vs Schemas
Next
From: tsuraan
Date:
Subject: Re: UUID as primary key