PostgreSQL 性能优化与查询计划分析实战

在生产环境被慢查询折磨了三个月后，我终于把 PostgreSQL 的查询计划分析啃了下来。这篇文章记录了从"看 EXPLAIN 像看天书"到"一眼定位性能瓶颈"的全过程。

结论

性能优化最有效的路只有一条：用 EXPLAIN 定位瓶颈 → 针对性加索引或改写 SQL → 验证执行计划变化。别凭直觉优化，每次修改前必须看执行计划。

环境准备

我的测试环境：

• PostgreSQL 15.3
• 一张 500 万行的订单表
• 一张 100 万行的用户表
• 一张 2000 万行的订单明细表

建表脚本：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    status VARCHAR(20),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER,
    quantity INTEGER,
    price DECIMAL(10,2)
);

先插入测试数据：

-- 插入 100 万用户
INSERT INTO users (email)
SELECT 'user' || generate_series(1, 1000000) || '@example.com';

第一课：别信直觉，信 EXPLAIN

这是我犯的第一个错误。某天接口响应从 200ms 飙升到 5s，我"直觉"认为是订单表数据量太大，二话不说加了索引——然并卵。

冷静下来跑了个 EXPLAIN：

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

输出关键部分（简化）：

{
  "Plan": {
    "Node Type": "Sort",
    "Actual Time": 4520.32,
    "Sort Key": "created_at DESC",
    "Plans": [{
      "Node Type": "Seq Scan",
      "Relation Name": "orders",
      "Actual Rows": 850000,
      "Actual Time": 12.33,
      "Filter": "(status = 'pending')"
    }]
  }
}

真相大白：全表扫描（Seq Scan）扫了 85 万行，然后花了 4.5 秒排序。我加的"直觉索引"根本没解决排序问题。

教训：任何优化前必须跑 EXPLAIN ANALYZE。不分析执行计划就加索引，等于蒙着眼睛修车。

索引优化：组合索引的艺术

先加一个覆盖查询条件的组合索引：

CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

-- 验证效果
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

新执行计划：

Limit  (cost=0.42..68.90 rows=20 width=48)
  ->  Index Scan Backward using idx_orders_status_created on orders
      (cost=0.42..1455260.32 rows=425000 width=48)
      Index Cond: (status = 'pending')

查询时间从 4.5 秒降到 0.3 毫秒。关键在于索引中的 created_at DESC 让排序变成了索引的天然顺序，不再需要额外的 Sort 节点。

第二课：WHERE 条件的顺序不重要——但索引列的顺序很重要

很多开发者（包括过去的我）以为 WHERE 条件的书写顺序会影响查询性能。实际上 PostgreSQL 的优化器会自动重排 WHERE 条件。

真正重要的是索引列的顺序。组合索引遵循"最左前缀原则"：

-- 这个索引对以下查询有效
CREATE INDEX idx_test ON orders (status, user_id, created_at);

-- ✅ 走索引
WHERE status = 'pending' AND user_id = 123;
-- ✅ 走索引
WHERE status = 'pending';
-- ❌ 不走索引（跳过了第一列 status）
WHERE user_id = 123;

实战 Debug：一个 JOIN 查询的优化过程

有个运营后台报表查询，跑一次要 30 秒：

SELECT u.email, o.total_amount, oi.quantity
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= '2024-01-01'
  AND u.email LIKE '%@gmail.com'
ORDER BY o.total_amount DESC;

EXPLAIN ANALYZE 后发现问题：

Nested Loop  (cost=0.00..1256320.00 rows=250000)
  Join Filter: (o.user_id = u.id)
  ->  Seq Scan on users u  (cost=0.00..485632.00 rows=100000)
        Filter: (email ~~ '%@gmail.com%')")
  ->  Seq Scan on orders o  (cost=0.00..385632.00 rows=250000)

两个全表扫描，Nested Loop 循环了 2500 亿次（理论上）。

逐步优化：

第一步——给外键加索引（这个本该建表时就有的）：

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

查了一下，order_items 和 orders 的关联走了索引，但 users 表还是全表扫描（因为 LIKE '%@gmail.com' 走不了普通 B-tree 索引）。

第二步——如果查询以 @gmail.com 结尾为主，用反向索引：

-- 对于后缀查询，存储反转的邮箱
CREATE INDEX idx_users_email_reverse ON users(reverse(email));
-- 查询时也反转条件
WHERE reverse(email) LIKE reverse('%@gmail.com');

