LOWER() 関数がカラムに適用されると、既存のインデックスが使われなくなる。

原因: LOWER() 関数がemail カラムに適用されているため、UNIQUE INDEX が使えず Seq Scan になっている。

改善策:

-- 式インデックスを追加
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- クエリはそのまま
SELECT * FROM users WHERE LOWER(email) = 'tanaka@example.com';
-- → Index Scan using idx_users_lower_email
-- Execution Time: 0.05ms

代替策: アプリケーション側でメール登録時に小文字に正規化し、通常のインデックスを使う。

OFFSETが大きい場合の問題と、カーソルベースページネーションを検討しよう。

原因:

1. user_id にインデックスがない → Seq Scan
2. OFFSET 5000 で5,000行をスキップ → 無駄なI/O

改善策:

-- インデックス追加
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- カーソルベースページネーションに変更
SELECT * FROM orders
WHERE user_id = 12345
  AND created_at < '2024-03-15 10:30:00'  -- 前ページの最後のcreated_at
ORDER BY created_at DESC
LIMIT 20;
-- → Index Scan, Execution Time: 0.1ms

JOINカラムへのインデックス、WHERE条件のインデックス、マテリアライズドビューの活用を検討しよう。

原因: JOINカラムにインデックスがなく、大量のSeq Scanが発生。

改善策:

-- インデックス追加
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- さらに最適化: マテリアライズドビュー
CREATE MATERIALIZED VIEW monthly_category_sales AS
SELECT
  date_trunc('month', o.created_at) AS month,
  p.category,
  SUM(oi.quantity * oi.unit_price) AS total_sales,
  COUNT(DISTINCT o.id) AS order_count
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
GROUP BY date_trunc('month', o.created_at), p.category;

CREATE INDEX idx_mv_month ON monthly_category_sales(month);

-- 定期リフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_category_sales;

-- クエリ
SELECT category, SUM(total_sales), SUM(order_count)
FROM monthly_category_sales
WHERE month >= '2024-01-01' AND month < '2024-04-01'
GROUP BY category
ORDER BY SUM(total_sales) DESC;
-- Execution Time: 5ms

JOINで1クエリにまとめるか、IN句で2クエリにまとめることを検討しよう。

原因: ループ内で個別クエリを実行するN+1問題。

改善策:

-- 1クエリで取得
SELECT
  o.id AS order_id,
  o.status,
  o.total,
  oi.id AS item_id,
  oi.quantity,
  oi.unit_price,
  p.name AS product_name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'pending'
ORDER BY o.id
LIMIT 500;  -- 注文100件 × アイテム数を想定

// TypeScript: 2クエリに分割
const orders = await db.query(
  'SELECT * FROM orders WHERE status = $1 LIMIT 100',
  ['pending']
);

const orderIds = orders.map(o => o.id);
const items = await db.query(
  `SELECT oi.*, p.name AS product_name
   FROM order_items oi
   JOIN products p ON p.id = oi.product_id
   WHERE oi.order_id = ANY($1)`,
  [orderIds]
);

// メモリ上でグルーピング
const itemsByOrder = new Map<number, typeof items>();
for (const item of items) {
  const group = itemsByOrder.get(item.order_id) ?? [];
  group.push(item);
  itemsByOrder.set(item.order_id, group);
}
// 合計: 2回のクエリ

必要なインデックス:

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

複合インデックスのカラム順序と、部分インデックスの活用を検討しよう。

原因: インデックスがなく Seq Scan + ソートが発生。

改善策:

-- 複合インデックス（WHERE + ORDER BY をカバー）
CREATE INDEX idx_products_cat_price ON products(category, price)
WHERE stock > 0;  -- 部分インデックス: 在庫ありのみ

-- クエリはそのまま
SELECT * FROM products
WHERE stock > 0
  AND category = 'electronics'
ORDER BY price ASC
LIMIT 20;
-- → Index Scan, Execution Time: 0.1ms

DISTINCT の代わりに GROUP BY を検討し、インデックスの活用を考えよう。

原因: 500万行のSeq Scan + DISTINCT処理（ソートまたはHash）。

改善策:

-- インデックス追加
CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);

-- DISTINCT → GROUP BY に変更（オプティマイザが効率的に処理）
SELECT user_id
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01'
GROUP BY user_id;
-- → Index Only Scan, Execution Time: 50ms

-- さらに最適化が必要な場合: HyperLogLog 等の近似カウント