PHP小码农

Appearance

避免全表扫描 ​

看到执行计划里的 Seq Scan 就着急加索引，是新手最常见的误区——顺序扫描本身不是问题，问题是在它不该出现的时候出现。本节先教你判断 Seq Scan 到底是「正确选择」还是「优化缺失」，再给出排查清单、索引类型选择表，以及部分索引、表达式索引、BRIN、仅索引扫描、CLUSTER、分区裁剪等一整套让查询真正少读数据的手段。

前置知识

阅读本节前，建议先了解：SELECT 写法优化

基础概念 ​

判断一个 Seq Scan 是否合理，要同时看三件事：表有多大、条件过滤掉多少行、读一页的成本是多少。这三件事分别对应统计信息、选择率和成本参数。

概念术语说明
顺序扫描Seq Scan逐页读取整表，读固定成本、无随机 IO
索引扫描Index Scan按索引定位，随机回表，适合低选择率
仅索引扫描Index Only Scan所需列全在索引中，可免回表
位图扫描Bitmap Heap Scan先按索引建位图，再按物理顺序批量回表
选择率Selectivity满足条件的行占全表比例，越低越适合索引
可索引条件Sargable条件能被索引直接利用，不破坏列的原值
完全可见映射Visibility Map (VM)标记全可见页，Index Only Scan 的前提
部分索引Partial Index只对满足条件的行建索引，索引更小
表达式索引Expression Index对函数结果建索引，用于列上套函数的查询
覆盖索引Covering Index用 INCLUDE 附带列，避免回表
块范围索引BRIN按物理块区间存摘要，适合物理有序的超大表
统计信息Statistics优化器估算选择率的依据，由 ANALYZE 更新

Seq Scan 何时是正确的：

场景原因示例
小表全部数据只占几页，索引反而更慢配置表、字典表（< 几千行）
高选择率条件命中行数占比大，回表随机 IO 得不偿失WHERE status <> 'done'（60% 命中）
无可用索引条件不在任何索引的前缀上无索引列上的 =
需要对全表做聚合无论如何都要读全表SELECT count(*) FROM orders
宽范围统计目的是扫全表做汇总月度全量对账

真正要解决的场景排查清单：

排查项检查方法典型表现
统计信息是否过期对比 pg_class.reltuples 与实际行数估算 rows=1 但实际 80 万行
条件是否可索引看计划里的 Filter: 是否含函数/表达式Filter: (date(created_at) = ...)
是否有隐式类型转换计划中出现 (col)::typeFilter: ((order_no)::text = ...)
是否存在 OR 跨列条件形如 a = 1 OR b = 2退化为 Seq Scan + Filter
是否前置通配符条件形如 LIKE '%张'无索引可用
索引是否已建/是否失效\d tablename 查看索引列表压根没建索引
成本参数是否偏高检查 random_page_cost远大于 seq_page_cost 时不走索引
排序是否缺索引ORDER BY ... LIMIT 无对应索引Seq Scan + top-N heapsort

成本参数的默认值及其影响：

参数默认值含义调优方向
seq_page_cost1.0顺序读一页的相对成本一般保持默认
random_page_cost4.0随机读一页的相对成本SSD 可降到 1.1~1.5
effective_cache_size视内存而定估算可用系统缓存设为物理内存的 50%~75%
cpu_tuple_cost0.01处理一行的 CPU 成本一般保持默认
default_statistics_target100直方图桶数，影响估算精度倾斜列可调高

random_page_cost / seq_page_cost 的比值决定了优化器「多愿意」走索引。机械盘该比值约 4，SSD 上随机读几乎不慢于顺序读，把 random_page_cost 降到 1.1 往往能让优化器开始选择索引扫描。

语法与用法 ​

标准流程：先用统计信息判断选择率，再看计划，最后决定加什么索引。

sql
-- 第 1 步：表有多大、多少页、有多少行
SELECT
  c.relname,
  c.relpages  AS 页数,          -- 估算占用页数
  c.reltuples AS 估算行数,
  pg_size_pretty(pg_relation_size(c.oid)) AS 表大小
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'orders';
sql
-- 第 2 步：各列的选择率与不同值个数
SELECT
  attname       AS 列名,
  n_distinct    AS 不同值数,     -- 负数表示占全表比例（-1 = 全唯一）
  null_frac     AS 空值比例,
  correlation   AS 物理相关性,   -- 接近 ±1 表示物理有序
  most_common_vals AS 高频值
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';
sql
-- 第 3 步：看计划，确认是「该 Seq Scan」还是「优化缺失」
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE status = 'pending';

