はじめに

PostgreSQLの監視や運用時などに便利（と思われる）なSQLの個人メモです。
色々な資料やサイトを参考に一覧にしてみました。

これからも便利そうなSQLを書いたり、見つけたりしたら都度更新していくつもりです。
なお、実行例の載せており、全てPostgreSQL12で実行した結果です。

キャッシュヒット率

データベース毎のキャッシュヒット率

select datname,
round(blks_hit*100/(blks_hit+blks_read), 2) as cache_hit_ratio
from pg_stat_database where blks_read > 0;

出力例

  datname  | cache_hit_ratio 
-----------+-----------------
           |           97.00
 postgres  |           96.00
 testdb    |           99.00
 dvdrental |           96.00
(4 rows)

テーブルのキャッシュヒット率を時系列で表示する（要pg_statsinfo）

select
  s.time,
  statsrepo.div(
    statsrepo.sub(t.heap_blks_hit, lag(t.heap_blks_hit, 1) over (order by t.snapid asc)),
    statsrepo.sub(t.heap_blks_read, lag(t.heap_blks_read, 1) over (order by t.snapid asc))
  ) "キャッシュヒット率"
from
  statsrepo.tables t left join statsrepo.snapshot s on t.snapid = s.snapid
where
  t."table" = 'テーブル名' -- ★設定する
  and t.database = 'postgres' -- ★設定する
  and t.schema = 'public' -- ★設定する
  and s.time between '2020-03-06 00:00:00'::timestamp and '2020-03-13 00:00:00'::timestamp; -- ★設定する

長時間実行されているSQLを抽出する

以下のSQLでは60秒以上、実行されているSQLを抽出します。

select datname, pid, usename, application_name, xact_start,
    (now() - xact_start)::interval(3) as duration, query
from pg_stat_activity
where (now() - xact_start)::interval > '60 sec'::interval;

オブジェクトのサイズを確認する

データベースのサイズを確認する

testdb=# select pg_size_pretty(pg_database_size('testdb'));
 pg_size_pretty 
----------------
 24 MB
(1 row)

テーブルのサイズを確認する

testdb=> select pg_relation_size('pgbench_accounts');
 pg_relation_size 
------------------
        135913472
(1 row)


インデックスを除外した(ただしTOAST、空き領域マップ、可視性マップを含む)ディスクサイズ。
testdb=> select pg_table_size('pgbench_accounts');
 pg_table_size 
---------------
     135979008
(1 row)

インデックスのサイズを確認する

インデックス名を指定する場合
testdb=> select pg_relation_size('pgbench_accounts_pkey');
 pg_relation_size 
------------------
         22487040
(1 row)

テーブル名を指定する場合。テーブルに作成された全てのインデックスを含む。
testdb=> select pg_indexes_size('pgbench_accounts');
 pg_indexes_size 
-----------------
        22487040
(1 row)

テーブルとインデックスの合計サイズ

testdb=> select pg_total_relation_size('pgbench_accounts');
 pg_total_relation_size 
------------------------
              158466048
(1 row)

ロック待ちが発生しているプロセスを確認する

pg_locks.grantedが"false"の場合、ロック待ちとなっている。
ロックを取得しているプロセスを表示したい場合は、以下のSQLでは表示されないのでpg_locks.grantedの条件を変更する。
※ロックが取得されている場合はtrue、ロック待ち状態の場合はfalse

select lock.locktype, class.relname, lock.pid, lock.mode, act.query
from pg_locks lock
  left outer join pg_stat_activity act on lock.pid = act.pid
  left outer join pg_class class on lock.relation = class.oid where not lock.granted
order by lock.pid;

以下、PostgreSQLエンタープライズ・コンソーシアム技術部会 WG#3の「2018年度WG3活動報告」から抜粋。

SELECT date_trunc('second', current_timestamp(0)) AS time,
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process,
    (current_timestamp - blocked_activity.xact_start) ::interval(3) AS duration
FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity
    blocked_activity
ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks AS
    blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT
FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT
FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT
FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT
FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT
FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT
FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT
FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT
FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT
FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity
    blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

テーブルのレコード数、ページ数を確認する

testdb=# select relname, relkind, relpages, reltuples from pg_class where relname = 'pgbench_accounts';
     relname      | relkind | relpages | reltuples 
