PostgreSQL SQL 优化

SQL 优化的核心不是“让查询使用索引”，而是：

用尽可能少的扫描、随机读取、排序、临时文件和锁等待，返回真正需要的数据。

以下内容以 PostgreSQL 18 为参考，大部分方法同样适用于 PostgreSQL 14～17。

一、标准优化流程

建议始终按照这个顺序处理：

找到真正消耗资源的 SQL
获取执行计划
比较估算行数与实际行数
判断瓶颈是扫描、连接、排序、统计信息还是锁
改写 SQL 或增加合适索引
使用相同参数重新测试
比较执行时间、读取块数和返回结果
在真实数据量和并发环境下验证

不要看到慢查询就直接加索引。

二、找到最值得优化的 SQL

推荐启用 pg_stat_statements。它能统计服务器执行过的 SQL、调用次数、总耗时、平均耗时和磁盘读取情况。PostgreSQL 官方文档

配置：

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

重启 PostgreSQL 后创建扩展：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

查询累计耗时最高的 SQL：

SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_written,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

分别关注：

total_exec_time：对数据库总体影响最大的 SQL
mean_exec_time：单次执行最慢的 SQL
calls：执行频率
shared_blks_read：从磁盘读取的数据块
temp_blks_written：排序或哈希产生的临时文件

优化优先级通常是：

总消耗高 > 调用频繁 > 偶尔执行一次但很慢

三、正确使用 EXPLAIN

1. 只查看预估计划

EXPLAIN
SELECT *
FROM fundamentals
WHERE code = '600519';

这不会真正执行查询。

2. 获取真实执行数据

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    TIMING OFF
)
SELECT code, name
FROM fundamentals
WHERE code = '600519';

ANALYZE 会真正执行 SQL，并显示实际耗时和实际行数；BUFFERS 能看到缓存和磁盘读取情况。EXPLAIN 官方说明

注意：不要直接在生产环境对以下语句执行 EXPLAIN ANALYZE：

UPDATE ...
DELETE ...
INSERT ...

因为它们会真正修改数据。优先在测试环境执行，或只使用普通 EXPLAIN。

四、怎么看执行计划

常见节点：

执行节点	含义	是否一定有问题
`Seq Scan`	全表扫描	小表或返回大量数据时很正常
`Index Scan`	通过索引定位，再访问表	常见
`Index Only Scan`	尽量只读取索引	通常更省读取
`Bitmap Heap Scan`	先收集位置，再批量访问表	返回中等数量数据时常见
`Nested Loop`	嵌套循环连接	小结果集通常很好
`Hash Join`	使用哈希表连接	大量等值连接常见
`Merge Join`	排序后合并连接	有序大数据集常见
`Sort`	排序	注意是否写入磁盘
`Aggregate`	聚合计算	注意输入行数
`Gather`	并行查询	不代表一定更快

最重要的四个检查点

1. 估算行数是否准确

rows=100
actual rows=100000

相差几百甚至几千倍，说明优化器对数据分布判断错误，容易选错连接方式或扫描方法。

2. 是否有大量无效过滤

Rows Removed by Filter: 2000000

扫描了大量数据，最后只返回几行，通常需要检查：

缺少索引
索引列顺序不合理
条件中对索引列使用了函数
条件选择性太低

3. `loops` 是否过大

actual rows=1 loops=100000

节点被重复执行十万次，常见于低效的 Nested Loop、关联子查询或应用层 N+1 查询。

4. 是否写入临时文件

Sort Method: external merge
Disk: 500MB

说明排序内存不足或待排序数据过多。优先减少输入数据、利用索引顺序，再考虑调整 work_mem。

五、索引优化原则

1. 给查询条件和连接字段建立索引

常见索引目标：

WHERE 条件
JOIN ... ON 条件
ORDER BY
高频分组和去重
外键列

PostgreSQL 不会因为创建了外键就自动为外键列建立索引。

