PostgreSQL 索引失效？我用 pg_stat_statements + EXPLAIN 15 分钟定位了隐式类型转换

说实话，上周被这个问题坑了半天。

场景是这样的：线上一个接口突然变慢，P99 从 20ms 飙到了 800ms。我翻了一圈日志、看了 CPU、看了连接数——都没问题。最后把查询单独拉出来跑，索引居然没生效。

但 schema 里明明建了索引，EXPLAIN 一看，全表扫描。

一顿排查下来，罪魁祸首是一个字段做了隐式类型转换。这个坑估计不少人踩过，但每次踩都觉得离谱——明明 SQL 看起来很正常，索引就是不过去。

这篇文章就把整个排查过程和解决方案记下来，下次再遇到 5 分钟能收活。

---

背景：那个导致线上告警的慢查询

先交代下情况。生产库是 PostgreSQL 14，机器配置不差，8 核 32G，数据量大概 2000 万行。接口慢的是一张 orders 表，核心查询是这样的：

SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = #{user_id}
ORDER BY created_at DESC
LIMIT 20;

user_id 是 varchar(32)，查询时传的是整型参数。一开始我怀疑是数据问题，但跑了一下：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 123456;

看到的结果让我愣了半天——Seq Scan（全表扫描），而不是 Index Scan。

user_id 上明明有索引，怎么不用？

---

第一步：pg_stat_statements 快速定位可疑查询

先不急着猜，先用 pg_stat_statements 把最近的高频慢查询捞出来。正常情况下这个插件没开的话要先装：

-- 开启 pg_stat_statements（需要管理员权限）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查询耗时 Top 10
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

跑完一看，那个 orders 表的查询平均耗时 420ms，调用次数还特别频繁。单独拎出来测了下，确实是全表扫描。

---

第二步：EXPLAIN 细看执行计划

把查询扔进 EXPLAIN (ANALYZE, BUFFERS)：

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 123456;

输出很长，关键信息摘出来：

{
  "Plan": {
    "Node Type": "Seq Scan",
    "Relation Name": "orders",
    "Filter": "((user_id)::bigint = 123456)"
  }
}

注意这行：((user_id)::bigint = 123456)

PostgreSQL 把 user_id 做了类型转换！ varchar 转 bigint，所以即使有索引，索引列上的类型不匹配，索引直接失效。

验证一下：

-- 直接用字符串类型查询
EXPLAIN
SELECT * FROM orders WHERE user_id = '123456';
-- 结果：Index Scan using idx_orders_user_id on orders

-- 用整型参数查询（触发隐式转换）
EXPLAIN
SELECT * FROM orders WHERE user_id = 123456;
-- 结果：Seq Scan on orders

两个执行计划完全不一样。

---

第三步：pg_stat_statements 结合字段类型排查

问题清楚了：应用层传入的参数类型和数据库字段类型不一致。PostgreSQL 做了隐式类型转换，导致索引失效。

但应用代码里直接改了参数类型影响面太大，更好的做法是在数据库层处理——给查询加上显式类型转换，让索引能正常命中：

-- 显式 cast，索引能正常命中
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = '123456'::varchar
ORDER BY created_at DESC
LIMIT 20;

或者在应用层保证传入的始终是字符串类型，从根本上避免隐式转换。

---

验证：修复后的执行计划

加上 ::varchar 后再看执行计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = '123456'::varchar;

这次输出：

{
  "Plan": {
    "Node Type": "Index Scan",
    "Index Name": "idx_orders_user_id",
    "Buffers: shared hit=5"
  }
}

Index Scan，而且只扫了 5 个 Buffers。耗时从 420ms 降到了 3ms 以内。

---

脚本化：一键排查索引失效的隐藏原因

写了个脚本，可以快速检查哪些字段存在类型转换风险：

-- 查找有索引但查询时做了类型转换的字段
SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE tablename IN (
  SELECT relname FROM pg_stat_user_indexes
)
AND attname IN (
  SELECT column_name::text FROM information_schema.columns
  WHERE data_type IN ('character varying', 'text')
)
LIMIT 50;

另外推荐定期跑这个，监控索引使用情况：

-- 查看哪些索引从来没被用过
SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(i.relid) DESC;

---

写在最后

这次排查从告警到解决，大概 20 分钟出头。主要时间花在定位类型转换上——一旦知道原因，修复其实很快。

两个关键点：

1. 类型不一致是索引失效的重灾区。varchar + 整型参数、integer + 字符串参数，这类组合在应用层传入时很常见，但很容易被忽略。
2. pg_stat_statements + EXPLAIN 是标配。生产环境出问题，先用前者捞高频查询，再逐个 EXPLAIN，哪个不走索引一目了然。

如果你的查询也有类似问题，建议先把 EXPLAIN 跑一遍，看看有没有 :: 类型转换。5 分钟排查，换 400 倍性能提升，这事值得做。

---