------------------+---------+----------+-----------
 pgbench_accounts | r       |    16394 |     1e+06
(1 row)

テーブルとインデックスの統計を確認する

タプルレベルの統計情報を取得するpgstattupleモジュールを使用します。
使用するためには、create extensionを実行します。

# create extension pgstattuple;
CREATE EXTENSION

pgstattupleについては以下を参照。

https://www.postgresql.jp/document/11/html/pgstattuple.html

テーブルの統計を確認する

testdb=# select * from pgstattuple('pgbench_accounts');
-[ RECORD 1 ]------+---------
table_len          | 13697024
tuple_count        | 100000
tuple_len          | 12100000
tuple_percent      | 88.34
dead_tuple_count   | 1658
dead_tuple_len     | 200618
dead_tuple_percent | 1.46
free_space         | 198924
free_percent       | 1.45

tuple_lenはタプルのサイズ（bytes)になります。
実際にはヘッダーも含まれるので、table_lenのサイズになります。
free_spaceはFILLFACTOR=100ならタプルが入りきらない場合の余りで、FILLFACTORが100未満であれば、その分の空きも含めたサイズになります。

インデックスの統計を確認する

pgstatindexを使用するためには、pgstattupleモジュールが必要です。

testdb=# select * from pgstatindex('pgbench_branches_pkey');
-[ RECORD 1 ]------+------
version            | 4
tree_level         | 0
index_size         | 16384
root_block_no      | 1
internal_pages     | 0
leaf_pages         | 1
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 0.29
leaf_fragmentation | 0

avg_leaf_densityはリーフページの平均密度（%）です。
leaf_fragmentationはリーフページの断片化の状況で、低い方が良いです。

テーブル情報の近似値を取得するためには以下のように実行します。
pgstattuple_approxによりpgstattupleより高速に情報を取得することができます。

testdb=# select * from pgstattuple_approx('pgbench_accounts');
-[ RECORD 1 ]--------+---------------------
table_len            | 134381568
scanned_percent      | 100
approx_tuple_count   | 1000000
approx_tuple_len     | 121000000
approx_tuple_percent | 90.04211053706413
dead_tuple_count     | 207
dead_tuple_len       | 25047
dead_tuple_percent   | 0.018638716881172274
approx_free_space    | 1894920
approx_free_percent  | 1.4101040999908558

テーブルの行長を確認する

testdb=# select tablename, attname, avg_width from pg_stats where tablename = 'pgbench_accounts';
    tablename     | attname  | avg_width 
------------------+----------+-----------
 pgbench_accounts | aid      |         4
 pgbench_accounts | bid      |         4
 pgbench_accounts | abalance |         4
 pgbench_accounts | filler   |        85
(4 rows)

testdb=# select tablename, sum(avg_width) as sum_size from pg_stats group by tablename order by sum_size desc;
               tablename                | sum_size 
----------------------------------------+----------
 pgbench_accounts                       |       97

チェックポイントの回数を確認する

testdb=# select * from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 31
checkpoints_req       | 4
checkpoint_write_time | 307814
checkpoint_sync_time  | 140
buffers_checkpoint    | 4955
buffers_clean         | 0
maxwritten_clean      | 0
buffers_backend       | 4179
buffers_backend_fsync | 0
buffers_alloc         | 6155
stats_reset           | 2019-10-10 13:54:19.079124+02

buffers_backend: 新しいバッファ割り当てのためバックエンドプロセスにより書き出されたdirtyバッファの数
checkpoints_timed: タイムアウトでチェックアウトが発生した回数
checkpoints_req: 更新の閾値超過でチェックアウトが発生した回数

checkpoints_reqが大きい場合は、checkpoint_segmentsが増加している可能性がある。
buffers_backendがbuffers_allocより大きい場合、shared_buffersの値が不足している可能性がある。

バッファの使用状況を確認する

pg_buffercacheモジュールを使用するので、CREATE EXTENSIONを実行しておきます。

CREATE EXTENSION pg_buffercache;

select c.relname, count(*) as buffers
from pg_buffercache as b
inner join pg_class as c on b.relfilenode = pg_relation_filenode(c.oid) and b.reldatabase in (0, (select oid from pg_database where datname = current_database()))
group by c.relname
order by 2 desc;


                    relname                    | buffers 
