题干与适用场景
orders 表有 2 亿行，并持续承受每秒 3,000 次插入或状态更新。全表约 2% 的订单处于 pending，但不同租户的占比有明显差异。运营后台以每秒 40 次的频率查询某个租户最近 30 天的待处理订单，要求按创建时间倒序返回前 50 条：
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
AND status = 'pending'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;表上已有 orders(tenantid) 和 orders(createdat) 两个单列 B-tree 索引。应用监控显示该查询的 p95 从 120 毫秒升到 2.8 秒，PostgreSQL 的 CPU、内存和连接数都未达到容量上限。一次在生产规模副本上执行的计划显示：优化器估算扫描 8,000 行，实际在排序前产生 420,000 行，读取或命中约 120,000 个共享缓冲块，最后用 top-N 排序取得 50 行。
本题限定 PostgreSQL 18。表规模、吞吐量和计划数字都是面试假设，用于验证推导是否闭环。目标是改善这条高频读取，同时控制索引构建风险、存储占用和写放大；分库分表、缓存和硬件扩容暂不进入首轮方案。
面试官考察点
第一项信号是先确认负载，再讨论 SQL。强回答会区分“单次最慢”和“累计消耗最大”：一个平均 80 毫秒、每秒调用 1,000 次的查询，可能比偶发的 5 秒查询更值得优先处理。pgstatstatements 提供调用次数、总执行时间和平均执行时间；p95 或租户分位数仍要从应用监控或追踪系统取得。
第二项信号是能否读懂执行计划中的因果链。这里的关键线索包括 8,000 与 420,000 的基数偏差、扫描节点输出的行数、每个节点的 loops、缓冲块读写、排序方式，以及过滤发生在索引条件还是扫描后的 Filter。只看到 Seq Scan 或“用了索引”都无法直接判定计划好坏。
第三项信号是从查询形状推导索引顺序。tenantid 是等值条件，createdat 同时承担范围过滤和排序；status='pending' 是固定且稀少的业务状态。推荐索引应让数据库定位一个租户的待处理区间，沿时间顺序读取，并在找到 50 条后尽早停止。
最后看验证和发布纪律。新增索引会占空间、消耗构建 I/O，并增加插入和状态变更成本。面试答案要给出生产规模数据上的前后计划、冷热缓存、不同租户、并发读写和回滚门槛，不能把一次本地执行更快当作完成。
回答前需要澄清的问题
- 慢的是哪一个统计口径？ 单租户 p95、全局 p95、平均值和总数据库时间会指向不同优先级。还要确认回归从何时开始，是否对应数据增长、参数分布、部署或统计信息变化。
- 待处理订单占比和租户分布怎样？ 若
pending长期只占 1% 到 2%，部分索引很有吸引力；若它占一半，部分索引的尺寸优势会缩小。大租户与小租户混在一个平均值里，也会掩盖参数倾斜。 - 查询是否始终使用字面量
status='pending'？ 部分索引只有在规划阶段能证明查询条件蕴含索引谓词时才可用。把状态写成通用参数，可能让规划器无法证明匹配。 - 返回列和读取一致性要求是什么？ 若还要返回大文本、JSON 或关联十张表，覆盖索引会迅速膨胀。先确认这条列表查询真正需要的列。
- 表有多热，允许怎样上线？ 每秒 3,000 次写入意味着索引宽度和状态变更成本必须测量。线上通常考虑
CREATE INDEX CONCURRENTLY，同时预留更长构建时间、额外扫描和失败后清理无效索引的操作窗口。 - 能否在生产规模副本执行实际计划？
EXPLAIN ANALYZE会真正执行语句。即使是SELECT，也可能产生明显负载；写语句还会执行副作用。应选择副本、限定参数，或先使用不带ANALYZE的计划。
30 秒回答框架
“我会先用应用 p95 和 pgstatstatements 确认这条归一化查询的调用量、总数据库时间及慢租户，排除锁等待和外部依赖。然后在生产规模副本对代表性参数运行 EXPLAIN (ANALYZE, BUFFERS)，从实际行数与估算行数、循环次数、缓冲块和排序节点定位代价。本例两个单列索引仍要产生 42 万候选行再排序；查询固定读取稀少的 pending 状态，我会先验证部分覆盖索引 (tenantid, createdat DESC) INCLUDE (id, totalcents) WHERE status='pending'。它能按租户和时间直接取前 50 条。若状态必须参数化，就比较完整复合索引 (tenantid, status, created_at DESC)。最后用大中小租户、冷热缓存和并发写入验证 p95、缓冲读取、索引大小与写延迟，再并发创建并保留回滚门槛。”
分步骤深入解答
第一步：从真实工作负载确定优先级
先把应用追踪中的路由、租户、参数范围和 p95 对齐到数据库的归一化查询。若已经启用 pgstatstatements，可先看累计资源消耗：
SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;totalexectime 适合发现大量调用累积出来的数据库时间，meanexectime 适合发现单次昂贵查询，calls 说明放大倍数。这个视图没有 p95，也不会自动解释某个租户或参数为何更慢，因此还要保留应用侧分位数和参数分组。若主要时间消耗在锁等待、连接排队、网络或下游服务，单独改执行计划不会解决完整延迟。
第二步：安全地取得实际执行证据
先用普通 EXPLAIN 查看计划形状，再在生产规模副本或受控环境执行：
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'pending'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;读取计划时从最深且实际耗时高的节点向上走，并把 actual rows × loops 作为节点总工作量的一部分。Buffers: shared read 反映需要从存储读取的块，shared hit 表示块已在共享缓冲区；命中仍会消耗 CPU 和内存带宽。排序落盘时会看到外部排序及临时块读写，通常说明输入行数或工作内存需要进一步分析。
题目中估算 8,000 行、实际 420,000 行，相差 52.5 倍。这个偏差可能来自统计信息过旧，也可能来自 tenant_id 与 status 的相关性没有被单列统计表达。先运行合适范围的 ANALYZE 并复测；若相关性稳定且确实影响计划，可评估针对这组列的扩展统计。扩展统计也有收集和规划成本，只为能改善关键估算的强相关列建立。
第三步：从查询形状推导索引
现有两个单列索引可能被 BitmapAnd 组合，但组合结果失去单个 B-tree 的顺序，仍要访问大量堆页并排序。也可能只选择其中一个索引，再对另一条件进行过滤。两个索引“都存在”只说明规划器有候选路径，不能保证候选路径符合 WHERE + ORDER BY + LIMIT 的完整形状。
针对固定且稀少的 pending 状态，先比较部分索引：
CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';
CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);第一种只保存待处理订单，索引通常更小；等值租户定位后，createdat 同时约束 30 天范围并提供倒序，规划器可以在取得 50 行后停止。id 和 totalcents 放入 INCLUDE，不会参与搜索或排序，只提供覆盖读取的可能。
第二种完整复合索引适合状态由参数决定、多个状态都需要同类查询的场景。B-tree 的前导等值列先缩小范围，后续时间列承担范围和顺序。不能机械套用“选择性最高的列永远放第一”；索引顺序要由等值、范围、排序和可复用查询共同决定。
第四步：明确部分索引和覆盖索引的边界
部分索引只有在规划时能证明查询条件包含 status='pending' 才能使用。若通用预编译语句写成 status = $2，该参数对所有可能值都不能保证满足索引谓词，规划器可能放弃部分索引。可为固定的运营查询保留字面量并单独封装，也可以选择完整复合索引；决定要根据真实查询模板验证，不能靠索引定义猜测。
INCLUDE 也不保证每次得到 Index Only Scan。PostgreSQL 仍要验证 MVCC 可见性；页面的 all-visible 位未设置时，需要回表检查。频繁插入和状态更新的热表会让更多页面需要堆访问。若计划显示 Heap Fetches 仍然很高，应评估去掉载荷列后的较窄索引是否更划算。宽索引还会增加磁盘、缓存占用和每次写入的维护成本。
第五步：验证收益与代价
用同一组代表性参数比较前后计划：超大租户、中位租户和小租户；有大量 pending 的租户和几乎没有 pending 的租户；缓存预热前与预热后。至少记录执行时间分布、实际行数、缓冲块、排序、临时 I/O、Heap Fetches 和索引尺寸。一次执行的毫秒数容易受缓存和并发干扰，计划工作量更容易解释变化原因。
再以真实写入比例压测插入以及 pending → paid 状态更新。部分索引在订单完成时要删除索引项，完整复合索引则会更新状态键；两者都不是零成本。验收门槛可写成：读取 p95 回到目标以内，p99 不明显回退；共享块与候选行数显著下降；写入 p95、WAL 量、磁盘和复制延迟仍在预算内。
上线时先确认磁盘余量和并发构建监控。CREATE INDEX CONCURRENTLY 允许表继续插入、更新和删除，但耗时更长，也有失败后留下无效索引等操作风险。索引建成后用生产查询模板确认实际采用，再观察一个完整高峰周期。若写延迟或复制延迟越界，先移除新查询路径，再按操作规范删除新索引；保留旧索引，直到新方案经过稳定窗口且确认没有其他依赖。
高质量示范回答
“我会先确认这条 SQL 值得优先处理。应用侧给我 p95 和慢租户，pgstatstatements 给我调用次数、总执行时间和平均执行时间。若它每秒 40 次且累计数据库时间靠前，我会抓取真实查询模板和几组不同规模租户，在生产规模副本运行 EXPLAIN (ANALYZE, BUFFERS)。
这份计划的核心问题是扫描结果太宽：优化器估 8,000 行，实际在 top-N 排序前有 420,000 行，并触碰约 120,000 个缓冲块。两个单列索引可能组合过滤，却不能直接提供 tenantid + pending + createdat DESC 的有序窄区间。我会先更新统计信息并复测；若租户与状态相关性导致估算持续偏差，再评估扩展统计。
因为运营查询固定读取稀少的 pending 状态，我会在副本验证部分索引 (tenantid, createdat DESC) INCLUDE (id, totalcents) WHERE status='pending'。它先限定索引集合，再按租户进入时间有序区间，找到 50 行即可停止。若应用要把状态参数化并查询多种状态，我会比较完整复合索引 (tenantid, status, created_at DESC)。INCLUDE 只提供 Index Only Scan 的可能，热页面仍可能回表，所以我会同时看 Heap Fetches。
验收覆盖大中小租户、冷热缓存和并发写入。我会比较 p95/p99、候选行数、缓冲块、临时 I/O、索引尺寸、WAL、写入延迟和复制延迟。生产使用并发建索引，建成后确认真实模板采用新计划，并观察完整高峰周期。读取收益不足或写路径越界时撤回新路径并删除新索引，不用硬件扩容掩盖未解释的计划问题。”
常见错误
- 看到慢 SQL 就加索引 → 没有确认频率、参数和等待类型，可能优化了低优先级查询 → 先关联应用分位数、
pgstatstatements和真实参数。 - 把
Seq Scan一律判成错误 → 返回大比例数据或小表时，顺序扫描可能更便宜 → 比较实际行数、缓冲块和替代计划的总成本。 - 只看计划是否出现 Index Scan → 索引扫描仍可能读取几十万项并大量回表 → 检查
actual rows × loops、Filter、Buffers 和 Heap Fetches。 - 忽略估算与实际差异 → 规划器会基于错误基数选择连接、扫描和排序方式 → 先更新统计，再为稳定相关列评估扩展统计。
- 认为多个单列索引等价于复合索引 → Bitmap 组合通常不能直接满足所需排序，也可能访问大量堆页 → 按等值、范围、顺序和 LIMIT 推导复合键。
- 给所有列都加
INCLUDE→ 索引膨胀会降低缓存效率并放大写成本 → 只覆盖高频查询真正需要且宽度可控的列。 - 部分索引建成却不验证查询模板 → 参数化谓词可能无法在规划时蕴含索引条件 → 用线上相同的预编译方式检查实际计划。
- 直接在主库对任意 SQL 跑
EXPLAIN ANALYZE→ 命令会真实执行查询，重查询会制造负载，写语句还会产生副作用 → 先用普通 EXPLAIN，并在副本或受控事务中取得实际证据。 - 只报告一次执行从 2.8 秒降到某个数 → 缓存、参数和并发都可能造成偶然结果 → 比较分布、计划工作量和完整高峰周期。
追问及应对
追问一：为什么顺序扫描有时比索引扫描快？
查询要读取表中很大比例的行时，顺序读取可以减少随机访问；索引扫描还要遍历索引并回到分散的堆页。小表可能只占少量页面，直接扫描也更便宜。应比较真实数据下的缓冲块和总时间，而不是把节点名称当成评分。
追问二：为什么 (tenantid) 和 (createdat) 两个索引不够？
规划器可以选择一个索引后过滤，也可以用 BitmapAnd 组合两个索引。Bitmap 组合需要收集候选元组位置，结果不保留 created_at 的 B-tree 顺序，因此常常还要读取大量堆页并排序。复合索引把等值租户、时间范围和排序放在同一条有序访问路径中，LIMIT 50 才能尽早停止。
追问三：部分索引为什么偶尔完全不被使用？
查询条件必须在规划阶段被识别为蕴含索引谓词。status='pending' 能直接匹配；status=$2 对任意参数都不能保证匹配。表达式写法不同、数据分布改变或成本估算认为回表更贵，也可能选择其他计划。应使用真实预编译模板执行 EXPLAIN，并在需要通用状态查询时改用完整复合索引。
追问四：执行 ANALYZE 后估算仍差 50 倍怎么办？
先确认采样覆盖、列的统计目标和数据是否刚发生剧烈变化。若 tenant_id 与 status 强相关，单列统计会把条件近似当作独立事件，可以为这组列建立依赖或 MCV 扩展统计再复测。扩展统计只改善估算，不会替代缺失的访问路径；索引和查询形状仍要独立验证。
追问五：读查询变快，但写入 p95 上升了，怎样决策？
把收益和成本放回 SLO 与总负载：读取节省了多少数据库时间，写入回退影响多少用户，WAL、磁盘和复制延迟增加多少。若只有固定稀少状态需要加速，较窄的部分索引可能比完整覆盖索引更合适；若载荷列导致膨胀，去掉 INCLUDE 接受少量回表。超过写入预算时撤回索引方案，继续从查询范围、分页契约或数据模型寻找更小的访问路径。