但这个方案太 hacky。实际场景中更好的做法是用 trigram 索引：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING gin(email gin_trgm_ops);

第三步——改写 SQL，减少 JOIN 的数据量：

-- 先缩小范围再 JOIN
WITH filtered_orders AS (
    SELECT * FROM orders
    WHERE created_at >= '2024-01-01'
),
matched_users AS (
    SELECT id FROM users
    WHERE email LIKE '%@gmail.com'
)
SELECT u.email, o.total_amount, oi.quantity
FROM matched_users u
JOIN filtered_orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
ORDER BY o.total_amount DESC;

最终查询时间从 30 秒降到 1.2 秒。

第三课：EXPLAIN 的隐藏信息

多数人只看 cost 和 Actual Time，但下面这几个字段更致命。

Buffers——告诉你有多少数据是从缓存读的，多少是从磁盘读的：

Buffers: shared hit=1234 read=5678

• shared hit = 内存命中（快）
• shared read = 磁盘读取（慢）
• 如果 read 远大于 hit，说明 shared_buffers 配置太小

Rows Removed by Filter——扫描了多少行后被过滤掉。如果这个值巨大，说明索引没用好：

Seq Scan on orders  (cost=0.00..485632.00 rows=250000 width=48)
  Filter: (status = 'pending')
  Rows Removed by Filter: 750000

扫了 100 万行，过滤掉 75 万——这时候索引就该上场了。

第四课：配置调优——PG 的默认配置是给 256MB 内存的机器用的

PostgreSQL 默认配置极其保守。我的云服务器有 16GB 内存，但 PostgreSQL 只用了 128MB 做缓存。

几个关键参数：

-- 推荐给 16GB 内存、SSD 硬盘的配置
shared_buffers = 4GB          -- 总内存的 25%
effective_cache_size = 12GB   -- 总内存的 75%
work_mem = 64MB               -- 排序和哈希操作
maintenance_work_mem = 1GB    -- VACUUM、CREATE INDEX
random_page_cost = 1.1        -- SSD 设为 1.1，机械硬盘维持 4.0

改完后，大量查询从磁盘读取转为内存命中，整体响应时间下降了约 40%。

注意：work_mem 是按操作分配的，不是按连接。如果你的连接数很多（比如 100），每个连接做排序时都会分配 work_mem，所以要算总账：max_connections * work_mem。

第五课：计划缓存和参数化查询

PostgreSQL 会缓存执行计划，但绑定了具体的参数值。同样的 SQL，参数不同执行计划也可能不同：

-- 预备语句让 PG 做通用计划
PREPARE find_orders (varchar) AS
SELECT * FROM orders WHERE status = $1;

但通用计划不一定最优——如果 status = 'pending' 占 50% 的数据，全表扫描反而比索引快。PG 11+ 用 custom plan 解决了这个问题，但旧版本需要手动干预。

我踩过的坑：某个查询参数从 'pending' 变成 'cancelled'（只有几千行），但 PG 复用了为 'pending' 生成的执行计划，结果走了全表扫描。

解决方法是在 SQL 层面加提示：

SET plan_cache_mode = force_custom_plan;

或者在 JDBC 连接串里禁用预备语句：

prepareThreshold=0

最终检查清单

每次做性能优化时，按这个顺序排查：

1. EXPLAIN (ANALYZE, BUFFERS)——先看执行计划，不看不动手
2. 检查 Seq Scan——如果表大且有 Seq Scan，大概率缺索引
3. 检查 Sort 节点——排序能否被索引覆盖？
4. 检查 Nested Loop 的 inner 表——有没有走 Seq Scan？加索引
5. 检查 Rows Removed by Filter——过滤了大量行说明索引没用上
6. 检查 Buffers——read 远大于 hit 说明内存配置不足
7. 看 work_mem 配置——是否频繁出现 File Sort、File Hash

延伸思考

这次优化让我重新想了一个问题：为什么很多 DBA 建议"默认不要加太多索引"？

因为索引不是免费的。每个索引都会拖慢写入速度（INSERT/UPDATE/DELETE 需要同时维护索引），还占用磁盘空间。关键是为查询建索引，不是为列建索引。

另外，PostgreSQL 16 引入了更激进的并行查询优化，我还没完全吃透。如果你在生产环境有 PG 16 的调优经验，欢迎分享。