PostgreSQL 索引优化实战：从 EXPLAIN 到执行计划

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  user_id      BIGINT NOT NULL,
  product_id   BIGINT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending',  -- pending / paid / shipped / cancelled
  amount_cents BIGINT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 已有 500 万行数据
-- 业务需求：查询某个用户的已支付订单，按时间倒序

SELECT id, product_id, amount_cents
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, product_id, amount_cents
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=0.56..116.44 rows=20 width=24)
  ->  Index Scan Backward using orders_pkey on orders
        (cost=0.56..5802.94 rows=1002 width=24)
        Filter: ((user_id = 42) AND (status = 'paid'::text))
        Rows Removed by Filter: 4998998

cost=0.56..116.44   —— 估算成本，上下限
rows=1002           —— 估算返回行数（优化器的猜测）
Buffers: heap=...   —— 实际读了多少个 8KB 数据页

CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

Limit  (cost=0.42..2.64 rows=20 width=24)
  ->  Index Scan using idx_orders_user_status_created on orders
        (cost=0.42..11.54 rows=1002 width=24)
        Index Cond: ((user_id = 42) AND (status = 'paid'::text))

SELECT * FROM products WHERE name ILIKE '%折叠%';
-- 或数组字段
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

-- 对数组/全文搜索用 GIN
CREATE INDEX idx_articles_tags ON articles USING gin (tags);

-- 全文搜索
CREATE INDEX idx_articles_fts
  ON articles USING gin (to_tsvector('chinese', body));

CREATE INDEX idx_orders_user_covering
  ON orders (user_id, status, created_at DESC)
  INCLUDE (product_id, amount_cents);

-- 坏：无法使用 idx_orders_user_created 上的 created_at
WHERE date(created_at) = '2026-08-01'

-- 好：改成范围条件
WHERE created_at >= '2026-08-01'
  AND created_at <  '2026-08-02'

-- user_id 是 BIGINT，传字符串时隐式转文本类型比较
WHERE user_id = '42'

-- 有索引 (user_id, status, created_at)
-- 坏：跳过第一列直接查 status
WHERE status = 'paid' AND created_at > '2026-01-01'

-- 大批量写入/删除后，优化器还在用旧估算
ANALYZE orders;
-- 或开启自动 analyze

-- status 只有 4 个取值，'pending' 占 90% 的行
WHERE status = 'pending'

每加一个索引：
- INSERT/UPDATE/DELETE 都要同步维护索引 → 写入变慢
- 磁盘空间额外占用 → 5 百万行订单，每个索引约几十 MB
- VACUUM 和 autovacuum 负担加重 → 更新频繁的表更明显

-- 找出没被使用过的索引
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

-- 1. 必须重新查看执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- 2. 看三件事
--    a. 是否用上了新索引（Index Scan using xxx）
--    b. Buffers 读页数是否大幅下降
--    c. 实际耗时（ANALYZE 是真实执行）

-- 3. 跑同一查询两次，第二次看是否命中缓存
--    第一次：Buffers: shared read=...
--    第二次：Buffers: shared hit=...

1. 定位慢 SQL（pg_stat_statements / 日志）
2. 跑 EXPLAIN (ANALYZE, BUFFERS)
3. 盯住 Rows Removed / rows 估算
4. 判断瓶颈：过滤太多？排序？还是回表？
5. 选型：复合索引 / GIN / 覆盖索引 / 部分索引
6. 建索引，重新 EXPLAIN，对比 Buffers 和耗时
7. 上线后观察 pg_stat_user_indexes 确认被使用

-- 只给 1% 的异常订单建索引，索引体积和写入开销都极小
CREATE INDEX idx_orders_abnormal ON orders (created_at)
  WHERE status IN ('pending', 'processing');