プログラミング

PostgreSQL便利コマンド

■Postgres設定値確認

set auto_explain.log_analyze = on;
SELECT * FROM pg_settings WHERE name like 'auto_explain.log_analyze';
SELECT * FROM pg_settings WHERE name like 'auto%';

■統計情報更新日の確認

SELECT * FROM pg_stat_all_tables WHERE relname = 'DB名'

■pg_classの確認

SELECT * FROM pg_class WHERE relname = 'DB名'

■実行中SQL確認

SELECT procpid, start, now() - start as lap, curent_query
FROM
(
SELECT
backendid,
pgstat_get_bakend_pid(S.backendid) as procpid,
pg_stat_get_backend_activity_start(S.backendid) as start,
pg_stat_getbackend_activity(S.backendid) as current_query
FROM
(SELECT pg_stat_get_backend_idset() as backendid) as s
) as S
WHERE current_query <> ''
ORDER BY lap DESC;

-プログラミング