准备本节贯穿使用的示例表（在 demo 库中执行）：

sql
-- 日志表：800 万行，物理上按 created_at 有序写入，适合演示 BRIN
CREATE TABLE access_log (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_email  TEXT        NOT NULL,
  url         TEXT        NOT NULL,
  status_code SMALLINT    NOT NULL,
  is_bot      BOOLEAN     NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL
);

INSERT INTO access_log (user_email, url, status_code, is_bot, created_at)
SELECT
  'User' || (i % 20000) || '@Example.COM',
  (ARRAY['/home', '/order/list', '/order/detail', '/api/user', '/api/pay'])[1 + (i % 5)],
  (ARRAY[200, 200, 200, 301, 404, 500])[1 + (i % 6)],
  i % 11 = 0,
  -- 关键：按单调递增时间写入，物理顺序与逻辑顺序一致
  TIMESTAMPTZ '2025-01-01 00:00:00+08' + (i * INTERVAL '1 second')
FROM generate_series(1, 8000000) AS i;

ANALYZE access_log;
text
-- 800 万行写入完成后，表规模大致如下
SELECT pg_size_pretty(pg_relation_size('access_log'));
 pg_size_pretty
----------------
 546 MB
(1 row)

最小可运行对照示例：

sql
-- 低选择率（命中 0.01%）：应该走索引
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log WHERE user_email = 'user777@example.com';

-- 高选择率（命中 99.9%）：Seq Scan 才是正确的
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM access_log WHERE status_code <> 500;
text
-- 第 1 条：单行定位，走索引
 Index Scan using idx_log_email on access_log
   Index Cond: (user_email = 'user777@example.com'::text)
   Buffers: shared hit=4
 Execution Time: 0.038 ms

-- 第 2 条：几乎全表命中，Seq Scan 正确
 Aggregate
   ->  Seq Scan on access_log
         Filter: (status_code <> 500)
         Rows Removed by Filter: 1333334
         Buffers: shared hit=2184 read=67510
 Execution Time: 1184.220 ms

详细说明 ​

判断依据：用统计信息代替感觉 ​

优化器的选择完全建立在统计信息上，所以排查第一步永远是看估算是否符合现实。pg_class.reltuples 是上次 ANALYZE 时采集的估算行数；如果它和实际行数差一个数量级，优化器必然选错计划。

sql
-- 估算行数 vs 实际行数
SELECT
  c.reltuples::bigint AS 估算行数,
  (SELECT count(*) FROM access_log) AS 实际行数
FROM pg_class c
WHERE c.relname = 'access_log';
text
 估算行数 | 实际行数
----------+----------
  8000000 |  8000000
(1 row)
-- 两者接近，说明统计信息新鲜
sql
-- 倾斜列的选择率：status_code 只有 6 个不同值
SELECT
  attname,
  n_distinct,
  array_to_string(most_common_vals, ',') AS 高频值,
  array_to_string(most_common_freqs, ',') AS 高频频率
FROM pg_stats
WHERE tablename = 'access_log' AND attname = 'status_code';
text
   attname    | n_distinct | 高频值         | 高频频率
--------------+------------+----------------+--------------------------------
 status_code  |          6 | {200,404,301,500,} | {0.5003,0.1667,0.1666,0.1666,0.05}
(1 row)

看到 200 的频率是 0.5 就该明白：按 status_code = 200 查是 50% 选择率，不该走索引；而 status_code = 500 约 16.7%，索引通常更优。

真正需要解决的场景：列上套函数 ​

最常见的「明明有索引却全表扫」原因是列上套了函数，使得条件不可索引。表达式索引或改写条件都能解决。

sql
-- 需求：按邮箱精确查找（忽略大小写）
-- 错误！lower() 破坏了 user_email 索引的可用性
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log WHERE lower(user_email) = 'user777@example.com';
text
 Aggregate  (actual time=1180.114..1180.115 rows=1 loops=1)
   Buffers: shared hit=2184 read=67510
   ->  Seq Scan on access_log  (actual time=0.014..1147.882 rows=364 loops=1)
         Filter: (lower(user_email) = 'user777@example.com'::text)
         Rows Removed by Filter: 7999636
 Execution Time: 1180.440 ms
