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.
I'm running on 9.6.14 on CentOS 7.
I've copied the EXPLAIN below. Let me know if additional info would be
helpful. Thanks in advance!
Ken
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;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=197.09..22533.42 rows=39858 width=160) (actual
time=92.538..386.047 rows=8 loops=1)
Output: c.relname, a.attname, col_description(a.attrelid,
(a.attnum)::integer)
Hash Cond: (a.attrelid = c.oid)
Buffers: shared hit=81066
-> Seq Scan on pg_catalog.pg_attribute a (cost=0.00..11718.81
rows=41278 width=70) (actual time=76.069..369.410 rows=8 loops=1)
Output: a.attrelid, a.attname, a.atttypid, a.attstattarget,
a.attlen, a.attnum, a.attndims, a.attcacheoff, a.atttypmod, a.attbyval,
a.attstorage, a.attalign, a.attnotnull, a.atthasdef, a.attisdropped,
a.attislocal, a.attinhcount, a.attcollation, a.attacl, a.attoptions,
a.attfdwoptions
Filter: (col_description(a.attrelid, (a.attnum)::integer) IS NOT
NULL)
Rows Removed by Filter: 40043
Buffers: shared hit=80939
-> Hash (cost=144.82..144.82 rows=4182 width=68) (actual
time=15.932..15.934 rows=4183 loops=1)
Output: c.relname, c.oid
Buckets: 8192 Batches: 1 Memory Usage: 473kB
Buffers: shared hit=103
-> Seq Scan on pg_catalog.pg_class c (cost=0.00..144.82
rows=4182 width=68) (actual time=0.015..7.667 rows=4183 loops=1)
Output: c.relname, c.oid
Buffers: shared hit=103
Planning time: 0.408 ms
Execution time: 386.148 ms
(18 rows)
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-requ...@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
