自治体ポータルにおける問い合わせ検索の遅延とアクセス統制
人口約40万人の自治体Eは、住民向けポータルの問い合わせ履歴を同じDBで管理している。履歴テーブルは3年前は約50万件で検索は0.3秒だったが、現在は約2,800万件で、夜間バッチの後は25秒かかることがある。実行計画を見ると履歴テーブルの全表走査が選ばれており、統計情報の最終更新は14か月前、自動統計収集は無効化されている。索引は（受付日時）と（担当者ID）の単独索引が2本ある。主な検索条件は「担当者ID = ? AND 受付日時 BETWEEN ? AND ? AND 状態 IN ('未対応','対応中')」で、状態は3値、担当者は約400名である。夜間バッチは履歴を一括で更新し、その後VACUUMは実行されていない。また、退職者のDBアカウントが12件残存しており、監査ログは取得していない。本番データを匿名化せずに開発環境へコピーしており、開発者は本番DBにも接続できる。
1. 検索が25秒かかる原因を、実行計画と統計情報の観点から説明し、対策の優先順位を述べよ。
Read the worked answer
原因：
- 統計情報が14か月前で、50万件だった頃の行数・分布のまま。2,800万件の実態と乖離しており、選択率の見積りが誤って全表走査が選ばれている。自動統計収集が無効なので放っておくと悪化する。
- バッチが大量更新した後にVACUUMが走っておらず、不要領域が膨らんで走査コストが上がっている。
- 単独索引しかなく、担当者IDと受付日時の組み合わせを効率よく絞れない。
優先順位：
- 統計情報を更新し（ANALYZE）、自動収集を有効化する。これだけで計画が戻る可能性がある。
- EXPLAIN (ANALYZE, BUFFERS) で見積り行数と実測行数の乖離、バッファヒット率を確認する。
- 複合索引を設計する。
- バッチ後のVACUUMを運用に組み込む。
- 古い履歴のアーカイブやパーティションで走査対象を減らす。
索引を闇雲に追加すると更新コストが増えるため、計測を先に行う。
2. 主な検索条件に対する複合索引の列順を、選択率と条件の種類を踏まえて設計せよ。不要な索引の整理にも触れること。
Read the worked answer
列順の考え方： 等価条件を先、範囲条件を後に置く。範囲条件を先に置くと、それ以降の列で絞り込めない。
- 担当者ID（約400名）は選択率が高く等価条件なので先頭。
- 受付日時は範囲条件なので2列目。
- 状態は IN で等価的に扱えるが3値で選択率が低く、2列目に置くと範囲条件の走査を妨げる。3列目か INCLUDE で持つ。
推奨： （担当者ID, 受付日時）の複合索引とし、状態や参照列は INCLUDE で付ける。これで担当者と期間で絞り、状態は索引上でフィルタできる。
既存索引の整理：
- （担当者ID）単独索引は複合索引の先頭列と重複するため削除候補。
- （受付日時）単独索引は、日付だけで絞る検索（月次の集計など）が実際にあるかを確認してから判断する。
- pg_stat_user_indexes などで使用回数を確認し、使われていない索引を落とす。索引はINSERTの性能とバッファを食う。
補足： 履歴が2,800万件まで増えたので、受付日時による月次パーティションも検討する。古いデータの削除が容易になり、プルーニングも効く。
3. 退職者アカウントの残存、監査ログの未取得、本番データの開発環境への持ち出しについて、優先して講じるべき統制を述べよ。
Read the worked answer
退職者アカウント（12件）：
- 人事システムの退職日を起点にアカウントを自動無効化する仕組みを作る。四半期ごとに棚卸しし、主管を情報システム部門と明確化する。
- 共有アカウントを禁止し、特権アカウントは作業時のみ付与する。
監査ログ：
- 誰が、いつ、どの住民データを、どの操作で参照・更新したかを記録する。個人情報の参照は特に対象とする。
- ログはDBサーバとは別のホストに追記型で保管し、DB管理者が消せないようにする。保管期間とレビュー体制を決める。
本番データの開発環境へのコピー：
- 直ちに停止し、匿名化・仮名化したデータか合成データに切り替える。氏名・住所・電話番号はマスキングし、結合キーは変換する。
- 持ち出しには申請と承認を必須にし、記録を残す。
権限設計：
- 開発者に本番DBへの接続権限を与えない。ロールを最小権限で設計し、職務分掌を守る。
- 接続経路の暗号化と、管理者操作のログ取得も併せて行う。
優先順位は、本番データの持ち出し停止（影響が最も大きい）→ 退職者アカウントの無効化 → 監査ログの取得開始 → 恒久的な仕組みづくり、の順とする。