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

On Sat, 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've messed around with adding indexes to a copy of pg_class to no
> avail. Any ideas on how to improve the performance?

It is probably the visibility checks. Is a \d fast if you include the
full name (schema.table)?

I brought this up a while ago and Tom has since rearranged some of the
psql queries to move the visibility check to come after the other where
clause segments.

It would be nice if the cost of the function could be added somehow --
even if it was just a low, medium or high setting. This would allow the
planner to shuffle the where clause executing ordering around in a
reasonable manner.
--