sql
-- 方案一：建表达式索引（对 lower(user_email) 建索引）
CREATE INDEX idx_log_email_lower ON access_log (lower(user_email));
ANALYZE access_log;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log WHERE lower(user_email) = 'user777@example.com';
text
 Index Scan using idx_log_email_lower on access_log
   Index Cond: (lower(user_email) = 'user777@example.com'::text)
   Buffers: shared hit=6
 Execution Time: 0.041 ms   -- 原 1180.4 ms
sql
-- 方案二：用 CITEXT 扩展从类型层面解决（需先 CREATE EXTENSION citext）
-- 或写入时统一归一化为小写，查询直接等值匹配
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log WHERE user_email = 'user777@example.com';

真正需要解决的场景：隐式类型转换 ​

当字面量类型与列类型不一致时，PostgreSQL 可能把列转换为字面量的类型，导致索引失效。计划里出现 (col)::type 就是明确信号。

sql
-- status_code 是 smallint，字面量给了整数 200（int4）
-- 恰好可以安全转换字面量，索引仍可用
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM access_log WHERE status_code = 200;
text
 Aggregate
   ->  Index Only Scan using idx_log_status on access_log
         Index Cond: (status_code = 200)
         Buffers: shared hit=1123
 Execution Time: 86.121 ms
sql
-- 危险写法：比较两列类型不一致，或列参与了表达式
-- 例如 VARCHAR 列与数字比较、DATE 与 TIMESTAMPTZ 混用
-- 计划中出现 (col)::type 表示列被转换，索引将不可用
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*) FROM access_log WHERE user_email = 12345;   -- 类型完全不匹配，会报错
text
ERROR:  invalid input syntax for type text: ...
-- 类型完全不兼容时直接报错，反而比静默转换安全

排查口诀：看到 Filter: 里的条件不是「列 直接 匹配 常量」，就要怀疑索引没被用上。

真正需要解决的场景：OR 跨列 ​

OR 连接不同列时，单一索引无法覆盖，优化器只能全表扫描或在多个索引间做 BitmapOr。改写为 UNION ALL 常常能让每一支都走索引。

sql
-- 原始：OR 跨列
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log
WHERE user_email = 'user777@example.com' OR url = '/api/pay';
text
 Seq Scan on access_log
   Filter: ((user_email = 'user777@example.com'::text) OR (url = '/api/pay'::text))
   Rows Removed by Filter: 7999410
   Buffers: shared hit=2184 read=67510
 Execution Time: 1163.870 ms
sql
-- 改写后：拆 UNION ALL（注意排除重叠条件，此处两列互斥语义由业务保证）
SELECT id, url FROM access_log WHERE user_email = 'user777@example.com'
UNION ALL
SELECT id, url FROM access_log WHERE url = '/api/pay' AND user_email <> 'user777@example.com';
text
 Append
   ->  Index Scan using idx_log_email on access_log
         Index Cond: (user_email = 'user777@example.com'::text)
   ->  Bitmap Heap Scan on access_log
         Recheck Cond: (url = '/api/pay'::text)
         Filter: (user_email <> 'user777@example.com'::text)
         ->  Bitmap Index Scan on idx_log_url
 Execution Time: 24.118 ms   -- 原 1163.9 ms

注意

拆 UNION ALL 时必须保证两支结果不重叠，否则会产生重复行。若无法保证互斥，改用 UNION（有去重成本）或保留 OR。

索引类型选择表 ​

选错索引类型和没建索引一样无效。下表给出各索引类型的适用场景：

索引类型适用场景典型示例备注
B-tree等值、范围、排序、前缀匹配WHERE created_at >= ...默认类型，覆盖 90% 场景
Hash仅等值WHERE token = '...'不支持范围与排序
GIN数组、JSONB、全文、三元组tags @> ARRAY['a']写入较慢，适合读多写少
GiST几何、范围、最近邻point <-> point支持 KNN 排序
BRIN物理有序的超大表created_at 单调递增索引极小，粒度粗
部分索引只查子集的业务WHERE is_bot = false索引显著变小
表达式索引列上套函数的查询lower(email)查询必须写同样表达式
覆盖索引免回表INCLUDE (amount)配合 Index Only Scan
sql
-- 各类型创建语法对照
CREATE INDEX idx_btree   ON access_log (created_at);              -- B-tree（默认）
CREATE INDEX idx_hash    ON access_log USING hash (user_email);   -- Hash，仅等值
CREATE INDEX idx_gin     ON access_log USING gin (to_tsvector('simple', url));  -- 全文
CREATE INDEX idx_brin    ON access_log USING brin (created_at);   -- BRIN
CREATE INDEX idx_partial ON access_log (created_at) WHERE is_bot = false;  -- 部分索引
CREATE INDEX idx_expr    ON access_log (lower(user_email));       -- 表达式索引
CREATE INDEX idx_cover   ON access_log (status_code) INCLUDE (url);  -- 覆盖索引

