From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Speeding up query pulling comments from pg_catalog |
Date: | 2019-07-20 14:46:44 |
Message-ID: | 19788.1563634004@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2019-07-20 19:08:00 | Re: Speeding up query pulling comments from pg_catalog |
Previous Message | Ken Tanzer | 2019-07-19 23:03:27 | Speeding up query pulling comments from pg_catalog |