别让 PG 背锅：一次真实慢查询的 7 步排查记录

问题定位与解决方案

慢查询问题定位慢查询主要出现在以下SQL语句：

SELECT o.id, o.amount, u.nick FROM orders o JOIN users u ON u.id = o.user_id WHERE o.status = 'PAID' AND o.pay_time >= '2025-12-17 00:00:00' ORDER BY o.id DESC LIMIT 20;

执行时间为38秒，问题根源在于索引未有效过滤数据，导致全表扫描。

执行计划分析通过EXPLAIN (ANALYZE, BUFFERS)分析执行计划：

Limit (cost=0.56..2923.45 rows=20 width=48) (actual time=38042.213..38042.215 rows=20 loops=1) -> Nested Loop (cost=0.56..2342342.11 rows=16043 width=48) -> Index Scan Backward using orders_pkey on orders o (cost=0.56..823234.22 rows=16043 width=32) Filter: ((status = 'PAID'::order_status) AND (pay_time >= '2025-12-17 00:00:00'::timestamp)) Rows Removed by Filter: 12345678 -> Index Scan using users_pkey on users u (cost=0.56..8.77 rows=1 width=24) Index Cond: (id = o.user_id) Buffers: shared hit=52346 read=1234567 I/O Timings: read=30452.123

关键问题在于Rows Removed by Filter: 12345678，说明索引未有效过滤数据。

索引优化方案现有索引：

\d orders Indexes: "orders_pkey" PRIMARY KEY, btree (id) "idx_orders_status" btree (status) "idx_orders_paytime" btree (pay_time)

优化方案是创建复合索引：

CREATE INDEX CONCURRENTLY idx_orders_status_paytime_id ON orders (status, pay_time DESC, id DESC);

优化后执行计划：

Limit (cost=0.56..12.34 rows=20 width=48) (actual time=0.381..0.389 rows=20 loops=1) -> Index Scan using idx_orders_status_paytime_id on orders o ... Index Cond: ((status = 'PAID'::order_status) AND (pay_time >= '2025-12-17 00:00:00'::timestamp)) Buffers: shared hit=64

执行时间从38秒降至0.38毫秒。

参数优化调整以下参数以进一步提升性能：

ALTER SYSTEM SET random_page_cost = 1.1; ALTER SYSTEM SET work_mem = '32MB'; SELECT pg_reload_conf();

验证与效果优化后SQL平均执行时间从28秒降至0.4毫秒，QPS回升至1.9万。

常用诊断SQL

-- 当前活跃慢查询 SELECT pid, now()-xact_start, left(query,120) FROM pg_stat_activity WHERE state='active' AND now()-xact_start > interval '3 s'; -- 表+索引大小 SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10; -- 未使用的索引 SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan=0;