部分索引实战 ​

业务上「高频查询只针对某个子集」时，部分索引能把索引体积降下来，同时提升命中效率。经典场景：软删除表只查未删除数据。

sql
-- 需求：99% 的查询只关心非机器人流量
-- 常规索引
CREATE INDEX idx_log_created_all ON access_log (created_at);

-- 部分索引：只索引 is_bot = false 的行（约占 91%）
CREATE INDEX idx_log_created_human ON access_log (created_at) WHERE is_bot = false;
sql
-- 查询必须带上同样的谓词，才能匹配部分索引
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log
WHERE is_bot = false
  AND created_at >= TIMESTAMPTZ '2025-03-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2025-03-02 00:00:00+08';
text
 Index Scan using idx_log_created_human on access_log
   Index Cond: ((created_at >= ...) AND (created_at < ...))
   Buffers: shared hit=213 read=4143
 Execution Time: 62.114 ms
sql
-- 对比索引体积：部分索引明显更小
SELECT
  indexrelname AS 索引名,
  pg_size_pretty(pg_relation_size(indexrelid)) AS 大小
FROM pg_stat_user_indexes
WHERE relname = 'access_log' AND indexrelname LIKE 'idx_log_created%';
text
        索引名         |  大小
-----------------------+---------
 idx_log_created_all   | 179 MB
 idx_log_created_human | 163 MB
(2 rows)
sql
-- 经典用法：软删除表只索引未删除行
-- CREATE INDEX idx_active_user ON users (email) WHERE deleted_at IS NULL;

BRIN：物理有序大表的利器 ​

BRIN 索引为每个「块范围」记录最小/最大值摘要，体积极小（通常是表大小的千分之一量级）。前提是列的物理顺序与逻辑顺序相关（pg_stats.correlation 接近 ±1）。

sql
-- 检查物理相关性
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'access_log' AND attname = 'created_at';
text
   attname   | correlation
-------------+-------------
 created_at  |           1
(1 row)
sql
-- 相关性为 1（物理完全有序），BRIN 是最佳选择
CREATE INDEX idx_log_created_brin
  ON access_log USING brin (created_at) WITH (pages_per_range = 128);
sql
-- BRIN vs B-tree：体积对比
SELECT
  indexrelname AS 索引名,
  pg_size_pretty(pg_relation_size(indexrelid)) AS 大小
FROM pg_stat_user_indexes
WHERE relname = 'access_log' AND indexrelname LIKE '%brin%';
text
       索引名       | 大小
---------------------+-------
 idx_log_created_brin | 1048 kB   -- 仅约 1 MB，B-tree 为 179 MB
(1 row)
sql
-- 查询仍需读取所有候选块再逐行 Recheck，但候选块已被剪枝
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url FROM access_log
WHERE created_at >= TIMESTAMPTZ '2025-03-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2025-03-01 01:00:00+08';
text
 Bitmap Heap Scan on access_log
   Recheck Cond: ((created_at >= ...) AND (created_at < ...))
   Rows Removed by Index Recheck: 0
   Heap Blocks: lossy=451
   ->  Bitmap Index Scan on idx_log_created_brin
   Buffers: shared hit=455
 Execution Time: 8.114 ms
特性B-treeBRIN
索引体积大（约表的 30%）极小（约表的 0.2%）
精度精确定位块范围粗过滤 + Recheck
物理有序要求无必须（correlation 接近 ±1）
适用规模通用数十 GB 以上的时序/日志表
维护成本随表线性增长几乎可忽略

Index Only Scan 的前置条件 ​

Index Only Scan 免回表，是最快的访问方式，但它有硬性前提：索引必须包含查询所需的全部列，且对应页在可见性映射（VM）中被标记为全可见——这依赖 VACUUM。

