# 别让报表 Agent 的 SQL 拖慢收银交易表

> 先定可量化目标、读执行计划，把报表迁到 PostgreSQL 物化视图，最后才考虑索引。

原文: https://fdetimes.net/zh/guides/postgresql-materialized-views-ai-reports/

设想你部署报表智能体的第一周，客户是一家零售连锁企业。每当有人问“本周各门店营业额”，Agent 就生成 SQL，直接扫描 `orders` 表，而收银机一整天都在往这张表里写数据。演示时一切顺利。上了生产环境，客户的运维团队开始追问：数据库怎么忙了这么多？

问题不在提示词。Agent 不知道哪些表服务于交易、哪些表用于分析，这一点得由你替它弄清楚。本文介绍一套可以在笔记本上用 PostgreSQL 实操的流程：定目标，读执行计划，把报表迁到物化视图上，最后才考虑索引。

文中的表结构是假设的简化版：一张 `orders(id, store_id, created_at, amount, is_paid)` 表。你需要在本地运行 PostgreSQL，有建表权限，最好再有一份足够大的数据副本，让执行计划接近生产环境的情况。

## 第 1 步：要达到什么数字，哪些语句值得改？

Oracle 把 SQL 调优定义为一个迭代过程，目标必须具体、可量化、可实现。所以在敲任何命令之前，先和客户一起把目标写下来。举个假设的例子：“周营业额报表在两秒内返回，且运行报表的 Agent 不拖慢订单写入”。

接着找出值得改的语句。Oracle 的文档建议查看执行历史，找出占系统负载和资源最多的语句，而不是随意调优。

有了 Agent，执行历史通常会暴露一个现象：少数几种聚合查询模式反复出现，只是参数略有不同。它们就是首批候选。

**检查：**你手里有一份负载最重的语句短名单，以及一个客户认可的目标数字。

## 第 2 步：真正执行之前，先读执行计划

按照 Oracle 的说法，执行计划是手动调优的主要诊断工具。`EXPLAIN` 只展示计划、不执行语句，因此是在客户数据库上最安全的一步。

```sql
EXPLAIN
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;
```

要拿到真实数字，就得用 `EXPLAIN ANALYZE`。PostgreSQL 文档写得很明确：这条命令会真正执行查询，所有副作用照常发生。对于写数据的语句，PostgreSQL 建议包在事务里，执行后回滚。

但别把回滚当成安全保障。回滚只撤销数据变更；一条重量级聚合查询仍会完整跑完，照样压在客户的交易数据库上。

对下面这种只读查询，包一层事务毫无帮助。应当在数据副本上运行，或者在运维团队同意后，于业务低峰期运行。

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;
```

`BUFFERS` 选项会报告计划中每个节点的 I/O。PostgreSQL 18 在使用 `ANALYZE` 时会隐式开启它，但显式写出来，对日后读你脚本的人仍有帮助。

## 第 3 步：看行数，别看成本

新手常把 `cost` 列当成毫秒来比较。它不是。PostgreSQL 文档指出，成本单位是任意的，并不对应真实时间；最该检查的是估算行数与实际情况是否接近。

假设扫描 `orders` 的节点估算 1,000 行，实际返回 900,000 行。规划器按小数据集选了策略，结果要处理的数据量大了 900 倍，该节点之上的每个决策都建立在错误的数字上。这是首先要记下的问题，还轮不到考虑索引。

**检查：**对名单上的每条语句，你都清楚哪个节点读取的缓冲区最多，哪个节点的估算偏差最大。

## 第 4 步：给 Agent 一份汇总数据来读

Oracle 把缺少访问结构（如索引和物化视图）列为 SQL 变慢的典型原因。对 AI 报表来说，物化视图通常是更好的选择：Agent 读取预先聚合好的表，而不是反复冲击交易表。

Microsoft 在描述物化视图（Materialized View）模式时强调了一个很有价值的特性：视图及其数据完全可丢弃，因为随时可以从源数据重建。它是一种特殊形式的缓存，所以你可以试验、出错、重建，而不会碰到客户的源数据。

```sql
CREATE MATERIALIZED VIEW report_daily_store_sales AS
SELECT store_id,
       date_trunc('day', created_at) AS day,
       sum(amount) AS revenue,
       count(*)    AS order_count
FROM orders
GROUP BY 1, 2;
```

**要点:** AI 报表应当读取汇总数据，而不是直接读客户的交易表。

这一步最难的问题与 SQL 无关。Microsoft 建议先确定数据可以接受滞后多久，再在事件驱动、定时和手动刷新之间做选择。

如果区域经理只在每天早上看报表，数据滞后几个小时没问题；如果他们白天要靠它调货，就不行。这个问题要在客户需求调研阶段问清楚，而不是靠猜。

## 第 5 步：刷新时不把读者锁在门外

普通的 `REFRESH MATERIALIZED VIEW` 在运行期间会阻塞对视图的读取，也就是说 Agent 得等着。`CONCURRENTLY` 方式刷新视图时不会锁住并发的 SELECT，但要求有一个 UNIQUE 索引，并且同一时间只允许一个刷新在运行。

```sql
CREATE UNIQUE INDEX report_daily_store_sales_key
  ON report_daily_store_sales (store_id, day);

