postgres-query-expert

PostgreSQL クエリエキスパート

このスキルは、クエリの構築、最適化、スキーマ管理、システムのイントロスペクションを網羅した、PostgreSQL 16 の決定版リファレンスです。

手順

1. 一般的なクエリ標準

• 構文：ANSI SQL 標準に準拠します。ただし、より明確なロジックや優れたパフォーマンスが得られる場合は、PostgreSQL の拡張機能（例：DISTINCT ON、RETURNING、LATERAL、FILTER 句）を優先します。
• 識別子：すべての識別子に snake_case を使用します。識別子を引用符で囲む（"MyTable"）のは、どうしても必要な場合に限り、引用符なしの小文字名を優先します。
• 安全性：
  • パラメーター化：リテラル値には必ずパラメーター（$1、$2、…）を使用します。ユーザー入力を直接埋め込んではいけません。
  • タイムアウト：大規模なデータベースで探索的クエリを実行する場合は、先頭に SET LOCAL statement_timeout = '30s'; を追加します。
  • トランザクション：複数ステップの操作では、明示的な BEGIN および COMMIT ブロックを使用します。

2. パフォーマンスと最適化

• 実行計画：ボトルネックの診断には EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) を使用します。
• 注意すべき兆候：大規模なテーブルでの Seq Scan、高い Buffers: shared hit（RAM 使用量）、または Disk: read（I/O）。
• インデックス作成：用途に応じて適切なインデックスの種類を推奨します。
  • B-tree：標準的な等価・範囲（=、<、>）クエリ向け。
  • GIN：JSONB（@>）や配列（&&）などの複合型、および全文検索向け。
  • GiST：幾何データおよび範囲向け。
• CTE：可読性を高めるために共通テーブル式（WITH）を使用します。PG16 以降では、MATERIALIZED を指定しない限り、デフォルトで最適化（インライン化）されます。

イントロスペクション（エージェントの機能）

新しいデータベースを調査するときは、次のクエリを使用してスキーマを把握します。

すべてのテーブルを一覧表示

SELECT n.nspname AS schema,
       c.relname AS table,
       obj_description(c.oid) AS description
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

テーブルの列と型を取得

SELECT a.attname AS column,
       format_type(a.atttypid, a.atttypmod) AS type,
       a.attnotnull AS not_null,
       col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a
WHERE a.attrelid = 'public.target_table_name'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

リファレンス：データクエリ（DQL）

高度な集計

• Filter 句：count(*) FILTER (WHERE status = 'active')
• Grouping sets：GROUP BY GROUPING SETS ((brand), (brand, category), ())
• 任意の値：any_value(col)（PG16 以降）は、グループ内の任意の値を返します。

ウィンドウ関数

現在の行に関連するテーブル行の集合に対して計算を実行します。

SELECT dept,
       emp_no,
       salary,
       -- Rank employees by salary within department
       dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
       -- Running total of salaries
       sum(salary) OVER (
         PARTITION BY dept
         ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees;

パターンマッチング

• LIKE：col LIKE 'foo%'（単純なワイルドカード）。
• ILIKE：col ILIKE 'foo%'（大文字と小文字を区別しない）。
• SIMILAR TO：col SIMILAR TO '[a-c]%'（SQL 正規表現形式）。
• POSIX 正規表現：
  • 大文字と小文字を区別：col ~ '^[a-z]+$'
  • 大文字と小文字を区別しない：col ~* 'foo'

リファレンス：データ変更（DML）

MERGE（アップサート／条件付き操作）

結合条件に基づいて挿入、更新、削除を行う標準 SQL の方法です（PG15 以降）。

MERGE INTO wine_stock ws
USING wine_shipments s
  ON s.winery_id = ws.winery_id
 AND s.year = ws.year
WHEN MATCHED THEN
  UPDATE SET stock = ws.stock + s.count
WHEN NOT MATCHED THEN
  INSERT (winery_id, year, stock)
  VALUES (s.winery_id, s.year, s.count);

INSERT ... ON CONFLICT（従来のアップサート）

Postgres 固有の機能で、単純な一意キーの競合には、多くの場合こちらのほうが簡潔です。

INSERT INTO kv_store (key, value)
VALUES ('config', '{"a":1}')
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value;

RETURNING 句

変更された行のデータを直ちに返します。

DELETE FROM archived_logs
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING id, created_at;

リファレンス：特殊なデータ型

JSONB（バイナリ JSON）

保存とインデックス作成には json より jsonb を優先します。

演算子	説明	例
-> / ->>	要素を取得（JSON／テキスト）	data->'key'
@>	含有（インデックス使用可能）	data @> '{"tag": "urgent"}'
?	キーの存在確認	data ? 'error'
#-	パスを削除	data #- '{info, sensitive}'

SQL/JSON パス（PG12 以降）：

-- Find all items with price > 10
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 10)')
FROM orders;

配列

SELECT ARRAY[1,2,3];           -- Creation
SELECT (ARRAY[1,2,3])[1];      -- Access (1-based index)
SELECT 1 = ANY(arr_col);       -- Check if value exists in array
SELECT unnest(arr_col) FROM t; -- Expand array to rows

範囲型

スケジュール管理や有効期間の表現に便利です。

• tstzrange：タイムゾーン付きタイムスタンプの範囲。
• int4range、daterange：整数と日付の範囲。
• 重複演算子（&&）：2 つの範囲が重複するかどうかを確認します。

SELECT *
FROM reservations
WHERE duration && tstzrange('2023-01-01 10:00', '2023-01-01 12:00');

リファレンス：システム管理と統計情報

長時間実行中のクエリを終了

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
  AND query_start < NOW() - INTERVAL '5 minutes';

テーブルサイズを確認（ディスク使用量）

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS data_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

例

シナリオ 1：グラフ／ツリーデータ用の再帰 CTE

組織階層をたどります。

WITH RECURSIVE subordinates AS (
    -- Base case: the manager
    SELECT employee_id, manager_id, full_name, 0 AS level
    FROM employees
    WHERE employee_id = $1

    UNION ALL

    -- Recursive step: direct reports
    SELECT e.employee_id, e.manager_id, e.full_name, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT *
FROM subordinates;

シナリオ 2：「カテゴリごとの上位 N 件」を取得する Lateral Join

各ユーザーの最新の投稿 3 件を効率的に取得します。

SELECT u.username, p.title, p.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT title, created_at
    FROM posts
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) p
WHERE u.status = 'active';

シナリオ 3：ランキング付き全文検索

ブログテーブルを検索します。

SELECT id,
       title,
       ts_rank(to_tsvector('english', title || ' ' || content), query) AS rank
FROM articles,
     to_tsquery('english', 'postgres | optimization') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;