On Sat, Nov 7, 2009 at 9:58 PM, Aris Samad-Yahaya <aris@quickschools.com> wrote:
> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
> schedule this weekly moving forward).
>
> Interesting pointer about system catalog bloat. I tried to vacuum full the
> system catalog tables (pg_*), and the performance for creating a single
> table manually improved dramatically (back to what it used to be), but as
> soon as I created the next schema, the performance went back down to the
> same level.
>
> So there's a clue there somewhere. Next I will try to vacuum full the entire
> database.
If you don't run autovac, it's possible you've managed to bloat your
pg_catalog tables... Note that we run similar numbers of tables, as
we have 30 tables and about 10 indexes in over 2000 schemas. We did
the trick Tom posted:
alter function pg_table_is_visible(oid) cost 10;
to get faster tab completion and / or \d performance.