PostgreSQL 索引为什么没用上：10 道练习题实测，同一条 LIMIT 20 查询 625 ms 和 0.13 ms

先做题再看答案的话，可以只看第一列，把每条 SQL 放到自己的测试库里跑 EXPLAIN，猜一下为什么慢。完整的 30 行对比在样例 CSV 里。

表上只有 (tenant_id, created_at) 时，查 created_at 在 2026-03-01 这一天的行数（3040 行）。计划是 Index Only Scan using idx_tenant_created，但 Index Cond 只能拿 created_at 去整个索引里逐条比，读了 7665 个块，3 次分别 71.7 / 59.1 / 38.0 ms。它比全表扫描便宜，所以优化器还是选了这个索引，EXPLAIN 里看到 Index Only Scan 不代表索引用对了。

两种改法：加一个 (created_at) 单列索引（建索引 623 ms），12 个块，0.46–0.58 ms；或者业务上本来就有租户条件，带上 tenant_id = 7，只读 5 个块，0.10–0.17 ms。

和 MySQL 的区别：MySQL 8.0 有 skip scan（10-24 那篇的 Q1 实测过，只在不回表时出现）；PostgreSQL 直到 2025-09-25 发布的 18 才加入 B-tree skip scan。本次只测了 17.11，18 上的效果没有测，不下结论。

user_id 是 int。WHERE user_id = 4242.0 的计划里 Filter: ((user_id)::numeric = 4242.0)，转换发生在列这一侧，idx_user 用不上，Parallel Seq Scan 扫 37593 个块，78–81 ms。改成 user_id = 4242，Bitmap Index Scan，0.10–0.13 ms，返回同样 3 行。

更隐蔽的是参数类型：PREPARE p(numeric) AS … WHERE user_id = $1，传 4242 也一样全表扫，76.0 ms。应用里用 BigDecimal 或者 ORM 把整数当 numeric 绑定时，就是这个样子。排查时看计划里列名外面有没有 ::numeric、::text 这类转换。

反过来，PostgreSQL 不会把数字悄悄转成字符串：text 列 sku = 42 直接报 ERROR: operator does not exist: text = integer。MySQL 在这里会做隐式转换然后索引失效（10-21 那篇实测过），PostgreSQL 是直接报错，问题在开发阶段就会暴露。

Q3：表上有 email 的普通索引，WHERE lower(email) = 'user4242@example.com'，全表 208–218 ms。建表达式索引 CREATE INDEX … (lower(email))（1.64 s）后，Bitmap Index Scan on idx_email_lower，0.11–0.14 ms。表达式要和索引定义完全一样，写成 upper(email) 或者 lower(trim(email)) 都用不上。原来那个 idx_email 在本次所有查询里一次都没被用过，只读体检 SQL 第 3 段会把它列出来（21 MB，idx_scan = 0）。

Q4 是我在这台机器上没预料到的：数据库排序规则是 C.UTF-8（libc），不是 C。WHERE sku LIKE 'SKU0420%'，计划是 Parallel Index Only Scan using idx_sku，但 LIKE 只出现在 Filter 里，每个 worker 过滤掉 666533 行，等于把 14 MB 的索引扫了一遍，51–59 ms。PostgreSQL 只有在排序规则是 C / POSIX 时，才会把 LIKE 前缀转成索引范围条件。

两种改法：建 text_pattern_ops 索引（729 ms，14 MB），Index Only Scan using idx_sku_pattern，0.14–0.18 ms；或者不加索引，把 SQL 改成 sku >= 'SKU0420' AND sku < 'SKU0421'，用原来的 idx_sku，0.13 ms。两种都返回 400 行。en_US.UTF-8、zh_CN.UTF-8 和 ICU 排序规则都属于这种情况，可以先用体检 SQL 第 1 段看自己库的 datcollate。

Q5：user_id = 42 OR sku = 'SKU00042'，两列都有索引，计划是 BitmapOr，两个 Bitmap Index Scan 的结果合并，0.63–0.68 ms，50 行。换成 user_id = 42 OR amount = 99.99，amount 没有索引，整个 OR 只能全表，80–106 ms。我试着拆成 UNION，user_id 那一侧走了索引，但 amount 那一侧还是全表，74–79 ms，几乎没变。OR 的每一侧都得有能用的索引，拆 UNION 救不了没有索引的那一侧。

Q6：在 status 上建索引，status = 'paid' 占 60%，优化器选 Parallel Seq Scan，98–119 ms。我用 enable_seqscan = off 逼它走索引，变成 Bitmap Heap Scan，159–175 ms，反而慢了约 50%。status = 'pending' 只占 2.01%，走了索引，但仍要 59–80 ms：40204 行散落在 24849 个数据块里，整张表一共才 37593 个块。2% 的行占了 66% 的块，区分度看比例，也要看数据在物理上散得有多开。