-----------------------------------------------+---------
 pgbench_accounts                              |    1676
 pgbench_accounts_pkey                         |     276
 pg_proc                                       |      83
 pgbench_history                               |      73
 pg_attribute                                  |      40
 pgbench_branches                              |      22
 pg_statistic                                  |      18
 pg_class                                      |      17
 pg_depend_reference_index                     |      15
 pgbench_tellers                               |      12
 pg_proc_proname_args_nsp_index                |      11
 pg_proc_oid_index                             |      10
 pg_depend                                     |      10

DML(Insert/Update/Delete)の実行回数を確認する

testdb=> select relname, n_tup_ins as insert_cnt, n_tup_upd as update_cnt, n_tup_del as delete_cnt from pg_stat_user_tables order by relname;

     relname      | insert_cnt | update_cnt | delete_cnt 
------------------+------------+------------+------------
 pgbench_tellers  |         10 |      20000 |          0
 test             |          1 |          0 |          0
 pgbench_accounts |     100000 |      20000 |          0
 pgbench_branches |          1 |      20000 |          0
 pgbench_history  |      20000 |          0 |          0
(5 rows)

回数をクリアする場合は以下を実行する。

select pg_stat_reset();

データベース毎の同時接続数を確認する

select datname, count(*) as connections
from pg_stat_activity
where datname is not null
group by datname;

 datname | connections 
---------+-------------
 testdb  |           1
(1 row)

ディスクソートの実行回数を確認する

work_memが不足していないか確認できます。

select datname, temp_files, pg_size_pretty(temp_bytes) as temp_bytes, pg_size_pretty(round(temp_bytes/temp_files,2)) as temp_file_size
from pg_stat_database
where temp_files > 0;

 datname | temp_files | temp_bytes | temp_file_size 
---------+------------+------------+----------------
 testdb  |          8 | 3968 kB    | 496 kB
(1 row)

temp_files: 作成された一時ファイルの数
pg_size_pretty(temp_bytes): 一時ファイルの合計サイズ
pg_size_pretty(round(temp_bytes/temp_files,2)): 一時ファイルの平均サイズ

一時ファイルが多い場合はwork_memのチューニングを検討する。

現在実行中のSQL一覧を表示する

select * from pg_stat_activity where state != 'idle' order by query_start asc;

-[ RECORD 1 ]----+-------------------------------------------------------------------------------
datid            | 16385
datname          | testdb
pid              | 4711
usesysid         | 16384
usename          | testuser
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-10-12 07:09:06.447984+02
xact_start       | 2019-10-12 07:09:15.463519+02
query_start      | 2019-10-12 07:09:15.463519+02
state_change     | 2019-10-12 07:09:15.463521+02
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 20720
query            | select * from pg_stat_activity where state != 'idle' order by query_start asc;
backend_type     | client backend

システム全体のスループット

select datname, xact_commit, xact_rollback from pg_stat_database;

  datname  | xact_commit | xact_rollback 
-----------+-------------+---------------
           |           0 |             0
 postgres  |         744 |             0
 testdb    |       20935 |            10
 template1 |           0 |             0
 template0 |           0 |             0
 dvdrental |         927 |             1
(6 rows)

xact_commit: コミットされたトランザクション数
xact_rollback: ロールバックされたトランザクション数

大量の行を読み取っている表スキャンを確認する

select relname, seq_scan, seq_tup_read,
   seq_tup_read/seq_scan as tup_per_read  from pg_stat_user_tables
     where seq_scan > 0 order by tup_per_read desc;

     relname      | seq_scan | seq_tup_read | tup_per_read 
------------------+----------+--------------+--------------
 pgbench_accounts |        3 |       201658 |        67219
 pgbench_tellers  |      374 |         3740 |           10
 pgbench_branches |     2464 |         2464 |            1
 test             |        1 |            1 |            1
(4 rows)

seq_scan : シーケンシャルスキャンの回数
seq_tup_read : スキャンで読み込んだタプル数
tup_per_read : 1回のスキャンで読み込んだタプル数

ガベージを大量に持っているテーブルを確認する