REFRESH MATERIALIZED VIEW CONCURRENTLY report_daily_store_sales;
```

一个容易踩的坑：PostgreSQL 文档要求这个索引只能使用普通列，不能是表达式索引，也不能带 WHERE 子句。上面的例子中，`date_trunc` 已经写在视图定义里，`day` 是视图的普通列，所以索引有效。如果把 `date_trunc` 放进索引，并发刷新就无法执行。

别忘了这个视图是从 `orders` 构建的：每次完整刷新都会针对交易表重新执行视图定义中的聚合，Microsoft 也指出刷新需要消耗计算资源。因此，在第 4 步约定的滞后阈值允许范围内尽量少刷新，并安排在低峰时段，而不是营业高峰。

索引能让 Agent 读得更快，但并非没有代价。Microsoft 指出，维护视图上的索引会给每个刷新周期增加写入成本。只保留报表查询真正用到的索引。

**检查：**把 Agent 的查询改为指向视图，重新运行 `EXPLAIN`，与第 3 步的缓冲区数量做对比。

## 第 6 步：悄无声息过期的视图是最危险的故障

Microsoft 警告，一旦触发器或刷新任务漏跑，视图会悄无声息地返回过期结果。对 Agent 而言，这比明显的报错更糟：它会用昨天的数字流畅作答。建议记录最近一次刷新的时间，当视图的数据时效超过约定阈值时发出告警。

一个简单的做法：刷新任务把完成时间写入一张小日志表，再由定期检查把这个时间与第 4 步的阈值做比较。你也可以让 Agent 读取这个时间戳，在回答中注明“数据截至……”，以免误导用户。

## 什么时候该考虑索引表？

有些查询不是聚合，而是按主键以外的键查找，比如按客户编码查订单。Microsoft 的索引表（Index Table）模式在架构层面解决这类问题，做法是单独构建一张索引表。

把它迁移到 PostgreSQL 索引上只是类比，并非 PostgreSQL 的规则，但该模式中有两条建议值得前线部署工程师（FDE）记住。

第一，不要为应用从不运行、或只偶尔运行的查询预先建索引表。每张索引表都会增加维护成本，因此需要定期审查，没人用了就删掉。放到 Agent 场景里，只有当第 1 步的执行历史显示某种查询模式确实反复出现时，才添加索引。

第二，低选择性的键（例如布尔列 `is_paid`）是糟糕的候选。索引表照样要付出全部存储和吞吐成本，却几乎缩小不了查询范围。只有两个取值的标志位只能把表一分为二，帮不了你快速找到少数几行。

## 常见错误

最常见的错误是在生产环境运行 `EXPLAIN ANALYZE`，以为有 ROLLBACK 就安全，负载却照样落在客户数据库上。第二个错误是比较两个计划的 cost，并以毫秒为单位下结论。

第三个错误是建了物化视图，却没有人约定可接受的滞后程度，于是刷新全凭感觉，出了故障也没有任何告警。

反过来，也别急着给 Agent 筛选过的每一列都建索引。每个索引都是客户交易系统要一直承担的写入成本。

## 如何把这项技能写进简历

如果 FDE 岗位描述提到直接对接客户数据库或生产环境性能，这项技能值得在面试中重点展示。

简历上别写“精通 SQL”。写一行结构清晰的描述：可量化的目标、负载最重的语句、你做的改动（物化视图、UNIQUE 索引、并发刷新），以及你如何监控数据滞后。

一个小仓库，包含脚本、优化前后的执行计划，以及一份解释为何不给布尔列建索引的 README，比任何证书都更有说服力。

Agent 写 SQL 的能力会越来越强。但弄清客户数据库在什么时间为谁服务、Agent 为什么不该碰它，依然是坐在客户运维团队旁边的 FDE 的工作。

**本周动手试试:**

- 在本地搭建 PostgreSQL，创建一张有几百万行的模拟订单表，对一条聚合查询运行 EXPLAIN (ANALYZE, BUFFERS)，记录估算行数与实际行数。
- 把这条查询改造成带 UNIQUE 索引的物化视图，运行 REFRESH MATERIALIZED VIEW CONCURRENTLY，然后对比前后的执行计划。
- 写一份简短的 README，说明目标、可接受的数据滞后、刷新计划，以及视图过旧时如何告警，然后放进你的作品集。

## 信源

- [Introduction to SQL Tuning (Oracle Database 23)](https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/introduction-to-sql-tuning.html)

- [Using EXPLAIN (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/18/using-explain.html)

- [Materialized View Pattern - Azure Architecture Center](https://learn.microsoft.com/en-us/azure/architecture/patterns/materialized-view)

- [Index Table Pattern - Azure Architecture Center](https://learn.microsoft.com/en-us/azure/architecture/patterns/index-table)

- [REFRESH MATERIALIZED VIEW (PostgreSQL 18 Documentation)](https://www.postgresql.org/docs/18/sql-refreshmaterializedview.html)
