Re: Speeding up query pulling comments from pg_catalog

[Tom Lane]

Ken Tanzer  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

Re: Speeding up query pulling comments from pg_catalog

[Ken Tanzer]

On Sat, Jul 20, 2019 at 7:46 AM Tom Lane  wrote:

> 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 ---
>

A lot faster for me too (~30-40 ms).  Thanks!


and then to add insult to injury, has to search pg_description a second
> time for each hit.
>

Not sure if I'm understanding this correctly, but are you saying that
because col_description() is specified in two places in the query, that it
actually will get called twice?  I was under the impression that a function
(at least a non-volatile one) specified multiple times, but with the same
arguments, would only get called once. Is that just wishful thinking?

Cheers,

Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Speeding up query pulling comments from pg_catalog

[Tom Lane]

Ken Tanzer  writes:
> On Sat, Jul 20, 2019 at 7:46 AM Tom Lane  wrote:
>> and then to add insult to injury, has to search pg_description a second
>> time for each hit.

> Not sure if I'm understanding this correctly, but are you saying that
> because col_description() is specified in two places in the query, that it
> actually will get called twice?

Yes.

> I was under the impression that a function
> (at least a non-volatile one) specified multiple times, but with the same
> arguments, would only get called once. Is that just wishful thinking?

Afraid so.  There's been assorted talk about various optimizations to
avoid unnecessary duplicate function calls, but I don't think that
merging textually-separate calls has even been on the radar.  The
discussions I can recall have been more about not calling stable functions
(with fixed arguments) more than once per query.

regards, tom lane