俺のPostgreSQLのSQLスニペット

目次

はじめに

ローカルで​育てている、​SQlスニペットを​ネットの​海に​放流しておく。

スニペット紹介

indexの​作成・削除

12345
DROP INDEX index_name;

CREATE INDEX index_name ON table_name (col1 DESC);
CREATE INDEX index_name ON table_name (col1) WHERE col2 = 1;
CREATE INDEX index_name ON table_name (col1, col2) WHERE col3 = 0;

テンプラリテーブルの​作成・削除

123
DROP TABLE tmp_table_name;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table_name(col1 bigint NOT NULL, ....);

テーブル名と​論理名を​表示

テーブルの​物理名と​コメント​（論理名）を​一覧​表示します。

12345678
SELECT
	pg_stat_user_tables.relname AS table_name
	, pg_description.description AS table_comment
FROM pg_stat_user_tables
	LEFT JOIN pg_description
		ON pg_stat_user_tables.relid = pg_description.objoid
			AND pg_description.objsubid = 0 -- 0 が​テーブル
ORDER BY table_name;

外部​サーバの​一覧を​表示

FDW​（Foreign Data Wrapper）で​設定された​外部​サーバの​情報を​確認します。

12345678
SELECT
	srvname AS name
	, srvowner ::regrole AS owner
	, fdwname AS wrapper
	, srvoptions AS options
FROM pg_foreign_server
	JOIN pg_foreign_data_wrapper w
		ON w.oid = srvfdw;

大きな​インデックスの​確認

1MB以上の​インデックスを​サイズの​大きい順に​表示します。

123456789101112
SELECT *
FROM (
	SELECT
		indexrelname AS index_name
		, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
	FROM pg_stat_user_indexes
	WHERE schemaname = 'public'
) a
WHERE
	index_size NOT LIKE '%kB'
	AND index_size NOT LIKE '%bytes'
ORDER BY LEFT (index_size, 3) ::INTEGER DESC;

インデックスの​断片化率確認

デッドタプルの​割合を​確認し、​VACUUMや​REINDEXの​必要性を​判断します。
私は​結果の​読み方が​分からないので、​LLMに​教えて​もらっています。

1234
SELECT
	relname, n_dead_tup / n_live_tup ::FLOAT * 100 AS dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0;

REINDEXの​実行

1
REINDEX INDEX index_name;

実行中の​クエリ確認

現在実行中の​クエリと​その​状態を​確認します。

12345678910111213
SELECT
	query
	, pid
	, STATE
	, application_name
	, query_start
	, backend_start
	, state_change
	, *
FROM pg_stat_activity
WHERE
	AND pid <> pg_backend_pid()
ORDER BY query_start DESC NULLS LAST;

VACUUM判断

デッドタプルの​割合を​確認し、​VACUUMを​実行する​必要の​ある​テーブルを​特定します。

12345678910111213
SELECT
	relname
	, last_vacuum
	, last_autovacuum
	, n_live_tup
	, n_dead_tup
	, CASE n_dead_tup
		WHEN 0
			THEN 0
		ELSE round(n_dead_tup * 100 / (n_live_tup + n_dead_tup), 2)
	END AS ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

ロック確認

特定の​テーブルに​対する​ロック状態を​確認します。

1234567891011
SELECT
	locktype
	, relation ::regclass
	, page
	, tuple
	, virtualtransaction
	, pid
	, mode
	, granted
FROM pg_locks
WHERE relation = 'table_name' ::regclass;

プロセスの​強制終了

問題の​ある​プロセスを​キャンセルまたは​強制終了します。

123
SELECT pg_cancel_backend(pid);

SELECT pg_terminate_backend(pid);

インストール済み拡張機能

インストールされている​拡張機能を​確認します。

1
SELECT * FROM pg_extension;

未使用インデックスの​特定

作成後​一度も​使用されていない​非ユニークインデックスを​特定します。

