Ken Tanzer <ken.tanzer@xxxxxxxxx> writes: > Hi. I've got an app that queries pg_catalog to find any table columns that > have comments. After setting up PgBadger, it was #2 on my list of time > consuming queries, with min/max/avg duration of 199/2351/385 ms (across > ~12,000 executions logged). > I'm wondering if there are any ways to speed this query up, including if > there are better options for what to query. > ag_reach=> EXPLAIN (ANALYZE, VERBOSE,BUFFERS,TIMING, COSTS) > SELECT c.relname AS table,a.attname AS > column,pg_catalog.col_description(a.attrelid, a.attnum) AS comment > FROM pg_catalog.pg_attribute a, pg_class c > WHERE a.attrelid = c.oid > AND pg_catalog.col_description(a.attrelid, a.attnum) IS NOT NULL; Unfortunately, the planner isn't smart enough to inline the col_description() function. But if you do so manually you'll end up with something like SELECT c.relname AS table, a.attname AS column, d.description AS comment FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON a.attrelid = c.oid LEFT JOIN pg_catalog.pg_description d ON d.classoid = c.tableoid and d.objoid = c.oid and d.objsubid = a.attnum WHERE d.description IS NOT NULL; For me, that formulation is quite a bit faster than the original --- what you wrote basically forces a nestloop join against pg_description, and then to add insult to injury, has to search pg_description a second time for each hit. regards, tom lane
