sql-pro

SQL Pro

コアワークフロー

1. スキーマ分析 - データベース構造、インデックス、クエリパターン、パフォーマンスのボトルネックを確認します
2. 設計 - CTE、ウィンドウ関数、適切な結合を使用して、集合ベースの操作を作成します
3. 最適化 - 実行計画を分析し、カバリングインデックスを実装して、テーブルスキャンをなくします
4. 検証 - EXPLAIN ANALYZE を実行し、大きなテーブルでシーケンシャルスキャンが発生していないことを確認します。クエリが 100 ミリ秒未満の目標を満たさない場合は、先に進む前にインデックスの選択またはクエリの書き換えを繰り返します
5. ドキュメント化 - クエリの説明、インデックス選定の根拠、パフォーマンス指標を提示します

リファレンスガイド

状況に応じて詳細なガイダンスを読み込みます。

トピック	リファレンス	読み込むタイミング
クエリパターン	references/query-patterns.md	JOIN、CTE、サブクエリ、再帰クエリ
ウィンドウ関数	references/window-functions.md	ROW_NUMBER、RANK、LAG/LEAD、分析処理
最適化	references/optimization.md	EXPLAIN プラン、インデックス、統計、チューニング
データベース設計	references/database-design.md	正規化、キー、制約、スキーマ
方言の違い	references/dialect-differences.md	PostgreSQL、MySQL、SQL Server 固有の違い

クイックリファレンスの例

CTE パターン

-- Isolate expensive subquery logic for reuse and readability
WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
    WHERE status = 'completed'          -- filter early, before the join
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1;                           -- latest completed order per customer

ウィンドウ関数パターン

-- Running total and rank within partition — no self-join required
SELECT
    department_id,
    employee_id,
    salary,
    SUM(salary)  OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

EXPLAIN ANALYZE の解釈

-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

出力で確認すべき主な点：

• 大きなテーブルでの Seq Scan → インデックスを追加または修正します
• actual rows ≫ estimated rows → ANALYZE <table> を実行して統計情報を更新します
• Buffers: shared hit と read の比較 → read の数が多い場合、キャッシュまたはインデックスが不足していることを示します

最適化前後の例

-- BEFORE: correlated subquery, one execution per row (slow)
SELECT order_id,
       (SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
FROM orders o;

-- AFTER: single aggregation join (fast)
SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(quantity) AS item_count
    FROM order_items
    GROUP BY order_id
) agg ON agg.order_id = o.id;

-- Supporting covering index (includes all columns touched by the query)
CREATE INDEX idx_order_items_order_qty
    ON order_items (order_id)
    INCLUDE (quantity);

制約事項

必ず行うこと

• 最適化を推奨する前に、実行計画を分析します
• 行単位の処理より、集合ベースの操作を使用します
• クエリ実行の早い段階でフィルタリングを適用します（可能であれば結合の前に行います）
• 存在確認には COUNT より EXISTS を使用します
• 比較と集計では NULL を明示的に処理します
• 頻繁に実行されるクエリにはカバリングインデックスを作成します
• 本番環境規模のデータ量でテストします

行ってはいけないこと

• 本番クエリで SELECT * を使用します
• 集合ベースの操作で実現できる場合にカーソルを使用します
• 特定の方言を対象とする際に、プラットフォーム固有の最適化を無視します
• データ量とカーディナリティを考慮せずにソリューションを実装します

出力テンプレート

SQL ソリューションを実装する際は、次の内容を提示します。

1. インラインコメント付きの最適化済みクエリ
2. 必要なインデックスとその根拠
3. 実行計画の分析
4. パフォーマンス指標（最適化前／最適化後）
5. 該当する場合はプラットフォーム固有の注意事項

ドキュメント