select relname, n_live_tup, n_dead_tup,
    round(n_dead_tup*100/(n_dead_tup+n_live_tup), 2)  as dead_ratio,
     pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables
     where n_live_tup > 0 order by dead_ratio desc;

     relname      | n_live_tup | n_dead_tup | dead_ratio | pg_size_pretty 
------------------+------------+------------+------------+----------------
 pgbench_accounts |     100000 |       3421 |       3.00 | 13 MB
 pgbench_tellers  |         10 |          0 |       0.00 | 64 kB
 pgbench_branches |          1 |          0 |       0.00 | 144 kB
 test             |          1 |          0 |       0.00 | 8192 bytes
 pgbench_history  |      10000 |          0 |       0.00 | 568 kB
(5 rows)

n_live_tup : 有効な行数（推定）
n_dead_tup: 不要な行数（推定）

設定を確認する

select name,setting,unit,context from pg_settings;

                  name                  |      setting       | unit |      context      
----------------------------------------+--------------------+------+-------------------
 allow_system_table_mods                | off                |      | postmaster
 application_name                       | psql               |      | user
 archive_cleanup_command                |                    |      | sighup
 archive_command                        | (disabled)         |      | sighup
 archive_mode                           | off                |      | postmaster
 archive_timeout                        | 0                  | s    | sighup
 array_nulls                            | on                 |      | user
 authentication_timeout                 | 60                 | s    | sighup
 autovacuum                             | on                 |      | sighup
 autovacuum_analyze_scale_factor        | 0.1                |      | sighup
 autovacuum_analyze_threshold           | 50                 |      | sighup
 autovacuum_freeze_max_age              | 200000000          |      | postmaster

SQLの総実行回数、総実行時間を確認する(pg_stat_statements)

pg_stat_statementsモジュールを使用してSQLの総実行回数、総実行時間を確認します。
事前にpg_stat_statementsモジュールの設定が必要です（省略）。
単位は秒です。

select substr(query, 0, 600) as query, calls
  ,(total_time / 1000)::numeric(20,3) as total_time_sec
  ,(mean_time / 1000)::numeric(20,3) as avg_time_sec
  ,(min_time / 1000)::numeric(20,3) as min_time_sec
 ,(max_time / 1000)::numeric(20,3) as max_time_sec
from pg_stat_statements
order by total_time desc
limit 100;

                                      query                                      | calls | total_time_sec | avg_time_sec | min_time_sec | max_time_sec 
---------------------------------------------------------------------------------+-------+----------------+--------------+--------------+--------------
 UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2             | 20000 |         69.178 |        0.003 |        0.000 |        0.034
 UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2              | 20000 |         55.097 |        0.003 |        0.000 |        0.045
 UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2             | 20000 |          0.632 |        0.000 |        0.000 |        0.005
 SELECT abalance FROM pgbench_accounts WHERE aid = $1                            | 20000 |          0.199 |        0.000 |        0.000 |        0.000
 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $ | 20000 |          0.161 |        0.000 |        0.000 |        0.002
 copy pgbench_accounts from stdin                                                |     1 |          0.128 |        0.128 |        0.128 |        0.128
 vacuum analyze pgbench_accounts                                                 |     1 |          0.063 |        0.063 |        0.063 |        0.063
 alter table pgbench_accounts add primary key (aid)                              |     1 |          0.058 |        0.058 |        0.058 |        0.058
 CREATE EXTENSION pg_stat_statements                                             |     1 |          0.030 |        0.030 |        0.030 |        0.030
 vacuum pgbench_branches                                                         |     2 |          0.025 |        0.013 |        0.012 |        0.013
(10 rows)

値は累積値で、リセットする場合は以下のSQLを実行する。

select pg_stat_statements_reset();

パーティション設定の確認

psqlメタコマンドの場合

testdb=> \d+ measurement
                           Partitioned table "public.measurement"
  Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+---------+-----------+----------+---------+---------+--------------+-------------
 city_id   | integer |           | not null |         | plain   |              | 
 logdate   | date    |           | not null |         | plain   |              | 
 peaktemp  | integer |           |          |         | plain   |              | 
 unitsales | integer |           |          |         | plain   |              | 
Partition key: RANGE (logdate)
Partitions: measurement_y2019m01 FOR VALUES FROM ('2019-01-01') TO ('2019-02-01'),
            measurement_y2019m02 FOR VALUES FROM ('2019-02-01') TO ('2019-03-01'),
            measurement_y2019m03 FOR VALUES FROM ('2019-03-01') TO ('2019-04-01')

