0

My basic question is: what affects the performance of \d?

I have a separate (small) schema that I manage on a database server that I have otherwise no higher privileges on. The server holds a huge (billion+ rows) database in its public schema. My separate schema provides some auxiliary information used only for webpage rendering of the data in the public schema.
     Edit: It's actually 5.5 billion+ rows, and that's just in one of the biggest tables (and only took 1.5 hours to finish the count(*)!).

When I do a simple \d at the psql prompt, it is really slow - on the order of 6.5 seconds when I time it. Clearly, the database/database server has heavy load, but should I bring this \d performance to the attention of the sysadmins (who generally don't want to be bothered)? (Does it imply something markedly wrong in the system?) Everything else in my schema runs on the order of milliseconds, so it's not impacting my schema's performance per se.

Regardless of whether I bring it to their attention, what impacts the performance of \d? The public schema has 985 tables. Even so, it seems like it would be a fairly simple query; I would expect it's only looking at table names, not table contents.

1
  • Typically, that either means that the system is really bogged down, or that you have way too many tables. Sep 21 at 19:22

1 Answer 1

1

If you run psql with the -E command line option, you will be able to see the query it emits when you issue \d. You can then run EXPLAIN (ANALYZE, BUFFERS) for that query to see where it may be struggling. You will also see that its performance won't depend on the data size, only on the number of objects in pg_class (and, probably to a lesser extent, the number of schemas).

This is what I see on my system:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.