sql
-- 建覆盖索引
CREATE INDEX idx_log_status_url ON access_log (status_code) INCLUDE (url);
sql
-- 未 VACUUM：VM 为空，仍需回表，Heap Fetches 很高
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT status_code, url FROM access_log WHERE status_code = 404;
text
 Index Only Scan using idx_log_status_url on access_log
   Index Cond: (status_code = 404)
   Heap Fetches: 1333334      -- 全部回表，「仅索引」形同虚设
   Buffers: shared hit=4218 read=14918
 Execution Time: 402.114 ms
sql
-- 执行 VACUUM 更新可见性映射
VACUUM (ANALYZE) access_log;
sql
-- VACUUM 后：Heap Fetches 归零，真正免回表
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT status_code, url FROM access_log WHERE status_code = 404;
text
 Index Only Scan using idx_log_status_url on access_log
   Index Cond: (status_code = 404)
   Heap Fetches: 0            -- 零回表
   Buffers: shared hit=4372
 Execution Time: 168.331 ms   -- 原 402.1 ms
sql
-- 查看可见性映射覆盖率
SELECT
  relname,
  pg_size_pretty(pg_relation_size(relname::regclass)) AS 表大小,
  pg_size_pretty(pg_relation_size(relname::regclass + '_vm'::text::regclass)) AS vm大小
FROM pg_class WHERE relname = 'access_log';

CLUSTER、分区裁剪与物化视图 ​

当索引仍不够用，可以从「物理组织」和「数据裁剪」两个层面继续优化。

CLUSTER：按索引重排物理顺序，让范围查询连续读页、并让 BRIN / Index Only Scan 生效。

sql
-- 按创建时间重排物理顺序（会持有排他锁，需在维护窗口执行）
CLUSTER access_log USING idx_log_created_all;
ANALYZE access_log;

-- 查看相关性是否改善
SELECT attname, correlation FROM pg_stats
WHERE tablename = 'access_log' AND attname = 'created_at';
text
   attname   | correlation
-------------+-------------
 created_at  |           1
(1 row)

分区裁剪：（Partition Pruning） 把大表按时间分区，查询带分区键时只扫描命中的分区。