SQLの場合

select pt.relnamespace::regnamespace::text as schema,
  base_tb.relname as parent_table_name, 
  pt.relname as table_name, 
  pg_get_partkeydef(base_tb.oid) as partition_key,
  pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from
  pg_class base_tb
  join pg_inherits i on i.inhparent = base_tb.oid 
  join pg_class pt on pt.oid = i.inhrelid;

 schema | parent_table_name |      table_name      |  partition_key  |               partition_expression               
--------+-------------------+----------------------+-----------------+--------------------------------------------------
 public | measurement       | measurement_y2019m01 | RANGE (logdate) | FOR VALUES FROM ('2019-01-01') TO ('2019-02-01')
 public | measurement       | measurement_y2019m02 | RANGE (logdate) | FOR VALUES FROM ('2019-02-01') TO ('2019-03-01')
 public | measurement       | measurement_y2019m03 | RANGE (logdate) | FOR VALUES FROM ('2019-03-01') TO ('2019-04-01')
(3 rows)

VACUUM, ANALYZEが最後に実行された日時の確認する。

VACUUM, ANALYZEが最後に実行された日時を確認します。

last_vacuum: 最後にVACUUMを実行した日時
last_autovacuum: 最後にAUTOVACUUMが実行された日時
last_analyze: 最後にANALYZEを実行した日時
last_autoanalyze: 最後にAUTOANALYZEが実行された日時

select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

VACUUMの進捗状況を確認する

VACUUMの進捗状況を確認します。VACUUM FULLはサポートされません。

select
  v.pid,
  v.datname,
  c.relname,
  v.phase,
  v.heap_blks_total,
  v.heap_blks_scanned,
  v.heap_blks_vacuumed,
  v.index_vacuum_count,
  v.max_dead_tuples,
  v.num_dead_tuples
from
  pg_stat_progress_vacuum as v
join
  pg_class as c
on v.relid = c.relfilenode;

Autovacuumのテーブルごとの実行履歴を確認する(要pg_statsinfo)

pg_statsinfoはPostgreSQLの利用統計情報を定期的に収集・蓄積するツール（拡張）です。
Autovacuumの実行結果の履歴を確認することができます。

# Autovacuumの実行履歴
select * from statsrepo.autovacuum where start > '2020-08-26★日付を指定'::timestamp order by start;

# Autovacuumがキャンセルされた実行履歴
select * from statsrepo.autovacuum_cancel order by timestamp;

テーブルのOIDを確認する

oid2nameを使用する方法

$ oid2name -U postgres -d testdb
From database "testdb":
  Filenode            Table Name
--------------------------------
     16433   measurement_default
     16420  measurement_y2019m01
     16423  measurement_y2019m02
     16426  measurement_y2019m03
     16429           meter_reads
     57555      pgbench_accounts
     57556      pgbench_branches
     57557       pgbench_history
     57558       pgbench_tellers
     57537           sizing_test
     32903                test01

SQLで取得する方法

testdb=# select relid,relname from pg_stat_all_tables;
 relid |         relname         
-------+-------------------------
  3541 | pg_range
  3600 | pg_ts_dict
  3764 | pg_ts_template
  1213 | pg_tablespace
  1259 | pg_class
  2617 | pg_operator
  2603 | pg_amproc
 14050 | sql_sizing_profiles

テーブルのFILLFACTORを確認する

testdb=> select relname, reloptions from pg_class where reloptions is not null;
     relname      |                reloptions                 
------------------+-------------------------------------------
 test01           | {fillfactor=100,autovacuum_enabled=false}
 pgbench_accounts | {fillfactor=100}
 pgbench_branches | {fillfactor=100}
 pgbench_tellers  | {fillfactor=100}
 pg_stats         | {security_barrier=true}
 pg_stats_ext     | {security_barrier=true}
(6 rows)

プロセスを停止

# プロセスを停止
select pg_cancel_backend([プロセスID]);
# プロセスを強制停止
select pg_terminate_backend([プロセスID]);

OIDを確認する

テーブル
testdb=> select 'pgbench_accounts'::regclass::oid;
  oid  
