Re: \d on database with a lot of tables is slow

On L, 2005-09-24 at 18:59 -0500, Jim C. Nasby wrote:
> I have a client with a database that contains 4000 relations according
> to vacuum verbose, and \d in psql is painfully slow. In particular...
>
> -> Seq Scan on pg_class c (cost=0.00..2343.09 rows=6124 width=73) (actual time=0.325..22100.840 rows=16856 loops=1)
> Filter: (((relkind = 'r'::"char") OR (relkind = 'v'::"char") OR (relkind = 'S'::"char") OR (relkind = ''::"char")) AND pg_table_is_visible(oid))
>
> That's off my laptop, but they're seeing similar issues on an 8-way
> Opteron as well...

I expext the issue on 8-way opteron to be more of a high load than slow
scan. It seems that sometimes a database with lots of activity slows
down considerably. I suspect some locking issues, but I'm not sure this
is the case.

Also, if a lot of temp tebles are used, then pg_class and pg_attribute
(at least) get bloated quickly and need vacuuming .

> I've messed around with adding indexes to a copy of pg_class to no
> avail. Any ideas on how to improve the performance?
>
> Also, not sure if this matters, but they're occasionally getting errors
> like 'Did not find relation named "table that exists"' (where 'table
> that exists' is the name of some table that is in the catalog) from \d.
> Does anyone know what causes that?

mostly this happens on temp tables from other connections, which have
managed to disappear by the time their detailed info is requested, and
which would actually not show up in \d output due tu visibility checks.

--
Hannu Krosing <hannu(at)skype(dot)net>