从Explain到执行：手把手优化PostgreSQL慢查询的5个关键步骤

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS, SETTINGS, WAL) 
SELECT ...;

-- 问题查询：统计年度大额订单
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.name, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND c.country = 'US'
GROUP BY o.order_id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > 1000
ORDER BY total DESC
LIMIT 100;

Limit  (cost=234567.89..234578.90 rows=100 width=40) (actual time=3567.890..3578.901 rows=100 loops=1)
  ->  Sort  (cost=234567.89..234789.01 rows=88448 width=40) (actual time=3567.882..3572.345 rows=88448 loops=1)
        Sort Key: (sum((oi.quantity * oi.unit_price))) DESC
        Sort Method: external merge  Disk: 10240kB
        ->  GroupAggregate  (cost=123456.78..234567.89 rows=88448 width=40) (actual time=1234.567..3456.789 rows=88448 loops=1)
              Group Key: o.order_id, c.name
              Filter: (sum((oi.quantity * oi.unit_price)) > 1000)
              Rows Removed by Filter: 123456
              ->  Sort  (cost=123456.78..126543.21 rows=1234572 width=40) (actual time=1234.500..2345.678 rows=1234572 loops=1)
                    Sort Key: o.order_id, c.name
                    Sort Method: external sort  Disk: 51200kB
                    ->  Hash Join  (cost=9876.54..87654.32 rows=1234572 width=40) (actual time=234.567..987.654 rows=1234572 loops=1)
                          Hash Cond: (oi.order_id = o.order_id)
                          ->  Seq Scan on order_items oi  (cost=0.00..65432.10 rows=2345678 width=16) (actual time=0.123..345.678 rows=2345678 loops=1)
                          ->  Hash  (cost=8765.43..8765.43 rows=87654 width=32) (actual time=234.500..234.500 rows=87654 loops=1)
                                Buckets: 131072  Batches: 1  Memory Usage: 10240kB
                                ->  Hash Join  (cost=5432.10..8765.43 rows=87654 width=32) (actual time=123.456..210.987 rows=87654 loops=1)
                                      Hash Cond: (o.customer_id = c.customer_id)
                                      ->  Seq Scan on orders o  (cost=0.00..7654.32 rows=87654 width=16) (actual time=0.012..98.765 rows=87654 loops=1)
                                            Filter: ((order_date >= '2023-01-01'::date) AND (order_date <= '2023-12-31'::date))
                                            Rows Removed by Filter: 987654
                                      ->  Hash  (cost=4321.09..4321.09 rows=87654 width=20) (actual time=123.400..123.400 rows=87654 loops=1)
                                            Buckets: 131072  Batches: 1  Memory Usage: 8192kB
                                            ->  Seq Scan on customers c  (cost=0.00..4321.09 rows=87654 width=20) (actual time=0.010..98.765 rows=87654 loops=1)
                                                  Filter: (country = 'US'::text)
                                                  Rows Removed by Filter: 345678
Planning Time: 12.345 ms
Execution Time: 4578.901 ms

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp15965.0", size 2048000MB
STATEMENT:  SELECT ... -- 复杂聚合查询

SHOW work_mem; -- 输出: 4MB

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...; -- 显示Disk: 2048000kB

排序数据量 = 2048000kB ≈ 2GB
推荐work_mem = 2GB * 1.2 = 2.4GB

-- 会话级调整（立即生效）
SET work_mem = '2GB';

-- 配置文件调整（postgresql.conf）
work_mem = 256MB              # 默认值提升64倍
maintenance_work_mem = 4GB     # 针对VACUUM等操作
shared_buffers = 16GB          # 系统内存的25%
effective_cache_size = 48GB    # 系统内存的75%

优化前：
Sort Method: external merge  Disk: 2048000kB

优化后：
Sort Method: quicksort  Memory: 2048000kB
Execution Time: 4567ms → 1567ms (减少65.7%)

-- 按数据库设置不同内存参数
ALTER DATABASE ecommerce SET work_mem = '512MB';

-- 按用户设置内存参数
ALTER USER report_user SET work_mem = '1GB';

-- 使用内存预算机制
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.1;
SET parallel_setup_cost = 1000;

SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
-- 执行时间: 1250ms

CREATE INDEX idx_orders_order_date_brin 
ON orders USING BRIN (order_date) 
WITH (pages_per_range = 64, autosummarize = on);
-- 索引大小: 1.2MB (vs 表大小60GB)

Index Scan using idx_orders_order_date_brin on orders
  (cost=0.00..1234.56 rows=87654 width=48) 
  (actual time=12.34..234.56 rows=87654 loops=1)
Execution Time: 245ms (减少80.4%)

SELECT * FROM orders
WHERE status = 'shipped'
  AND order_date >= '2023-06-01'
  AND category_id = 42
ORDER BY order_date DESC
LIMIT 100;

-- 列顺序决策：高筛选度在前，避免排序
CREATE INDEX idx_orders_status_category_date 
ON orders (status, category_id, order_date DESC)
INCLUDE (customer_id, total_amount);
-- 包含INCLUDE列避免回表

-- 使用部分索引减少大小
WHERE status != 'cancelled';

SELECT 
  n_distinct AS distinct_values,
  correlation
FROM pg_stats 
WHERE tablename = 'orders' 
  AND attname IN ('status','category_id','order_date');

-- 反例
SELECT * FROM orders;

-- 正例
SELECT order_id, customer_id, order_date FROM orders;

-- 反例（应用层循环）
FOR order_id IN (SELECT order_id FROM orders WHERE ...)
  SELECT * FROM order_items WHERE order_id = $1;

-- 正例（单次JOIN）
SELECT o.order_id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE ...;

