[PostgreSQL]算法服务查询慢？PostgreSQL执行计划深度拆解

-- LEVEL 1: 基础计划树
EXPLAIN 
SELECT user_id, AVG(score) FROM predictions GROUP BY user_id;

-- LEVEL 2: 实际执行 + 缓冲区统计
EXPLAIN (ANALYZE, BUFFERS) 
SELECT user_id, AVG(score) FROM predictions WHERE created_at > '2024-01-01' GROUP BY user_id;

-- 输出解读：
-- Buffers: shared hit=12345 read=0  -- 命中缓存，无磁盘读
-- I/O Timings: read=0.000 write=0.000  -- I/O时间可忽略

-- LEVEL 3: 详细Verbose + 输出格式控制
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT user_id, COUNT(*) FROM user_features GROUP BY user_id;

-- JSON格式便于程序化分析，包含节点成本、行数、过滤条件等结构化数据

-- LEVEL 4: 自动捕获慢查询计划
CREATE TABLE slow_query_plans (
    query_id BIGINT,
    query_text TEXT,
    plan_json JSONB,
    captured_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建慢查询捕获函数
CREATE OR REPLACE FUNCTION capture_slow_queries()
RETURNS void AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN 
        SELECT query, calls, mean_exec_time, queryid
        FROM pg_stat_statements
        WHERE mean_exec_time > 1000  -- 平均执行超过1秒
    LOOP
        INSERT INTO slow_query_plans (query_id, query_text, plan_json)
        VALUES (
            rec.queryid,
            rec.query,
            (EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) EXECUTE rec.query)::jsonb
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Seq Scan：全表扫描（最坏情况）
EXPLAIN SELECT * FROM predictions WHERE model_version = 'v1';
-- ->  Seq Scan on predictions  (cost=0.00..123456.78 rows=10000 width=48)

-- Index Scan：索引扫描（精确查找）
EXPLAIN SELECT * FROM predictions WHERE prediction_id = 12345;
-- ->  Index Scan using predictions_pkey on predictions  (cost=0.42..2.44 rows=1 width=48)

-- Index Only Scan：覆盖索引（不回表）
EXPLAIN SELECT model_version, score FROM predictions WHERE prediction_id = 12345;
-- ->  Index Only Scan using idx_covering on predictions  (cost=0.42..2.44 rows=1 width=12)

-- Bitmap Heap Scan：位图扫描（多条件组合）
EXPLAIN SELECT * FROM predictions 
WHERE model_version = 'v1' AND created_at > '2024-01-01';
-- ->  Bitmap Heap Scan on predictions (cost=100.20..1234.56 rows=1000 width=48)
--      ->  Bitmap Index Scan on idx_model_created (cost=0.00..100.10 rows=1000 width=0)

-- Nested Loop Join（小表驱动大表）
EXPLAIN SELECT * FROM users u JOIN predictions p ON u.user_id = p.user_id;
-- ->  Nested Loop  (cost=0.87..23456.78 rows=10000 width=96)
--      ->  Seq Scan on users u (cost=0.00..100.00 rows=1000 width=48)
--      ->  Index Scan on predictions p (cost=0.87..23.45 rows=10 width=48)

-- Hash Join（大表关联，哈希分桶）
EXPLAIN SELECT * FROM users u JOIN predictions p ON u.user_id = p.user_id;
-- ->  Hash Join  (cost=1234.56..34567.89 rows=100000 width=96)
--      ->  Seq Scan on users u (cost=0.00..1000.00 rows=100000 width=48)
--      ->  Hash  (cost=1234.56..1234.56 rows=100000 width=48)
--            ->  Seq Scan on predictions p (cost=0.00..1000.00 rows=100000 width=48)

-- Merge Join（已排序数据合并）
EXPLAIN SELECT * FROM users u JOIN predictions p ON u.user_id = p.user_id 
ORDER BY u.user_id;
-- ->  Merge Join  (cost=2345.67..45678.90 rows=100000 width=96)
--      ->  Index Scan using users_pkey on users u (cost=0.42..12345.67 rows=100000 width=48)
--      ->  Sort  (cost=1234.56..2345.67 rows=100000 width=48)

-- 算法特征查询，线上耗时4.3秒
SELECT 
    user_id,
    AVG(prediction_score) as avg_score,
    MAX(confidence) as max_conf
FROM model_predictions
WHERE created_at::date = '2024-01-15'  -- 注意：类型转换！
GROUP BY user_id
LIMIT 1000;

EXPLAIN (ANALYZE, BUFFERS) 
-- 问题发现：
-- ->  Seq Scan on model_predictions (cost=0.00..123456.78 rows=100000 width=48)
--      Filter: (created_at::date = '2024-01-15'::date)
--      Rows Removed by Filter: 9876543  -- 删除了98%的行！
-- 根本原因：created_at::date导致索引失效

-- 方案I：改写为范围查询（推荐）
SELECT ...
WHERE created_at >= '2024-01-15'::timestamp 
  AND created_at < '2024-01-16'::timestamp;

-- 执行计划变为：
-- ->  Index Scan using idx_created_at on model_predictions (cost=0.87..1234.56 rows=10000 width=48)
--      Index Cond: (created_at >= '2024-01-15 00:00:00')
-- 耗时：从4.3秒降至45毫秒

-- 方案II：创建函数索引（不推荐，维护成本高）
CREATE INDEX idx_created_at_date 
ON model_predictions ((created_at::date));

-- 表只有100行，索引查找反而更慢
EXPLAIN (ANALYZE) SELECT * FROM model_versions WHERE version = 'v1';
-- ->  Seq Scan on model_versions (cost=0.00..1.01 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=1)
--     Filter: (version = 'v1'::text)
--     Rows Removed by Filter: 99

-- 优化器判断：全表100个块，索引查找需要3个块（root+branch+leaf），成本相近

-- 需要返回表中80%的数据
EXPLAIN (ANALYZE) SELECT * FROM predictions WHERE created_at > '2023-01-01';
-- ->  Seq Scan on predictions (cost=0.00..12345.67 rows=800000 width=48)
--     Filter: (created_at > '2023-01-01 00:00:00+08')
--     Rows Removed by Filter: 200000

-- 索引扫描需要随机IO，而Seq Scan是顺序IO，带宽更高

-- 在函数或表达式上查询
EXPLAIN (ANALYZE) SELECT * FROM predictions WHERE LENGTH(model_version) > 10;
-- ->  Seq Scan on predictions (cost=0.00..12345.67 rows=50000 width=48)
--     Filter: (length(model_version) > 10)

-- 优化：创建函数索引
CREATE INDEX idx_model_version_len ON predictions (LENGTH(model_version));
EXPLAIN (ANALYZE) SELECT * FROM predictions WHERE LENGTH(model_version) > 10;
-- ->  Bitmap Index Scan on idx_model_version_len

-- 查询只访问索引列
EXPLAIN (ANALYZE, BUFFERS)
SELECT model_version, COUNT(*) 
FROM predictions 
WHERE created_at > '2024-01-01'
GROUP BY model_version;

-- 索引设计：
CREATE INDEX idx_covering ON predictions (created_at, model_version);
-- 不是覆盖索引，因为COUNT(*)需要回表检查可见性

-- 正确覆盖索引：
CREATE INDEX idx_covering_v2 ON predictions (created_at, model_version) 
INCLUDE (prediction_id);  -- INCLUDE不影响索引排序，但存储值

-- Index Only Scan需要"可见性映射"（VM）
EXPLAIN (ANALYZE, BUFFERS)
SELECT model_version FROM predictions WHERE created_at > '2024-01-01';

-- 输出：
-- ->  Index Only Scan using idx_covering on predictions
--      Heap Fetches: 0  -- 0说明所有页都标记为全可见

-- 如果Heap Fetches > 0，说明需要回表检查，可能是VACUUM不足
VACUUM ANALYZE predictions;  -- 清理死元组并更新可见性映射

-- 查询：找出VIP用户或高风险用户
EXPLAIN (ANALYZE) 
SELECT * FROM users 
WHERE user_level = 'VIP' OR risk_score > 0.8;

-- 执行计划：
-- ->  Seq Scan on users (cost=0.00..12345.67 rows=1234 width=48)
--     Filter: ((user_level = 'VIP'::text) OR (risk_score > 0.8))

-- 问题：无法同时使用两个索引

-- 分别创建索引
CREATE INDEX idx_user_level ON users (user_level);
CREATE INDEX idx_risk_score ON users (risk_score);

-- 使用Bitmap Heap Scan合并结果
EXPLAIN (ANALYZE)
SELECT * FROM users 
WHERE user_level = 'VIP' OR risk_score > 0.8;

-- 执行计划：
-- ->  Bitmap Heap Scan on users (cost=12.34..456.78 rows=1234 width=48)
--      Recheck Cond: ((user_level = 'VIP'::text) OR (risk_score > 0.8))
--      ->  BitmapOr  (cost=12.34..12.34 rows=1234 width=0)
--            ->  Bitmap Index Scan on idx_user_level (cost=0.00..5.67 rows=500 width=0)
--                  Index Cond: (user_level = 'VIP'::text)
--            ->  Bitmap Index Scan on idx_risk_score (cost=0.00..6.78 rows=800 width=0)
--                  Index Cond: (risk_score > 0.8)

-- 优化：避免Recheck（减少随机IO）
SET enable_bitmapscan = on;
SET effective_io_concurrency = 200;  -- SSD优化

-- 场景：查询VIP用户的预测结果（VIP用户只有500人）
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.user_id, u.user_level, p.prediction_score
FROM users u
JOIN predictions p ON u.user_id = p.user_id
WHERE u.user_level = 'VIP';

-- 执行计划：
-- ->  Nested Loop (cost=0.87..12345.67 rows=500 width=24)
--      ->  Bitmap Heap Scan on users (cost=5.67..45.78 rows=500 width=8)
--            ->  Bitmap Index Scan on idx_user_level (cost=0.00..5.55 rows=500 width=0)
--                  Index Cond: (user_level = 'VIP'::text)
--      ->  Index Scan using idx_predictions_user on predictions p (cost=0.87..23.45 rows=10 width=16)
--            Index Cond: (user_id = u.user_id)

-- 优化器决策：users表返回500行（小表），使用Nested Loop，每次循环走索引
-- 性能：12毫秒完成

-- 场景：查询所有用户的预测结果（users表1000万行）
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.user_id, p.prediction_score
FROM users u
JOIN predictions p ON u.user_id = p.user_id;

-- 危险执行计划：
-- ->  Nested Loop (cost=0.00..123456789.01 rows=10000000 width=24)
--      ->  Seq Scan on users (cost=0.00..12345.67 rows=10000000 width=8)
--      ->  Index Scan on predictions p (cost=0.00..12.34 rows=1 width=16)
--            Index Cond: (user_id = u.user_id)

-- 性能灾难：1000万×1次索引查找 = 23秒

-- 关闭Nested Loop（会话级）
SET enable_nestloop = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.user_id, p.prediction_score
FROM users u
JOIN predictions p ON u.user_id = p.user_id;

-- 执行计划：
-- ->  Hash Join (cost=12345.67..567890.12 rows=10000000 width=24)
--      ->  Seq Scan on users (cost=0.00..12345.67 rows=10000000 width=8)
--      ->  Hash (cost=12345.67..12345.67 rows=10000000 width=16)
--            ->  Seq Scan on predictions (cost=0.00..12345.67 rows=10000000 width=16)

-- 性能提升：23秒 → 4.5秒
-- 原因：Hash Join批量处理，内存哈希表查找O(1)

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.user_id, p.prediction_score
FROM users u
JOIN predictions p ON u.user_id = p.user_id;

-- 观察：
-- ->  Hash (cost=12345.67..12345.67 rows=10000000 width=16) (actual rows=10000000 loops=1)
--      Buffers: temp read=12345 written=12345  -- 使用了临时文件

-- 问题：work_mem不足，溢出到磁盘

-- 增大work_mem（会话级）
SET work_mem = '1GB';

-- 再次执行：
-- Buffers: temp read=0 written=0  -- 内存足够，无溢出
-- 性能：4.5秒 → 3.2秒

-- 监控内存使用
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
-- 查看: Memory Usage: 1024kB 峰值内存

-- 场景：时间序列关联（已按时间排序）
EXPLAIN (ANALYZE, BUFFERS)
SELECT t1.ts, t1.value, t2.value
FROM sensor_data t1
JOIN sensor_data t2 ON t1.ts = t2.ts
WHERE t1.device_id = 1001 AND t2.device_id = 1002
ORDER BY t1.ts;

-- 执行计划：
-- ->  Merge Join (cost=23456.78..56789.01 rows=10000 width=40)
--      ->  Index Scan using idx_sensor_ts on sensor_data t1 (cost=0.42..1234.56 rows=10000 width=20)
--            Index Cond: ((device_id = 1001) AND (ts IS NOT NULL))
--      ->  Index Scan using idx_sensor_ts on sensor_data t2 (cost=0.42..1234.56 rows=10000 width=20)
--            Index Cond: ((device_id = 1002) AND (ts IS NOT NULL))

-- 优势：数据已排序，Merge Join只需一次遍历O(N)
-- 性能：2.3秒（Hash Join需要4.1秒）

-- 当优化器错误选择Hash Join时
SET enable_hashjoin = off;
SET enable_mergejoin = on;

-- 查看表的统计信息
SELECT * FROM pg_stats 
WHERE schemaname = 'public' AND tablename = 'predictions';

-- 关键字段：
-- - attname: 列名
-- - n_distinct: 不同值数量（-1表示唯一）
-- - most_common_vals: 高频值（MCV）
-- - most_common_freqs: 高频值频率
-- - histogram_bounds: 直方图边界

-- 查看单列统计
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'predictions' AND attname = 'model_version';

-- 结果：
-- attname       | n_distinct | correlation
-- ---------------+------------+-------------
-- model_version |         10 |        0.95

-- correlation接近1，说明物理存储与逻辑排序一致，Index扫描更高效

-- 1. 导入1000万数据
COPY predictions FROM '/data/batch.csv';

-- 2. 立即查询（统计信息仍显示1000行）
EXPLAIN (ANALYZE) SELECT * FROM predictions WHERE model_version = 'v3';
-- ->  Seq Scan (cost=0.00..10.00 rows=100 width=48)  -- 预估100行，实际100万行！

-- 3. 手动收集统计信息
ANALYZE predictions;

-- 4. 再次查询
EXPLAIN (ANALYZE) SELECT * FROM predictions WHERE model_version = 'v3';
-- ->  Index Scan (cost=0.42..1234.56 rows=1000000 width=48)  -- 正确预估

-- postgresql.conf
autovacuum_analyze_scale_factor = 0.05;  -- 5%变更就触发ANALYZE（默认10%）
autovacuum_analyze_threshold = 5000;      -- 最少5000行变更

-- 针对特定表（批量导入后立即分析）
ALTER TABLE predictions SET (
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_analyze_threshold = 1000
);

-- 查看score列的直方图
SELECT histogram_bounds
FROM pg_stats
WHERE tablename = 'predictions' AND attname = 'score';

-- 结果：
-- histogram_bounds: {0.01,0.11,0.22,0.33,0.44,0.55,0.66,0.77,0.88,0.99}

-- 优化器估算：查询score > 0.8的选择性 ≈ 20%

-- 查看model_version的高频值
SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'predictions' AND attname = 'model_version';

-- 结果：
-- most_common_vals: {v1,v2,v3}
-- most_common_freqs: {0.5,0.3,0.2}

-- 优化器估算：model_version = 'v1'的选择性 = 50%

-- 查询高危用户（risk_score > 0.95），线上超时30秒
SELECT * FROM users WHERE risk_score > 0.95 AND is_active = true;

EXPLAIN (ANALYZE, BUFFERS) 
-- 输出：
-- ->  Seq Scan on users (cost=0.00..123456.78 rows=100 width=48)
--     Filter: ((risk_score > 0.95) AND (is_active = true))
--     Rows Removed by Filter: 9876543

-- 问题：统计信息中risk_score > 0.95的估算是100行，实际是100万行

-- 检查直方图
SELECT histogram_bounds FROM pg_stats 
WHERE tablename = 'users' AND attname = 'risk_score';
-- histogram_bounds未包含0.95以上的边界（数据倾斜）

-- 检查实际分布
SELECT COUNT(*) FROM users WHERE risk_score > 0.95;
-- 结果：1,234,567行（统计信息严重滞后）

-- 1. 手动收集统计信息
ANALYZE users;

-- 2. 增加统计目标（更精细的直方图）
ALTER TABLE users ALTER COLUMN risk_score SET STATISTICS 1000;  -- 默认100

-- 3. 重新收集
ANALYZE users;

-- 4. 验证执行计划
EXPLAIN (ANALYZE) SELECT * FROM users WHERE risk_score > 0.95;
-- ->  Bitmap Heap Scan (cost=1234.56..23456.78 rows=1234567 width=48)
--      ->  Bitmap Index Scan on idx_risk_score (cost=0.00..1234.56 rows=1234567 width=0)
-- 执行时间：从30秒降至2.3秒

CREATE INDEX idx_user_feature ON user_features (user_id, feature_date, feature_type);

-- 有效查询（符合最左前缀）
EXPLAIN SELECT * FROM user_features WHERE user_id = 12345;  -- ✓
EXPLAIN SELECT * FROM user_features WHERE user_id = 12345 AND feature_date = '2024-01-01';  -- ✓
EXPLAIN SELECT * FROM user_features WHERE user_id = 12345 AND feature_type = 'behavior';  -- ✓（部分）

-- 无效查询（跳过最左列）
EXPLAIN SELECT * FROM user_features WHERE feature_date = '2024-01-01';  -- ✗ Seq Scan
EXPLAIN SELECT * FROM user_features WHERE feature_type = 'behavior';   -- ✗ Seq Scan

-- 降序索引（时间序查询优化）
CREATE INDEX idx_ts_desc ON model_predictions (created_at DESC, model_version);

-- 查询最近N条记录时，避免排序
EXPLAIN (ANALYZE)
SELECT * FROM model_predictions 
WHERE model_version = 'v3'
ORDER BY created_at DESC 
LIMIT 100;

-- 执行计划：
-- ->  Limit (cost=0.42..12.34 rows=100 width=48)
--      ->  Index Scan Backward using idx_ts_desc on model_predictions
--            Index Cond: ((model_version = 'v3'::text) AND (created_at < '2024-01-31 23:59:59'))

-- 错误：在低选择性列上建索引
CREATE INDEX idx_model_version ON predictions (model_version);  -- 只有10个版本，选择性=0.1

-- 正确：在高选择性列上建索引
CREATE INDEX idx_prediction_id ON predictions (prediction_id);  -- 唯一，选择性=1.0

-- 组合策略：低选择性+高选择性
CREATE INDEX idx_model_created ON predictions (model_version, created_at DESC);

-- 算法场景：查询用户最近30天、最近10次行为的特征
CREATE INDEX idx_user_behavior_lookup ON user_behavior (
    user_id, 
    event_date DESC,
    event_type
) INCLUDE (event_value);

-- 查询自动使用Index Only Scan
SELECT 
    user_id,
    event_type,
    event_value
FROM user_behavior
WHERE user_id = 12345
  AND event_date > CURRENT_DATE - 30
ORDER BY event_date DESC
LIMIT 10;

-- 性能：从120毫秒降至4毫秒

-- 索引大小：10亿行 × 8字节 ≈ 8GB
CREATE INDEX idx_sensor_ts ON sensor_data (ts);

-- 查询性能：
EXPLAIN (ANALYZE) SELECT * FROM sensor_data 
WHERE ts > NOW() - INTERVAL '1 day';
-- Index Scan: 45毫秒（良好）
-- 但索引维护成本极高：每次插入需要更新8GB索引

-- BRIN索引大小：10亿行 / 128块范围 × 8字节 ≈ 62MB（99%节省）
CREATE INDEX idx_sensor_ts_brin ON sensor_data 
USING BRIN (ts) WITH (pages_per_range = 128);

-- 查询性能：
EXPLAIN (ANALYZE) SELECT * FROM sensor_data 
WHERE ts > NOW() - INTERVAL '1 day';
-- Bitmap Heap Scan: 89毫秒（略慢，但可接受）

-- 写入性能提升：从5000 TPS提升至25000 TPS（5倍）

-- 查看索引大小与膨胀率
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan < 1000  -- 扫描次数少（可能是未使用索引）
ORDER BY pg_relation_size(indexrelid) DESC;

-- 输出：
-- schemaname | tablename | indexname            | index_size | idx_scan
-- ------------+-----------+----------------------+------------+----------
-- public     | predictions | idx_old_unused       | 45 GB      |        0
-- public     | predictions | idx_model_created    | 12 GB      | 123456

-- 方式I：REINDEX（锁表）
REINDEX INDEX idx_predictions_user;
-- 阻塞写入，仅维护窗口使用

-- 方式II：CONCURRENTLY（在线重建）
REINDEX INDEX CONCURRENTLY idx_predictions_user;
-- 不锁表，但速度慢2倍

-- 方式III：pg_repack（第三方工具）
pg_repack -d algorithm_db -t predictions --no-order
-- 在线重建表和索引，几乎无锁

#!/usr/bin/env python3
# index_advisor.py - 基于pg_stat_statements建议索引
import psycopg2

def suggest_indexes(conn):
    cur = conn.cursor()
    
    # 查询高频Seq Scan
    cur.execute("""
        SELECT query, calls, mean_exec_time
        FROM pg_stat_statements
        WHERE query LIKE '%WHERE%' 
          AND query NOT LIKE '%CREATE INDEX%'
          AND mean_exec_time > 1000
        ORDER BY calls DESC
        LIMIT 10
    """)
    
    for query, calls, time in cur.fetchall():
        print(f"慢查询（调用{calls}次，平均{time}ms）: {query[:100]}...")
        
        # 提取WHERE条件中的列
        # 简化版：实际需SQL解析
        import re
        match = re.search(r'WHERE\s+(.+?)(?:\s+ORDER|\s+LIMIT|$)', query, re.IGNORECASE)
        if match:
            where_clause = match.group(1)
            print(f"建议索引列: {where_clause}")

if __name__ == '__main__':
    conn = psycopg2.connect("host=localhost dbname=algorithm_db")
    suggest_indexes(conn)
    conn.close()

-- postgresql.conf
max_worker_processes = 32;              -- 系统总worker数
max_parallel_workers = 24;              -- 并行worker数上限
max_parallel_workers_per_gather = 8;    -- 单查询最大并行度

-- 会话级设置
SET max_parallel_workers_per_gather = 8;
SET parallel_setup_cost = 100;          -- 降低并行门槛（默认1000）
SET parallel_tuple_cost = 0.01;         -- 降低并行传输成本（默认0.1）

EXPLAIN (ANALYZE, VERBOSE)
SELECT device_id, AVG(temperature), STDDEV(temperature)
FROM sensor_data
WHERE ts > NOW() - INTERVAL '7 days'
GROUP BY device_id;

-- 输出：
-- ->  Finalize GroupAggregate (cost=12345.67..56789.01 rows=10000 width=40)
--      ->  Gather (cost=12345.67..45678.90 rows=80000 width=40)
--            Workers Planned: 8
--            Workers Launched: 8
--            ->  Partial HashAggregate (cost=0.00..33456.78 rows=10000 width=40)
--                  ->  Parallel Index Only Scan using idx_sensor_ts on sensor_data (cost=0.42..12345.67 rows=123456 width=16)

-- 全表聚合查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM sensor_data;

-- 非并行：Seq Scan，耗时12秒
-- 并行8 workers：Parallel Seq Scan，耗时1.8秒（6.6倍加速）

-- 索引覆盖查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id, AVG(temperature)
FROM sensor_data
WHERE ts > NOW() - INTERVAL '1 day'
GROUP BY device_id;

-- 非并行：Index Only Scan，耗时4.5秒
-- 并行8 workers：Parallel Index Only Scan，耗时0.8秒

-- 大表关联
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.user_id, AVG(p.score)
FROM users u
JOIN predictions p ON u.user_id = p.user_id
GROUP BY u.user_id;

-- 执行计划：
-- ->  Finalize GroupAggregate
--      ->  Gather (Workers Planned: 8)
--            ->  Partial HashAggregate
--                  ->  Parallel Hash Join
--                        ->  Parallel Seq Scan on users
--                        ->  Parallel Hash
--                              ->  Parallel Seq Scan on predictions

-- 查询：并行扫描后排序
EXPLAIN (ANALYZE)
SELECT * FROM sensor_data 
WHERE ts > NOW() - INTERVAL '1 day'
ORDER BY ts DESC
LIMIT 100;

-- 执行计划：
-- ->  Limit (rows=100)
--      ->  Gather (workers=8)
--            ->  Parallel Index Scan
--                  ->  Sort (rows=1000)
--                        ->  Parallel Index Scan

-- 问题：每个worker排序1000行，Gather后需再次排序，重复劳动
-- 性能：并行反而比串行慢20%

-- 不在ORDER BY/LIMIT前Gather
SET max_parallel_workers_per_gather = 0;  -- 禁用并行

-- 改写：先LIMIT再并行
WITH limited_data AS (
    SELECT * FROM sensor_data 
    WHERE ts > NOW() - INTERVAL '1 day'
    LIMIT 10000  -- 先限制数据量
)
SELECT * FROM limited_data
ORDER BY ts DESC
LIMIT 100;

-- 查询：按user_id聚合，但某个用户有1000万条记录
SELECT user_id, COUNT(*) FROM predictions GROUP BY user_id;

-- 并行执行计划：
-- ->  Finalize HashAggregate
--      ->  Gather (workers=8)
--            ->  Partial HashAggregate
--                 -- Worker 0: 处理1000万行（慢）
--                 -- Workers 1-7: 各处理1万行（空闲）

-- 性能：并行加速比仅1.5x（理想8x）

-- 使用哈希分桶均匀分布
SET parallel_leader_participation = off;  -- 主进程不参与计算

-- 或改写为两阶段聚合
WITH partial_agg AS (
    SELECT mod(user_id, 8) as bucket, user_id, COUNT(*) as cnt
    FROM predictions
    GROUP BY bucket, user_id
)
SELECT user_id, SUM(cnt) 
FROM partial_agg
GROUP BY user_id;

-- 查看并行查询统计
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%Parallel%'
ORDER BY total_exec_time DESC;

-- 查看并行worker活动
SELECT * FROM pg_stat_activity WHERE backend_type = 'parallel worker';

-- 原始SQL：CTE被多次引用
WITH high_value_users AS (
    SELECT user_id, SUM(order_amount) as total_amount
    FROM orders
    WHERE order_date > CURRENT_DATE - 30
    GROUP BY user_id
    HAVING SUM(order_amount) > 10000
)
SELECT * FROM high_value_users hv
JOIN user_features uf ON hv.user_id = uf.user_id
WHERE hv.total_amount > 20000;

-- 执行计划：CTE物化一次，然后被扫描两次
-- CTE Scan on high_value_users (cost=0.00..1234.56 rows=100 width=16)
-- CTE Scan on high_value_users (cost=0.00..1234.56 rows=100 width=16)  -- 重复！
-- 性能：2.3秒

-- 方式I：使用子查询（自动内联）
SELECT hv.*, uf.*
FROM (
    SELECT user_id, SUM(order_amount) as total_amount
    FROM orders
    WHERE order_date > CURRENT_DATE - 30
    GROUP BY user_id
    HAVING SUM(order_amount) > 10000
) hv
JOIN user_features uf ON hv.user_id = uf.user_id
WHERE hv.total_amount > 20000;

-- 执行计划：物化子查询，只扫描一次
-- 性能：1.1秒

-- 方式II：使用WITH NOT MATERIALIZED（PG 12+）
WITH high_value_users AS NOT MATERIALIZED (
    SELECT user_id, SUM(order_amount) as total_amount
    FROM orders
    WHERE order_date > CURRENT_DATE - 30
    GROUP BY user_id
    HAVING SUM(order_amount) > 10000
)
SELECT * FROM high_value_users hv
JOIN user_features uf ON hv.user_id = uf.user_id
WHERE hv.total_amount > 20000;

-- 执行计划：CTE被内联到主查询
-- 性能：0.8秒

-- 原始：多次调用函数
SELECT 
    user_id,
    get_user_embedding(user_id) as embedding,
    calculate_similarity(get_user_embedding(user_id), target_vector) as sim
FROM users
LIMIT 100;

-- 问题：get_user_embedding被调用两次（性能浪费）

-- 使用LATERAL缓存结果
SELECT 
    u.user_id,
    ue.embedding,
    calculate_similarity(ue.embedding, target_vector) as sim
FROM users u,
LATERAL get_user_embedding(u.user_id) as ue(embedding)
LIMIT 100;

-- 执行计划：Lateral Function Scan，embedding只计算一次
-- 性能：从2.1秒降至0.9秒

-- 表结构
CREATE TABLE model_configs (
    config_id SERIAL,
    hyperparams JSONB
);

CREATE INDEX idx_hyperparams_gin ON model_configs USING GIN (hyperparams);

-- 查询（无法使用索引）
EXPLAIN SELECT * FROM model_configs 
WHERE hyperparams @> '{"learning_rate": 0.01}';
-- ->  Seq Scan

-- 方式I：使用BTREE索引（等值查询）
CREATE INDEX idx_learning_rate ON model_configs ((hyperparams->>'learning_rate'));

EXPLAIN SELECT * FROM model_configs 
WHERE hyperparams->>'learning_rate' = '0.01';
-- ->  Index Scan using idx_learning_rate

-- 方式II：使用GIN表达式索引
CREATE INDEX idx_hyperparams_path ON model_configs 
USING GIN ((hyperparams @> '{"learning_rate": 0.01}'));

-- 方式III：重构为数组存储
ALTER TABLE model_configs ADD COLUMN lr FLOAT;
UPDATE model_configs SET lr = (hyperparams->>'learning_rate')::float;
CREATE INDEX idx_lr ON model_configs (lr);

-- 原始：unnest导致内存爆炸
SELECT user_id, unnest(embeddings) as emb_value
FROM user_embeddings;

-- 优化：使用LATERAL + generate_subscripts
SELECT user_id, embeddings[i] as emb_value
FROM user_embeddings,
LATERAL generate_subscripts(embeddings, 1) as gs(i);

-- 查询：模型版本v1或分数大于0.9
SELECT * FROM predictions 
WHERE model_version = 'v1' OR score > 0.9;

-- 执行计划：Seq Scan

-- 方式I：拆分为UNION
SELECT * FROM predictions WHERE model_version = 'v1'
UNION ALL
SELECT * FROM predictions WHERE score > 0.9 AND model_version != 'v1';

-- 各自使用索引：
-- ->  Bitmap Heap Scan on predictions (model_version = 'v1')
-- ->  Index Scan on idx_score (score > 0.9)

-- 方式II：使用位图扫描（自动优化）
-- 确保两个条件都有索引
CREATE INDEX idx_model_version ON predictions (model_version);
CREATE INDEX idx_score ON predictions (score);

-- 优化器会自动选择Bitmap Heap Scan

-- 1. 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 2. 配置（postgresql.conf）
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.track_planning = on

-- 3. 查询慢查询
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- 平均超过1秒
ORDER BY total_exec_time DESC
LIMIT 10;

-- 创建慢查询日志表
CREATE TABLE slow_query_log (
    id SERIAL PRIMARY KEY,
    query_id BIGINT,
    query_text TEXT,
    plan_json JSONB,
    captured_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建捕获函数
CREATE OR REPLACE FUNCTION capture_slow_query(planid BIGINT)
RETURNS void AS $$
DECLARE
    query_text TEXT;
    plan_json JSONB;
BEGIN
    SELECT query INTO query_text FROM pg_stat_statements WHERE queryid = planid;
    
    EXECUTE format('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) %s', query_text)
    INTO plan_json;
    
    INSERT INTO slow_query_log (query_id, query_text, plan_json)
    VALUES (planid, query_text, plan_json);
END;
$$ LANGUAGE plpgsql;

-- 定时任务（每小时捕获最慢的5个查询）
SELECT cron.schedule('capture-slow-queries', '0 * * * *', $$
    SELECT capture_slow_query(queryid)
    FROM pg_stat_statements
    ORDER BY total_exec_time DESC
    LIMIT 5
$$);

-- 查询：在过去1小时中执行的全表扫描
SELECT 
    query,
    calls,
    total_exec_time,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%Seq Scan%'
  AND calls > 100
  AND mean_exec_time > 1000;

-- 输出示例：
-- query: SELECT * FROM predictions WHERE model_version = $1
-- calls: 1234
-- mean_exec_time: 4500ms  -- 全表扫描4.5秒

#!/usr/bin/env python3
# auto_index_advisor.py
import psycopg2
import json
import re

def analyze_plans(conn):
    cur = conn.cursor()
    
    # 从slow_query_log获取执行计划
    cur.execute("SELECT query_id, query_text, plan_json FROM slow_query_log")
    
    for query_id, query_text, plan_json in cur.fetchall():
        plan = json.loads(plan_json)
        
        # 递归遍历节点
        def walk_node(node, depth=0):
            node_type = node.get('Node Type', '')
            
            # 检测Seq Scan
            if node_type == 'Seq Scan':
                relation = node.get('Relation Name', '')
                rows = node.get('Actual Rows', 0)
                print(f"🔴 全表扫描: {relation} (行数: {rows})")
                print(f"   查询: {query_text[:100]}...")
                
                # 提取WHERE条件
                filter_cond = node.get('Filter', '')
                if filter_cond:
                    cols = re.findall(r'(\w+)\s*=', filter_cond)
                    if cols:
                        print(f"   建议索引: CREATE INDEX idx_{relation}_{cols[0]} ON {relation} ({cols[0]});")
            
            # 检测慢节点
            total_time = node.get('Actual Total Time', 0)
            if total_time > 1000:
                print(f"⚠️  慢节点: {node_type} ({total_time}ms)")
            
            # 递归子节点
            for key in ['Plans', 'Plan']:
                if key in node:
                    walk_node(node[key], depth+1)
        
        walk_node(plan[0]['Plan'])

if __name__ == '__main__':
    conn = psycopg2.connect("host=localhost dbname=algorithm_db")
    analyze_plans(conn)
    conn.close()

# 自动生成可视化报告
# 在pgAdmin中右键查询 -> EXPLAIN -> 图形化展示
# 显示节点成本、行数、实际vs估算差异

# 安装
npm install -g pev2

# 使用
psql -d algorithm_db -c "EXPLAIN (ANALYZE, VERBOSE, FORMAT JSON) SELECT ..." > plan.json
pev2 plan.json  # 浏览器打开交互式执行计划图

-- 创建执行计划监控视图
CREATE VIEW v_plan_anomalies AS
SELECT 
    query_id,
    query_text,
    (plan_json->>'Plan'->>'Actual Total Time')::float as actual_time,
    (plan_json->>'Plan'->>'Plan Rows')::int as est_rows,
    (plan_json->>'Plan'->>'Actual Rows')::int as actual_rows,
    (plan_json->>'Plan'->>'Actual Rows')::int / NULLIF((plan_json->>'Plan'->>'Plan Rows')::int, 0) as est_ratio
FROM slow_query_log
WHERE (plan_json->>'Plan'->>'Actual Total Time')::float > 5000
  AND (plan_json->>'Plan'->>'Actual Rows')::int / NULLIF((plan_json->>'Plan'->>'Plan Rows')::int, 0) > 10;

-- est_ratio > 10 表示估算偏差超过10倍