123456789101112131415161718192021
SELECT
	s.relname AS table_name
	, indexrelname AS index_name
	, CASE
		WHEN i.indisunique
			THEN 'Y'
		ELSE 'N'
	END AS UNIQUE
	, idx_scan AS index_scans
	, idx_scan AS idx_tup_read
	, idx_scan AS idx_tup_fetch
	, pg_size_pretty(pg_relation_size(quote_ident(s.indexrelname) ::text)) AS index_size
FROM
	pg_catalog.pg_stat_user_indexes s
	, pg_index i
WHERE
	i.indexrelid = s.indexrelid
	AND s.idx_scan = 0
	AND s.idx_tup_read = 0
	AND s.idx_tup_fetch = 0
	AND i.indisunique <> 'y';

重複インデックスの​特定

同じ​カラムに​対して​複数の​インデックスが​存在する​無駄な​インデックスを​特定します。
クエリチューニングする​ときに​使う

1234567891011121314151617181920212223
SELECT
	indrelid ::regclass table_name
	, att.attname column_name
	, amname index_method
FROM
	pg_index i
	, pg_class c
	, pg_opclass o
	, pg_am a
	, pg_attribute att
WHERE
	o.oid = ALL (indclass)
	AND att.attnum = ANY (i.indkey)
	AND a.oid = o.opcmethod
	AND att.attrelid = c.oid
	AND c.oid = i.indrelid
GROUP BY
	table_name
	, att.attname
	, indclass
	, amname
	, indkey
HAVING count(*) > 1;

テーブルサイズ確認​（インデックス除く）

指定テーブルの​データ部分のみの​サイズを​確認します。

1
SELECT pg_size_pretty(pg_table_size('table_name'));

テーブルサイズ確認​（インデックス含む）

指定テーブルの​インデックスを​含めた​総サイズを​確認します。

1
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

全テーブルサイズ一覧

データベース内の​全テーブルを​サイズ順に​表示します。

12345
SELECT
	relname
	, (relpages / 1024 * 8192 / 1024) AS byte
FROM pg_class
ORDER BY byte DESC;

レプリケーション設定確認

レプリケーションテーブルの​一覧を​確認します。

12345
SELECT
	pubname AS publication_name
	, schemaname AS schema_name
	, tablename AS table_name
FROM pg_publication_tables;

統計情報取得

特定の​テーブル・カラムの​統計情報を​確認します。
クエリチューニングする​ときに​使う

1
SELECT * FROM pg_stats WHERE tablename = 'table_name' AND attname = 'col_name';

トランザクション状態の​確認

現在実行中の​トランザクションと​その​時間を​確認します。

12345678910111213
SELECT
	pid
	, usename
	, application_name
	, xact_start
	, NOW() - xact_start AS duration
	, state
	, query
FROM pg_stat_activity
WHERE
	xact_start IS NOT NULL
	AND pid <> pg_backend_pid()
ORDER BY xact_start;

ロングトランザクションの​検出

5分以上​継続している​トランザクションを​検出します。

1234567891011121314
SELECT
	pid
	, usename
	, application_name
	, xact_start
	, NOW() - xact_start AS duration
	, state
	, LEFT(query, 100) AS query_excerpt
FROM pg_stat_activity
WHERE
	xact_start IS NOT NULL
	AND NOW() - xact_start > interval '5 minutes'
	AND pid <> pg_backend_pid()
ORDER BY xact_start;

待機中の​トランザクション確認

ロック​待ちなどで​待機状態に​ある​トランザクションを​確認します。

1234567891011121314
SELECT
	pid
	, usename
	, application_name
	, wait_event_type
	, wait_event
	, state
	, backend_xid
	, backend_xmin
	, query
FROM pg_stat_activity
WHERE
	wait_event IS NOT NULL
	AND pid <> pg_backend_pid();

おわりに

今後も​便利な​スニペットが​増えて、覚えていたら追記していこうと​思います。

目次