PostgreSQL查询性能调优全攻略：从执行计划到索引优化

-- 基础计划（不执行）
EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01';

-- 带实际执行信息（推荐）
EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING, FORMAT TEXT) 
SELECT * FROM orders WHERE order_date > '2025-01-01';

Seq Scan on orders  (cost=0.00..48765.23 rows=102300 width=48) (actual time=0.023..123.456 rows=102300 loops=1)
  Filter: (order_date > '2025-01-01'::date)
  Rows Removed by Filter: 897700
  Buffers: shared hit=1000 read=5000
Planning Time: 0.123 ms
Execution Time: 125.678 ms

-- 查看表的统计信息最近更新时间
SELECT schemaname, tablename, last_analyze, last_autoanalyze 
FROM pg_stat_user_tables 
WHERE tablename = 'orders';

-- 查看列上的高频值（MCV）和直方图边界
SELECT attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats 
WHERE tablename = 'orders' AND attname = 'order_date';

-- 为特定列单独设置统计信息目标
ALTER TABLE orders ALTER COLUMN order_status SET STATISTICS 500;
ANALYZE orders;

-- 创建复合索引
CREATE INDEX idx_orders_date_status ON orders(order_date, order_status);

-- 以下查询会使用该索引
EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01' AND order_status = 'PAID';
-- 以下查询只能部分使用索引（仅用于 order_date 过滤）
EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01';

-- 只对未完成的订单建立索引
CREATE INDEX idx_orders_pending ON orders(order_id, created_at) 
WHERE order_status != 'COMPLETED';

-- 查询自动利用该部分索引
SELECT * FROM orders WHERE order_status = 'PENDING' AND created_at > now() - interval '7 days';

-- 常见场景：忽略大小写查询
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- 查询将走索引
SELECT * FROM users WHERE lower(email) = 'admin@example.com';

-- 为 JSONB 字段创建 GIN 索引（支持 ?、?|、?&、@> 等操作符）
CREATE INDEX idx_orders_metadata ON orders USING gin(metadata);

-- 查询
SELECT * FROM orders WHERE metadata @> '{"priority": "high"}';

CREATE INDEX idx_orders_date_brin ON orders USING brin(order_date) 
WITH (pages_per_range = 128);

-- 查询走 BRIN 索引
SELECT * FROM orders WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('idx_orders_date_status');

-- 错误：order_id 是整数，但传入字符串
SELECT * FROM orders WHERE order_id = '12345';  -- 隐式转换，导致索引失效

-- 正确
SELECT * FROM orders WHERE order_id = 12345;

-- 低效
SELECT * FROM customers 
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- 高效（EXISTS 通常可转为半连接，性能更优）
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.amount > 1000);

-- 低效：无法使用普通索引
SELECT * FROM orders WHERE date_trunc('day', order_date) = '2025-08-15';

-- 高效：使用范围查询
SELECT * FROM orders 
WHERE order_date >= '2025-08-15' AND order_date < '2025-08-16';

-- 低效：OFFSET 越大越慢
SELECT * FROM orders ORDER BY order_id LIMIT 20 OFFSET 100000;

-- 高效：使用“延迟关联”或“游标”
SELECT * FROM orders 
WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 1 OFFSET 100000) 
ORDER BY order_id LIMIT 20;

-- 低效：循环单条 INSERT/UPDATE

-- 高效：批量插入
INSERT INTO orders (id, customer_id, amount) 
SELECT * FROM unnest(
    ARRAY[1,2,3],
    ARRAY[101,102,103],
    ARRAY[99.9, 199.9, 299.9]
);

SET work_mem = '128MB';
SET enable_seqscan = off;  -- 强制走索引（仅测试用）

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最耗时的前 10 条 SQL
SELECT queryid, query, calls, total_time, mean_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;

SELECT 
    date_trunc('month', order_date) AS month,
    order_status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND customer_id IN (SELECT customer_id FROM vip_customers)
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT 
    date_trunc('month', o.order_date) AS month,
    o.order_status,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_amount
FROM orders o
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND EXISTS (SELECT 1 FROM vip_customers v WHERE v.customer_id = o.customer_id)
GROUP BY 1, 2
ORDER BY 1, 2;

CREATE INDEX idx_orders_cover ON orders(order_date, customer_id, amount) 
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';  -- 部分索引进一步缩小范围