From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ken Tanzer <ken.tanzer@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Speeding up query pulling comments from pg_catalog
In-reply-to: 
 <CAD3a31UEEojeQ9VYZHO0u2j0eyyh5jaYd8Pc=7yfJEHrfzELFg@mail.gmail.com>
References: 
 <CAD3a31UEEojeQ9VYZHO0u2j0eyyh5jaYd8Pc=7yfJEHrfzELFg@mail.gmail.com>
Comments: In-reply-to Ken Tanzer <ken.tanzer@gmail.com>
	message dated "Fri, 19 Jul 2019 16:03:27 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19787.1563634004.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 20 Jul 2019 10:46:44 -0400
Message-ID: <19788.1563634004@sss.pgh.pa.us>
Precedence: bulk

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi.  I've got an app that queries pg_catalog to find any table columns t=
hat
> 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=3D> 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 =3D 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 =3D c=
.oid
  LEFT JOIN pg_catalog.pg_description d ON d.classoid =3D c.tableoid and d=
.objoid =3D c.oid and d.objsubid =3D 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