From: Scott Ribe <scott_ribe@elevated-dev.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\))
Subject: system catalog performance
Message-Id: <3DA944AB-CCDB-4A74-8562-691D02FD0F2E@elevated-dev.com>
Date: Fri, 1 Oct 2021 08:21:43 -0600
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/3DA944AB-CCDB-4A74-8562-691D02FD0F2E%40elevated-dev.com>
Precedence: bulk

Under what circumstances would the following query be extra slow?

SELECT=20
  pg_catalog.to_char(pg_catalog.pg_postmaster_start_time(), 'YYYY-MM-DD =
HH24:MI:SS.MS TZ'),=20
  CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE ('x' || =
pg_catalog.substr(pg_catalog.pg_walfile_name(pg_catalog.pg_current_wal_lsn=
()), 1, 8))::bit(32)::int END,=20
  CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE =
pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_current_wal_lsn(), =
'0/0')::bigint END,=20
  pg_catalog.pg_wal_lsn_diff(pg_catalog.pg_last_wal_replay_lsn(), =
'0/0')::bigint,=20
  =
pg_catalog.pg_wal_lsn_diff(COALESCE(pg_catalog.pg_last_wal_receive_lsn(), =
'0/0'), '0/0')::bigint,=20
  pg_catalog.pg_is_in_recovery() AND =
pg_catalog.pg_is_wal_replay_paused(),=20
  pg_catalog.to_char(pg_catalog.pg_last_xact_replay_timestamp(), =
'YYYY-MM-DD HH24:MI:SS.MS TZ'),=20
  =
pg_catalog.array_to_json(pg_catalog.array_agg(pg_catalog.row_to_json(ri)))=
=20
FROM (
  SELECT=20
    (SELECT rolname FROM pg_authid WHERE oid =3D usesysid) AS usename,=20=

    application_name,=20
    client_addr,=20
    w.state,=20
    sync_state,
    sync_priority=20
  FROM pg_catalog.pg_stat_get_wal_senders() w, =
pg_catalog.pg_stat_get_activity(pid)
) as ri;=EF=BB=BF

This is regarding Patroni--I haven't yet dug into that source to see how =
long that timeout is. PG 13.3, monitoring did not show heavy load nor =
unusual lock activity around when this happened.=