-------
 16392

testdb=>  select relid,relname from pg_stat_all_tables where relname = 'pgbench_accounts';
 relid |     relname      
-------+------------------
 16392 | pgbench_accounts


インデックス（SQLはテーブルの場合と同じ）
testdb=> select 'pgbench_accounts_pkey'::regclass::oid;
  oid  
-------
 16406

oid2nameの場合

$ oid2name -d testdb -t pgbench_accounts
From database "testdb":
  Filenode        Table Name
----------------------------
     16398  pgbench_accounts

$ oid2name -d testdb -i
From database "testdb":
  Filenode             Table Name
---------------------------------
     16398       pgbench_accounts
     16406  pgbench_accounts_pkey
     16399       pgbench_branches
     16402  pgbench_branches_pkey
     16408        pgbench_history
     16401        pgbench_tellers
     16404   pgbench_tellers_pkey

インデックスが使用されているか確認する

idx_scanでインデックスへ実施されたスキャン回数が確認できます。
idx_scan=0であれば、インデックスが使用されていないということになります。
不要であれば削除する、SQLに問題あればインデックスが使用されるように検討します。

postgres=# select * from pg_stat_user_indexes;
 relid | indexrelid | schemaname | relname  |  indexrelname  | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+----------+----------------+----------+--------------+---------------
 24576 |      24582 | public     | test_tbl | idx_test_tbl01 |        0 |            0 |             0
(1 row)

パーティショニングテーブルの確認

パーティショニングテーブルの一覧を取得する。

select ns_parent.nspname, cl_parent.relname, cl_child.relname from pg_class cl_parent, pg_class cl_child, pg_inherits inh, pg_namespace ns_parent, pg_namespace ns_child
where cl_parent.oid = inh.inhparent and cl_child.oid = inh.inhrelid and cl_parent.relkind in ('p', 'r')
and ns_parent.oid = cl_parent.relnamespace and ns_child.oid = cl_parent.relnamespace
and ns_parent.nspname = 'スキーマ名' order by cl_parent.relname, cl_child.relname

'p': 宣言的パーティショニング

パーティショニングテーブルのインデックスの一覧を取得する。

select ns_parent.nspname, cl_parent.relname, cl_child.relname from pg_class cl_parent, pg_class cl_child, pg_inherits inh, pg_namespace ns_parent, pg_namespace ns_child
where cl_parent.oid = inh.inhparent and cl_child.oid = inh.inhrelid and cl_parent.relkind in ('i', 'I')
and ns_parent.oid = cl_parent.relnamespace and ns_child.oid = cl_parent.relnamespace
and ns_parent.nspname = 'スキーマ名' order by cl_parent.relname, cl_child.relname

'I': 宣言的パーティショニングの親テーブルのインデックス

インストールされている拡張（Extension）を確認する

select * from pg_available_extensions;

ログの設定（デフォルトから変更しておいたほうが良い項目）

設定項目の詳細は以下を参照。

PostgreSQL 11.5文書 19.8. エラー報告とログ取得

# 原因となったSQLを出力するログレベル
log_min_error_statement = warning

# ログ出力内容
log_line_prefix = '[%t][%p][%u][%d] '

# ログファイル名
log_filename = 'postgresql-%Y-%m-%d-%H-%M-%S.log'

# スロークエリをログに出力する（ミリ秒）
log_min_duration_statement = 500

# チェックポイントの情報を出力する
log_checkpoints = on

# ロック待ちの情報を出力する
log_lock_waits = on

# 一時ファイルの情報を出力する
log_temp_files = 0

# 設定した時間以上かかった自動VACUUMの情報を出力する
log_autovacuum_min_duration = 5000

# サーバへの接続試行がログに残す
log_connections = on

# クライアント接続の終了をログに残す
log_disconnections = on

参考

稼動統計情報を活用しよう by Let's Postgres
PGECons 2018年度WG3活動報告書性能トラブル調査編
［改訂新版］内部構造から学ぶPostgreSQL 設計・運用計画の鉄則
PostgreSQL徹底入門第4版
参考資料ダウンロード(LPI-JAPAN OSS-DB) OSS-DB Exam Gold技術解説無料セミナー

PostgreSQLの監視に便利なSQL一覧（随時更新中）