CREATE INDEX idx_orders_user_id
ON orders (user_id);

但主键和唯一约束已经自动带有唯一索引，不要重复创建：

PRIMARY KEY (code)

已经覆盖：

CREATE INDEX ON fundamentals (code);

2. 联合索引列顺序

假设查询为：

SELECT id, created_at, amount
FROM orders
WHERE user_id = 1001
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

适合的索引：

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC)
INCLUDE (id, amount);

一般顺序可以理解为：

等值过滤列 → 范围过滤列/排序列 → INCLUDE 返回列

B-tree 联合索引最有效的部分通常从最左侧列开始。多列索引官方说明

不要机械地遵循“选择性最高的列必须放第一位”。应当根据实际查询组合、排序要求和复用范围决定。

3. 覆盖索引

CREATE INDEX idx_fundamentals_name
ON fundamentals (name)
INCLUDE (code, core_view);

当查询只需要索引中的字段时，可能使用 Index Only Scan。但能否真正避免访问表，还受到 MVCC 可见性映射影响。覆盖索引官方说明

不要把大量宽文本字段都放进 INCLUDE，否则索引会变得很大。

4. 部分索引

如果只频繁查询少量未处理数据：

CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';

对应查询必须包含能够匹配索引条件的谓词：

SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

部分索引只保存符合条件的数据，可以减小索引体积和维护成本。部分索引官方说明

5. 表达式索引

以下查询不能直接有效利用普通 email 索引：

SELECT *
FROM users
WHERE lower(email) = 'user@example.com';

建立表达式索引：

CREATE INDEX idx_users_lower_email
ON users (lower(email));

6. 根据数据类型选择索引

场景	常用索引
等值、范围、排序	B-tree
JSONB、数组、全文检索	GIN
范围、空间、相似度	GiST
超大、按时间自然增长的表	BRIN
简单等值查询	B-tree 通常已经足够

PostgreSQL 支持 B-tree、Hash、GiST、SP-GiST、GIN、BRIN 等索引类型。索引类型官方说明

六、常见 SQL 改写

1. 不要无条件使用 `SELECT *`

不推荐：

SELECT *
FROM fundamentals
WHERE code = '600519';

2. 不要对索引列进行函数计算

不推荐：

SELECT *
FROM orders
WHERE created_at::date = DATE '2026-09-15';

3. 避免深分页 OFFSET

不推荐：

SELECT id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 500000;

数据库仍然需要找到并跳过前面五十万行。

推荐游标分页：

SELECT id, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;

配套索引：

CREATE INDEX idx_orders_created_id
ON orders (created_at DESC, id DESC);

4. 判断存在时不要统计全部数据

不推荐：

SELECT count(*)
FROM orders
WHERE user_id = 1001;

如果只需要判断是否存在：

SELECT EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = 1001
);

5. 谨慎使用 `NOT IN`

如果子查询可能返回 NULL，NOT IN 的结果容易出现意外。

6. 消除应用层 N+1 查询

不推荐：

查询100个用户
然后针对每个用户单独查询订单
总计执行101条SQL

可以使用连接、批量查询或数组参数：

SELECT *
FROM orders
WHERE user_id = ANY ($1);

七、更新统计信息

优化器需要统计信息来估算行数。ANALYZE 会收集数据分布信息，供查询规划器选择执行计划。ANALYZE 官方说明

大量导入、删除或更新数据后执行：

ANALYZE orders;

也可以：

VACUUM (ANALYZE) orders;

如果某个字段分布非常不均匀：

ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders;

如果多个字段具有明显关联关系：

CREATE STATISTICS st_orders_user_status
    (dependencies, ndistinct, mcv)
ON user_id, status
FROM orders;

ANALYZE orders;

扩展统计信息可以改善多个相关字段组合查询的行数估算。规划器统计信息官方说明

八、排序与内存优化

如果执行计划出现：

Sort Method: external merge
Disk: ...

说明排序使用了临时文件。

