PostgreSQL 查询优化：从 30 秒到 30 毫秒的调优之路

# PostgreSQL 查询优化：从 30 秒到 30 毫秒 ## 问题现场 一条报表查询在生产环境执行超过 30 秒，导致前端超时。 ```sql SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at BETWEEN '2024-01-01' AND '2024-12-31' AND o.status = 'completed' GROUP BY u.name ORDER BY total DESC LIMIT 50; ``` ## 第一步：EXPLAIN ANALYZE ```sql EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...; -- 输出关键信息： -- Seq Scan on orders (cost=0.00..185432.00 rows=5000000) -- Actual time: 28543.234ms ``` **问题**：orders 表全表扫描（500 万行），没有命中索引。 ## 第二步：创建复合索引 ```sql CREATE INDEX idx_orders_status_created ON orders (status, created_at) INCLUDE (user_id, amount); ``` 使用 INCLUDE 将查询需要的列都放入索引，实现 Index-Only Scan。 优化后：12 秒 -> 依然不够快。 ## 第三步：查询重写 用 CTE 先过滤再 JOIN： ```sql WITH filtered_orders AS ( SELECT user_id, amount FROM orders WHERE status = 'completed' AND created_at BETWEEN '2024-01-01' AND '2024-12-31' ) SELECT u.name, COUNT(*) as order_count, SUM(fo.amount) as total FROM filtered_orders fo JOIN users u ON u.id = fo.user_id GROUP BY u.name ORDER BY total DESC LIMIT 50; ``` 优化后：800 毫秒。 ## 第四步：分区表 按月对 orders 表做范围分区： ```sql CREATE TABLE orders ( id BIGSERIAL, user_id BIGINT NOT NULL, amount NUMERIC(12,2), status VARCHAR(20), created_at TIMESTAMP NOT NULL ) PARTITION BY RANGE (created_at); CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); ``` 分区裁剪后只扫描相关月份，最终耗时：**30 毫秒**。 ## 总结 优化路径：加索引 -> 查询重写 -> 分区表，逐步缩小数据扫描范围。