auto_explain / スロークエリログによる取得とセクション定義

# しきい値（ms）を超えたクエリをログに記録
# -1 = 無効, 0 = 全クエリ記録（負荷大）
log_min_duration_statement = 1000   # 1秒以上のクエリを記録

# ログ出力先（stderr / csvlog / jsonlog）
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 各行のプレフィックス形式
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# 実行計画をログに記録（後述の auto_explain で設定する方が推奨）
# log_min_duration_statement だけでは実行計画は記録されない

# セッション単位での一時的な変更（pg_reload_conf() 不要）
# SET log_min_duration_statement = 500;  -- 500ms 以上

2026-05-29 14:32:01.123 JST [12345]: [3-1] user=appuser,db=mydb,app=myapp,client=192.168.1.10
  LOG:  duration: 2345.678 ms  statement: SELECT u.user_id, u.name, COUNT(o.order_id) AS order_count
        FROM users u
        LEFT JOIN orders o ON u.user_id = o.user_id
        WHERE u.created_at >= '2025-01-01'
        GROUP BY u.user_id
        ORDER BY order_count DESC
        LIMIT 100;

# shared_preload_libraries に追加（再起動が必要）
shared_preload_libraries = 'auto_explain'

# しきい値（ms）を超えたクエリの実行計画をログに記録
auto_explain.log_min_duration = 1000   # 1秒以上

# ANALYZE 相当の実測値を記録（true 推奨）
auto_explain.log_analyze = on

# バッファ情報を記録（log_analyze = on と組み合わせ）
auto_explain.log_buffers = on

# 実行時間を記録
auto_explain.log_timing = on

# VERBOSE 相当の詳細出力
auto_explain.log_verbose = off   # ログが大量になるため通常は off

# EXPLAIN の出力形式
auto_explain.log_format = text   # text / json / xml / yaml

# ネストされたクエリ（関数内クエリなど）も記録
auto_explain.log_nested_statements = off

# サンプリング率（0.0 〜 1.0）：全件記録のオーバーヘッドを下げる（PostgreSQL 12+）
auto_explain.sample_rate = 1.0   # 1.0 = 100%（全件）

-- superuser のみ実行可能
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;   -- 全クエリ（テスト用）
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;

-- テスト実行
SELECT * FROM orders WHERE created_at >= '2026-01-01';

-- ログに実行計画が出力される

2026-05-29 14:32:01.123 JST [12345]: [3-1] user=appuser,db=mydb,app=myapp,client=192.168.1.10
  LOG:  duration: 2345.678 ms  plan:
        Query Text: SELECT u.user_id, u.name, COUNT(o.order_id)
                    FROM users u LEFT JOIN orders o ON u.user_id = o.user_id
                    WHERE u.created_at >= '2025-01-01'
                    GROUP BY u.user_id ORDER BY 3 DESC LIMIT 100
        Hash Join  (cost=1234.56..5678.90 rows=100 width=48)
                   (actual time=123.456..2340.123 rows=100 loops=1)
          Hash Cond: (o.user_id = u.user_id)
          Buffers: shared hit=12340 read=456
          ->  Seq Scan on orders o  (cost=0.00..3210.00 rows=100000 width=16)
                                     (actual time=0.012..890.234 rows=100000 loops=1)
              Filter: (created_at >= '2025-01-01'::date)
              Rows Removed by Filter: 50000
              Buffers: shared hit=8000 read=320
          ->  Hash  (cost=567.89..567.89 rows=50000 width=32)
                     (actual time=45.678..45.678 rows=50000 loops=1)
              Buckets: 65536  Batches: 1  Memory Usage: 3456kB
              Buffers: shared hit=4340 read=136
              ->  Index Scan using users_pkey on users u
                                     (cost=0.43..567.89 rows=50000 width=32)
                                     (actual time=0.034..32.456 rows=50000 loops=1)
                  Buffers: shared hit=4340 read=136
        Planning Time: 1.234 ms
        Execution Time: 2345.678 ms

# CSV 形式でログ出力（PostgreSQL 10+）
log_destination = 'csvlog'
logging_collector = on

# JSON 形式でログ出力（PostgreSQL 15+）
log_destination = 'jsonlog'
logging_collector = on

-- CSVログ読み込み用テーブル（PostgreSQL マニュアルより）
CREATE TABLE postgres_log (
    log_time               timestamp(3) with time zone,
    user_name              text,
    database_name          text,
    process_id             integer,
    connection_from        text,
    session_id             text,
    session_line_num       bigint,
    command_tag            text,
    session_start_time     timestamp with time zone,
    virtual_transaction_id text,
    transaction_id         bigint,
    error_severity         text,
    sql_state_code         text,
    message                text,
    detail                 text,
    hint                   text,
    internal_query         text,
    internal_query_pos     integer,
    context                text,
    query                  text,
    query_pos              integer,
    location               text,
    application_name       text,
    backend_type           text,
    leader_pid             integer,
    query_id               bigint
);

-- CSV ログファイルをインポート
COPY postgres_log FROM '/var/log/postgresql/postgresql-2026-05-29.csv'
WITH CSV;

-- スロークエリの検索
SELECT log_time, user_name, database_name, application_name,
       REGEXP_MATCH(message, 'duration: (\d+\.?\d*) ms') AS duration_ms,
       query
FROM postgres_log
WHERE message LIKE 'duration:%'
  AND CAST(REGEXP_REPLACE(message, 'duration: (\d+\.?\d*).*', '\1') AS numeric) >= 1000
ORDER BY log_time DESC;