这是 10 道题里差距最大的。只有 (created_at) 索引时，WHERE status = 'disputed' ORDER BY created_at DESC LIMIT 20，优化器估计 disputed 有 400 行（实际 383），按均匀分布算，倒着扫 idx_created 大约扫 10 万行就能凑够 20 行，所以选了 Index Scan Backward。实际 disputed 全在最早的 20 万行里，倒着扫要先穿过 2026 年和 2025 年后半段的全部数据：Rows Removed by Filter: 1811551，命中 117 万个缓冲块，592–625 ms。

建 (status, created_at) 组合索引（1.48 s）后，Index Cond 直接定位到 disputed，再倒序取 20 行，读 23 个块，0.11–0.16 ms，快了约 4000 倍。

线上遇到“同一条 LIMIT 查询，有的参数快、有的参数慢”，先看计划里是不是 Index Scan Backward + 大量 Rows Removed by Filter。改法是让过滤列排在排序列前面，组合索引 (过滤列, 排序列)。

建部分索引 CREATE INDEX … ON orders(created_at) WHERE status = 'pending'（146 ms，只有 896 kB）。WHERE status = 'pending' AND created_at >= '2026-09-01'，Index Only Scan using idx_pending_created，0.42–0.65 ms，3198 行。

改成预编译语句 PREPARE p(text, timestamptz) AS … WHERE status = $1 AND created_at >= $2。plan_cache_mode = force_custom_plan 时每次按实际参数规划，还是走部分索引，0.43 ms。force_generic_plan 时，计划必须对任意 $1 都成立，部分索引的条件 status = 'pending' 无法证明，于是改走 idx_created 再 Filter: (status = $1)，每个 worker 过滤掉 51481 行，16.7–18.4 ms，慢约 40 倍。

默认的 auto 会先用 5 次 custom plan，再比较成本决定要不要切到 generic plan，所以线上可能表现为“前几次快、后来变慢”。用部分索引时，让这个条件以字面量出现在 SQL 里，比依赖参数更稳。

WHERE created_at::date = '2026-03-01'，计划是扫整个 idx_created，Filter 里做转换，每个 worker 过滤掉 665653 行，62–71 ms。

我第一反应是建一个 (created_at::date) 表达式索引，直接报 ERROR: functions in index expression must be marked IMMUTABLE。原因是 created_at 是 timestamptz，转成 date 的结果取决于会话的 TimeZone 参数，同一行在 Asia/Shanghai 和 UTC 下可能是不同的日期，PostgreSQL 不允许这种表达式进索引。

两种改法：把时区写死，建 ((created_at AT TIME ZONE 'Asia/Shanghai')::date) 表达式索引（717 ms），查询也必须写成同样的表达式，0.59–0.75 ms；或者不建索引，改成带时区的半开区间 created_at >= '2026-03-01 00:00+08' AND created_at < '2026-03-02 00:00+08'，用现有的 idx_created，0.38–0.46 ms，两种都是 3040 行。我更推荐后者，少一个索引，也不会因为写法不一致用不上。

WHERE title LIKE '%a1b2c%'，btree 帮不上，全表 118–128 ms。CREATE EXTENSION pg_trgm 后建 GIN (title gin_trgm_ops)，Bitmap Index Scan on idx_title_trgm，1.9–2.1 ms，59 行（索引先给出 71 个候选，Recheck 去掉 12 个）。

代价要算清楚：这个 GIN 索引 202 MB，表本身才 294 MB；建索引用了 23.3 秒，之后每次写 title 都要更新它。另外，LIKE '%a1%' 只有 2 个字符，拼不出三元组，计划还是全表 129–134 ms，而且这个条件本身匹配了约 23 万行，走索引也不会快。搜索框类需求在上 pg_trgm 之前，先确认最短输入长度和匹配行数。

实验脚本会建删库、建扩展，只能在测试实例跑。到自己的库上，先跑这份只读索引体检 SQL（8 段，只有 SELECT 和会话级 statement_timeout，已过站内只读守卫测试）：数据库排序规则（Q4）；统计信息从什么时候开始累计；从没被扫描过的非唯一索引；顺序扫描多、每次扫很多行的表；表达式索引和部分索引的定义（Q3、Q8、Q9）；前导列相同的索引（Q1、Q7）；索引总大小和表大小之比（Q10）；GIN / GiST / BRIN 和 text_pattern_ops 索引。

在实验库上跑完 10 题后的结果：datcollate = C.UTF-8；idx_email 21 MB、idx_scan = 0；orders 索引 451 MB、表 294 MB，比值 1.53；前导列是 created_at 的索引有 2 个、是 sku 的有 2 个。“没用过”要结合统计周期和从库的使用情况判断，不要看到 idx_scan = 0 就直接删。