可以针对当前事务临时增加：

BEGIN;

SET LOCAL work_mem = '128MB';

SELECT ...
ORDER BY ...;

COMMIT;

但不要直接把全局 work_mem 设置得很大，因为它不是“每个连接只使用一次”，而可能被：

每个排序节点使用
每个哈希节点使用
每个并行工作进程使用
每个并发查询使用

优先考虑：

减少排序前的数据量
建立满足 ORDER BY 的索引
删除不必要的排序
最后再调整 work_mem

九、查询慢不一定是 SQL 慢

检查正在运行的 SQL：

SELECT
    pid,
    usename,
    now() - query_start AS runtime,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE datname = current_database()
  AND state <> 'idle'
ORDER BY runtime DESC;

检查被阻塞的会话：

SELECT
    pid,
    pg_blocking_pids(pid) AS blocking_pids,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

如果出现：

wait_event_type = Lock

那么主要问题可能是：

长事务
未提交事务
大批量更新
DDL 锁
多个事务以不同顺序更新相同资源

此时增加索引可能无法解决问题。

十、针对你的 fundamentals 表

你的表大约只有 3000 条 A 股数据，所以即使出现 Seq Scan，也不一定需要优化。小表全表扫描可能比读取索引更快。

已有主键：

PRIMARY KEY (code)

以下查询已经可以利用主键索引：

SELECT code, name, core_view
FROM fundamentals
WHERE code = '600519';

name 的 B-tree 索引适合：

WHERE name = '贵州茅台'

以及部分前缀查询：

WHERE name LIKE '贵州%'

但通常不能高效支持：

WHERE name LIKE '%茅台%'

如果将来需要在公司亮点、主营业务、题材要点中进行大量模糊搜索，可以考虑 pg_trgm：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_fundamentals_core_view_trgm
ON fundamentals
USING gin (core_view gin_trgm_ops);

不过以目前约 3000 行的数据规模，除非查询非常频繁，否则没有必要建立大量全文或模糊搜索索引。

最终检查清单

每次优化一条 SQL，依次确认：

SQL 是否真的属于高消耗 SQL
返回字段是否过多
扫描行数是否远大于返回行数
估算行数和实际行数是否严重不一致
loops 是否异常大
是否发生磁盘排序
查询条件是否能使用索引
联合索引顺序是否匹配查询
是否存在重复或无效索引
是否有锁等待或长事务
优化后写入性能是否受到影响
优化前后是否使用相同参数和数据进行测试

最有效的具体分析材料是：

1. 原始 SQL
2. CREATE TABLE 结构
3. 已有索引
4. EXPLAIN (ANALYZE, BUFFERS) 输出
5. 表数据量
6. 查询参数的典型值

有了这六项，才能从“通用建议”进入针对某条 SQL 的精确优化。

PostgreSQL SQL 优化

一、标准优化流程

二、找到最值得优化的 SQL

三、正确使用 EXPLAIN

1. 只查看预估计划

2. 获取真实执行数据

四、怎么看执行计划

最重要的四个检查点

1. 估算行数是否准确

2. 是否有大量无效过滤

3. loops 是否过大

4. 是否写入临时文件

五、索引优化原则

1. 给查询条件和连接字段建立索引

2. 联合索引列顺序

3. 覆盖索引

4. 部分索引

5. 表达式索引

6. 根据数据类型选择索引

六、常见 SQL 改写

1. 不要无条件使用 SELECT *

2. 不要对索引列进行函数计算

3. 避免深分页 OFFSET

4. 判断存在时不要统计全部数据

5. 谨慎使用 NOT IN

6. 消除应用层 N+1 查询

七、更新统计信息

八、排序与内存优化

九、查询慢不一定是 SQL 慢

十、针对你的 fundamentals 表

最终检查清单

3. `loops` 是否过大

1. 不要无条件使用 `SELECT *`

5. 谨慎使用 `NOT IN`