Обсуждение: Speeding up query pulling comments from pg_catalog

Ken Tanzer <ken.tanzer@gmail.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

Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Sat, Jul 20, 2019 at 7:46 AM Tom Lane <tgl@sss.pgh.pa.us> 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

Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Sat, Jul 20, 2019 at 12:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There's been assorted talk about various optimizations to
>> avoid unnecessary duplicate function calls,

> So I had read the sentence below to mean my functions would only get called
> once.  But is that sentence only supposed to apply to index scans?  Or does
> it mean the planner is allowed to optimize, but it just doesn't know how
> yet?

> A STABLE function cannot modify the database and is guaranteed to return
> the same results given the same arguments for all rows within a single
> statement. *This category allows the optimizer to optimize multiple calls
> of the function to a single call.*

It says "allows", not "requires".  But in particular, we've interpreted
that to mean trying to call a stable function (with constant or at least
stable arguments) once per query rather than once per row, as the naive
interpretation of SQL semantics would have us do.  Matching up textually
distinct calls has not been on the radar --- it seems fairly expensive
to do, with no return in typical queries, and relatively small return
even if we find a match.

            regards, tom lane