sql
-- 按月分区的大表
CREATE TABLE access_log_p (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  url         TEXT        NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE access_log_p_2025_01 PARTITION OF access_log_p
  FOR VALUES FROM ('2025-01-01+08') TO ('2025-02-01+08');
CREATE TABLE access_log_p_2025_02 PARTITION OF access_log_p
  FOR VALUES FROM ('2025-02-01+08') TO ('2025-03-01+08');
sql
-- 计划中只出现命中的分区，其余被裁剪掉
EXPLAIN (COSTS OFF)
SELECT count(*) FROM access_log_p
WHERE created_at >= TIMESTAMPTZ '2025-02-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2025-03-01 00:00:00+08';
text
 Aggregate
   ->  Seq Scan on access_log_p_2025_02 access_log_p
         Filter: ((created_at >= ...) AND (created_at < ...))
-- 只有 2025_02 分区被扫描，2025_01 被裁剪

物化视图： 把昂贵的聚合结果预先算好、定时刷新，把「每次查询都全表聚合」变成「读一张小结果表」。

sql
CREATE MATERIALIZED VIEW mv_log_hourly AS
SELECT
  date_trunc('hour', created_at) AS 小时,
  status_code,
  count(*) AS 请求数
FROM access_log
GROUP BY 1, 2
WITH DATA;

CREATE UNIQUE INDEX ON mv_log_hourly (小时, status_code);   -- 支持 CONCURRENTLY 刷新
sql
-- 刷新（不阻塞读）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_log_hourly;

实战示例 ​

步骤 1：完整排查一条慢查询 ​

sql
-- 慢查询：按 URL 前缀 + 时间范围统计
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM access_log
WHERE url LIKE '/api/%'
  AND created_at >= TIMESTAMPTZ '2025-06-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2025-06-02 00:00:00+08';
text
 Aggregate  (actual time=1102.114..1102.115 rows=1 loops=1)
   Buffers: shared hit=2184 read=67510
   ->  Seq Scan on access_log  (actual time=0.012..1094.221 rows=191982 loops=1)
         Filter: ((url ~~ '/api/%'::text) AND (created_at >= ...) AND (created_at < ...))
         Rows Removed by Filter: 7808018
 Execution Time: 1102.440 ms

诊断：url LIKE '/api/%' 是前缀匹配（非前置通配符），理论上可用 B-tree + text_pattern_ops；created_at 有 B-tree 索引但选择率 1/365 也可用。优化器却选了 Seq Scan，说明估算的选择率偏高。

sql
-- 步骤 1a：确认统计信息是否过期
SELECT reltuples::bigint AS 估算行数 FROM pg_class WHERE relname = 'access_log';

-- 步骤 1b：刷新统计信息
ANALYZE access_log;
sql
-- 步骤 1c：建立支持前缀匹配的索引（text_pattern_ops 才能加速 LIKE 'x%'）
CREATE INDEX idx_log_url_pattern ON access_log (url text_pattern_ops);

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM access_log
WHERE url LIKE '/api/%'
  AND created_at >= TIMESTAMPTZ '2025-06-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2025-06-02 00:00:00+08';
text
 Aggregate
   ->  Bitmap Heap Scan on access_log
         Recheck Cond: ((url ~~ '/api/%'::text) AND (created_at >= ...))
         ->  BitmapAnd
               ->  Bitmap Index Scan on idx_log_url_pattern
                     Index Cond: ((url ~>=~ '/api/'::text) AND (url ~<~ '/api0'::text))
               ->  Bitmap Index Scan on idx_log_created_all
                     Index Cond: ((created_at >= ...) AND (created_at < ...))
         Buffers: shared hit=15 read=5218
 Execution Time: 34.221 ms   -- 原 1102.4 ms，提升 32×

步骤 2：把 Seq Scan 改造成 Index Only Scan ​

sql
-- 需求：统计各状态码请求数（只用到 status_code 一列）
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT status_code, count(*) FROM access_log GROUP BY status_code;
text
 HashAggregate
   Group Key: status_code
   Buffers: shared hit=2184 read=67510
   ->  Seq Scan on access_log
         Buffers: shared hit=2184 read=67510
 Execution Time: 1211.882 ms
sql
-- 建立只含 status_code 的窄索引，再 VACUUM 更新 VM
CREATE INDEX idx_log_status_only ON access_log (status_code);
VACUUM (ANALYZE) access_log;
sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT status_code, count(*) FROM access_log GROUP BY status_code;
text
 HashAggregate
   Group Key: status_code
   ->  Index Only Scan using idx_log_status_only on access_log
         Heap Fetches: 0
         Buffers: shared hit=11350
 Execution Time: 402.118 ms   -- 原 1211.9 ms，提升 3.0×
指标改造前改造后变化
执行时间1211.9 ms402.1 ms3.0× 提升
访问方式Seq Scan（69734 页）Index Only Scan（11350 页）页数减少 84%
Heap Fetches—0完全免回表

步骤 3：SSD 上调整 random_page_cost ​

sql
-- 默认 random_page_cost = 4.0 时，优化器可能不愿走索引
SHOW random_page_cost;
text
 random_page_cost
------------------
 4
(1 row)
sql
-- SSD 环境下按会话降低该值，观察计划变化
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, url, created_at FROM access_log
WHERE created_at >= TIMESTAMPTZ '2025-08-01 00:00:00+08'
  AND created_at <  TIMESTAMPTZ '2025-08-02 00:00:00+08';
text
 Index Scan using idx_log_created_all on access_log
   Index Cond: ((created_at >= ...) AND (created_at < ...))
   Buffers: shared hit=2192 read=13
 Execution Time: 18.442 ms   -- 默认参数下为 Seq Scan，1180 ms
sql
-- 确认有效后全局固化（需重启或 reload 生效）
-- ALTER SYSTEM SET random_page_cost = 1.1;
-- SELECT pg_reload_conf();

步骤 4：对比强制走索引是否真的更快 ​

sql
-- 基线：高选择率条件，Seq Scan
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM access_log WHERE is_bot = false;
text
 Aggregate
   ->  Seq Scan on access_log
         Filter: (NOT is_bot)
         Rows Removed by Filter: 727273
         Buffers: shared hit=2184 read=67510
 Execution Time: 1118.441 ms
sql
-- 强制走索引，用「代价」证明 Seq Scan 的正确性
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM access_log WHERE is_bot = false;
text
 Aggregate
   ->  Bitmap Heap Scan on access_log
         Recheck Cond: (NOT is_bot)
         Heap Blocks: exact=69723
         ->  Bitmap Index Scan on idx_log_is_bot
         Buffers: shared hit=10 read=126178
 Execution Time: 1738.229 ms   -- 比 Seq Scan 慢 1.55×
sql
-- 诊断完毕，恢复默认
RESET enable_seqscan;

注意事项 ​

  1. Seq Scan 不是错误，先看命中行数占比，再决定是否需要干预：
    sql
    -- 命中 91% 的行，Seq Scan 是正确的
SELECT count(*) FROM access_log WHERE is_bot = false;
  2. 不要在索引列上套函数，否则索引直接失效；用表达式索引或改写条件：
    sql
    -- 错误！索引无法匹配
SELECT * FROM access_log WHERE lower(user_email) = 'a@b.com';
  3. 字面量类型要与列类型一致，看到计划里出现 (col)::type 就说明索引大概率没生效。
  4. LIKE 'x%' 走 B-tree 需要 text_pattern_ops，默认排序规则下优化器可能不用索引：
    sql
    -- 不推荐：默认 opclass 对 LIKE 前缀匹配支持受限
CREATE INDEX idx_bad ON access_log (url);
-- 正确
CREATE INDEX idx_good ON access_log (url text_pattern_ops);
  5. LIKE '%x%' 任何 B-tree 都救不了，需要 pg_trgm + GIN：
    sql
    -- 错误！前置通配符必定全表扫描
SELECT * FROM access_log WHERE url LIKE '%/pay%';
-- 正确：CREATE EXTENSION pg_trgm;
--        CREATE INDEX ... USING gin (url gin_trgm_ops);
  6. Index Only Scan 必须先 VACUUM，否则计划里 Heap Fetches 大于 0，等于白建索引。
  7. BRIN 只适合物理有序的列，correlation 接近 0 时 BRIN 完全无效，甚至会拖慢查询：
    sql
    -- 错误！随机分布列上建 BRIN 无意义
-- CREATE INDEX ... USING brin (user_email);
  8. 部分索引的谓词必须与查询条件一致才能被匹配，否则优化器不认：
    sql
    -- 错误！查询漏了 is_bot = false，无法使用 idx_log_created_human
SELECT * FROM access_log WHERE created_at >= '2025-03-01';
  9. CLUSTER 会持有排他锁且不自动维护，后续写入会打乱顺序，必须配合定时任务重跑。
  10. enable_seqscan = off 仅用于诊断，生产环境绝不可开启；测完立即 RESET。
  11. 调 random_page_cost 前先确认存储介质，机械盘保持 4.0 更稳妥，盲目调低会让优化器在慢速随机读上翻车。
  12. 索引不是越多越好，每个索引都会拖慢写入并占用空间；先删掉长期不被使用的索引：
    sql
    -- 找出从未被使用的索引
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY relname;

最佳实践 ​

  1. 先判断再动手：用 pg_class.reltuples、pg_stats.n_distinct、pg_stats.correlation 三个视图确认表规模、选择率和物理有序性，再决定加什么索引。
  2. 把 ANALYZE 当作第一步排查动作，统计信息过期是「计划选错」最高频的原因。
  3. 让条件保持 sargable：列单独在等号/范围左侧，函数与运算移到常量一侧，或对函数结果建表达式索引。
  4. 优先用 EXPLAIN (ANALYZE, BUFFERS) 而不是 EXPLAIN，只有真实执行才有 actual time 与 Buffers 可对比。
  5. 按访问模式选索引类型：范围与排序用 B-tree，等值用 Hash，数组/JSONB/全文用 GIN，超大有序列用 BRIN。
  6. 高频子集查询建部分索引，如 WHERE deleted_at IS NULL、WHERE is_bot = false，索引更小且更易缓存。
  7. 需要免回表就建覆盖索引 INCLUDE，并配合定时 VACUUM 让可见性映射生效。
  8. SSD 环境把 random_page_cost 调到 1.1~1.5，让优化器更愿意选择索引扫描；改动前先按会话验证。
  9. 超大数据量优先分区 + 分区裁剪，把「全表扫描」变成「只扫一个分区」。
  10. 重复执行的昂贵聚合用物化视图固化，并建唯一索引以支持 REFRESH ... CONCURRENTLY。
  11. 定期清理无用索引，用 pg_stat_user_indexes.idx_scan = 0 找出从未使用的索引并评估删除。
  12. 每次改动后用 EXPLAIN 前后对照 + EXCEPT 结果一致性校验，确认优化既变快又没改语义。

下一节

继续学习：分页优化（键集分页）

参考链接 ​