-- Keyset分页（游标分页）
SELECT * FROM orders
WHERE order_date < '2023-06-15'  -- 上次最后一条
AND order_id > 123456            -- 防止日期重复
ORDER BY order_date DESC, order_id DESC
LIMIT 100;

-- 使用游标
DECLARE orders_cursor SCROLL CURSOR FOR
SELECT * FROM orders ORDER BY order_date DESC;
FETCH 100 FROM orders_cursor;

-- 反例（索引失效）
SELECT * FROM orders 
WHERE DATE_TRUNC('month', order_date) = '2023-01-01';

-- 正例（可索引）
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
  AND order_date < '2023-02-01';

-- 反例（CTE作为优化屏障）
WITH cte AS (
  SELECT * FROM orders WHERE status = 'shipped'
)
SELECT * FROM cte JOIN order_items ...

-- 正例（内联子查询）
SELECT * FROM 
(SELECT * FROM orders WHERE status = 'shipped') AS shipped_orders
JOIN order_items ...

-- 创建父表
CREATE TABLE orders (
    order_id BIGSERIAL,
    order_date DATE NOT NULL,
    customer_id BIGINT,
    total_amount NUMERIC
) PARTITION BY RANGE (order_date);

-- 创建子分区（每月分区）
CREATE TABLE orders_202301 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
    WITH (parallel_workers = 4);
    
CREATE TABLE orders_202302 PARTITION OF orders
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
    WITH (parallel_workers = 4);

-- 创建默认分区捕获异常数据
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 分区索引（本地索引）
CREATE INDEX idx_orders_partition_customer 
    ON orders_202301 (customer_id);

-- 创建物化视图
CREATE MATERIALIZED VIEW daily_sales_summary
AS
SELECT
    DATE_TRUNC('day', order_date) AS sale_day,
    product_id,
    COUNT(*) AS order_count,
    SUM(quantity) AS total_quantity,
    SUM(quantity * unit_price) AS total_revenue
FROM order_items
GROUP BY sale_day, product_id
WITH DATA;

-- 定时刷新（每天凌晨）
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- 查询优化效果
原始查询: 12.5s → 物化视图查询: 45ms (提升278倍)

-- 安装pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- 查找最慢查询
SELECT 
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows / calls AS avg_rows,
    shared_blks_read,
    temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 索引使用统计
SELECT 
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    100 * idx_scan / (SELECT SUM(idx_scan) FROM pg_stat_all_indexes) AS usage_percent
FROM pg_stat_all_indexes
WHERE schemaname NOT LIKE 'pg_%'
ORDER BY idx_scan ASC;

-- 自动重建低效索引
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN 
        SELECT indexrelid::regclass AS index_name
        FROM pg_stat_all_indexes
        WHERE idx_scan < 100  -- 很少使用
          AND pg_relation_size(indexrelid) > 1048576  -- 大于1MB
    LOOP
        EXECUTE 'REINDEX INDEX CONCURRENTLY ' || rec.index_name;
        RAISE NOTICE 'Reindexed %', rec.index_name;
    END LOOP;
END $$;

-- 智能VACUUM配置
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_threshold = 2000,
    autovacuum_analyze_scale_factor = 0.01
);

pgbench -i -s 100 ecommerce  # 初始化100倍数据
pgbench -c 50 -j 4 -T 600 ecommerce

Prometheus + Grafana + pg_exporter 监控体系
├── 数据库层：QPS、连接数、缓存命中率
├── 查询层：慢查询TOP10、索引效率
├── 系统层：CPU、内存、磁盘I/O
└── 业务层：关键事务响应时间

-- 重建所有索引
REINDEX DATABASE CONCURRENTLY ecommerce;

-- 更新统计信息
ANALYZE VERBOSE;

-- 检查表膨胀
SELECT * FROM pg_bloat_check;

-- 安全备份
pg_basebackup -D /backup -Ft -z -P

SET jit = on;
SET jit_above_cost = 100000;
-- 对复杂分析查询提升2-3倍性能

-- 强制并行度
SET max_parallel_workers_per_gather = 8;
SET parallel_tuple_cost = 0;
SET parallel_setup_cost = 0;

-- 监控并行效率
EXPLAIN (ANALYZE) SELECT ...;

-- 创建扩展统计
CREATE STATISTICS orders_stats (dependencies)
ON status, category_id FROM orders;

-- 增加采样率
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

-- 使用表分区
CREATE TABLE orders_active PARTITION ...;
CREATE TABLE orders_archive PARTITION ...;

-- 不同存储策略
ALTER TABLE orders_active SET TABLESPACE ssd_tablespace;
ALTER TABLE orders_archive SET TABLESPACE hdd_tablespace;

/* 诊断工具 */
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;  -- 查询计划分析
SELECT * FROM pg_stat_activity WHERE state = 'active'; -- 活动查询
SELECT * FROM pg_locks WHERE granted = false;    -- 锁等待
SELECT pg_size_pretty(pg_total_relation_size('orders')); -- 表大小

/* 索引管理 */
CREATE INDEX CONCURRENTLY ...;      -- 在线创建索引
REINDEX INDEX CONCURRENTLY ...;     -- 在线重建索引
DROP INDEX CONCURRENTLY ...;        -- 在线删除索引

/* 统计信息 */
ANALYZE VERBOSE orders;             -- 更新统计信息
SELECT * FROM pg_stats WHERE tablename = 'orders'; -- 查看统计

/* 性能维护 */
VACUUM (VERBOSE, ANALYZE) orders;   -- 清理并分析
CHECKPOINT;                         -- 强制检查点
pg_repack --table orders ecommerce  -- 在线表重组

/* 配置管理 */
SELECT pg_reload_conf();            -- 重载配置
ALTER SYSTEM SET work_mem = '64MB'; -- 持久化配置