PostgreSQL 查询优化

一、执行计划基础

1.1 EXPLAIN 命令

EXPLAIN 是分析查询性能的最重要工具，它展示了查询优化器选择的执行计划。

-- 基本用法
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 显示实际执行信息
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 完整选项
EXPLAIN (
    ANALYZE,      -- 实际执行并显示统计
    BUFFERS,      -- 显示缓冲区使用情况
    COSTS,        -- 显示成本估算（默认开启）
    TIMING,       -- 显示实际时间
    FORMAT TEXT   -- 输出格式：TEXT, XML, JSON, YAML
) SELECT * FROM users WHERE id = 1;

-- 不实际执行（只看计划）
EXPLAIN (COSTS, VERBOSE) SELECT * FROM users WHERE id = 1;

1.2 执行计划解读

1.3 常见扫描方式

-- 1. Seq Scan（顺序扫描/全表扫描）
EXPLAIN SELECT * FROM users;
-- Seq Scan on users (cost=0.00..155.00 rows=10000 width=100)

-- 2. Index Scan（索引扫描）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=100)
--   Index Cond: (id = 1)

-- 3. Index Only Scan（仅索引扫描，覆盖索引）
CREATE INDEX idx_users_email_name ON users(email, name);
EXPLAIN SELECT email, name FROM users WHERE email = 'a@b.com';
-- Index Only Scan using idx_users_email_name on users

-- 4. Bitmap Index Scan + Bitmap Heap Scan（位图扫描）
EXPLAIN SELECT * FROM users WHERE status IN (1, 2, 3);
-- Bitmap Heap Scan on users
--   Recheck Cond: (status = ANY ('{1,2,3}'::integer[]))
--   -> Bitmap Index Scan on idx_users_status
--        Index Cond: (status = ANY ('{1,2,3}'::integer[]))

-- 5. Parallel Seq Scan（并行顺序扫描）
EXPLAIN SELECT * FROM large_table WHERE col > 100;
-- Gather (workers_planned: 2)
--   -> Parallel Seq Scan on large_table

1.4 连接方式

-- 1. Nested Loop（嵌套循环连接）
-- 适合小表或有索引的情况
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = 1;
-- Nested Loop
--   -> Index Scan using users_pkey on users u
--   -> Index Scan using idx_orders_user_id on orders o

-- 2. Hash Join（哈希连接）
-- 适合大表连接，无索引
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- Hash Join
--   Hash Cond: (o.user_id = u.id)
--   -> Seq Scan on orders o
--   -> Hash
--        -> Seq Scan on users u

-- 3. Merge Join（归并连接）
-- 适合已排序的数据
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id ORDER BY u.id;
-- Merge Join
--   Merge Cond: (u.id = o.user_id)
--   -> Index Scan using users_pkey on users u
--   -> Index Scan using idx_orders_user_id on orders o

二、优化器原理

2.1 成本估算

-- 查看成本参数
SHOW seq_page_cost;
SHOW random_page_cost;

-- SSD 环境建议调整
SET random_page_cost = 1.1;  -- SSD 随机读快，降低此值
SET effective_io_concurrency = 200;  -- SSD 并发 IO 能力强

-- 查看表统计信息
SELECT
    relname,
    reltuples AS row_estimate,
    relpages AS page_count,
    pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname = 'users';

-- 查看列统计信息
SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'users'
AND attname = 'status';

2.2 统计信息

-- 更新统计信息
ANALYZE users;
ANALYZE VERBOSE users;

-- 更新整个数据库
ANALYZE;

-- 设置统计目标（采样精度）
ALTER TABLE users ALTER COLUMN email SET STATISTICS 1000;
-- 默认值 100，范围 0-10000

-- 查看自动分析配置
SHOW autovacuum_analyze_threshold;     -- 默认 50
SHOW autovacuum_analyze_scale_factor;  -- 默认 0.1
-- 触发条件：变更行数 > threshold + scale_factor * 行数

-- 查看上次分析时间
SELECT
    schemaname,
    relname,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup,
    n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'users';

2.3 优化器提示（非标准）

-- PostgreSQL 不支持传统的优化器提示
-- 但可以通过配置参数影响优化器决策

-- 禁用特定扫描方式
SET enable_seqscan = off;       -- 禁用顺序扫描
SET enable_indexscan = off;     -- 禁用索引扫描
SET enable_bitmapscan = off;    -- 禁用位图扫描
SET enable_indexonlyscan = off; -- 禁用仅索引扫描

-- 禁用特定连接方式
SET enable_hashjoin = off;      -- 禁用哈希连接
SET enable_mergejoin = off;     -- 禁用归并连接
SET enable_nestloop = off;      -- 禁用嵌套循环

-- 示例：强制使用索引
SET enable_seqscan = off;
EXPLAIN SELECT * FROM users WHERE status = 1;
SET enable_seqscan = on;  -- 恢复

-- 使用 pg_hint_plan 扩展（非官方）
-- 需要单独安装
-- SELECT /*+ IndexScan(users idx_users_email) */ * FROM users WHERE email = 'a@b.com';

三、SQL 优化技巧

3.1 WHERE 条件优化

-- 1. 避免对索引列使用函数
-- 不好
SELECT * FROM users WHERE UPPER(email) = 'A@B.COM';
-- 好（创建函数索引或修改查询）
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

-- 2. 避免隐式类型转换
-- 不好
SELECT * FROM users WHERE id = '100';
-- 好
SELECT * FROM users WHERE id = 100;

-- 3. 使用 EXISTS 替代 IN（大数据集）
-- 可能慢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 通常更快
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 4. 避免 NOT IN 使用 NOT EXISTS
-- NOT IN 有 NULL 陷阱
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
-- 更安全
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 5. 范围条件放在最后
-- 复合索引 (status, created_at)
SELECT * FROM users WHERE status = 1 AND created_at > '2024-01-01';

-- 6. 使用 BETWEEN 替代 >= AND <=
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- 7. 使用 ANY/ALL 替代多个 OR
-- 不好
SELECT * FROM users WHERE status = 1 OR status = 2 OR status = 3;
-- 好
SELECT * FROM users WHERE status = ANY(ARRAY[1, 2, 3]);
SELECT * FROM users WHERE status IN (1, 2, 3);

3.2 JOIN 优化

-- 1. 确保连接列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 2. 小表驱动大表
-- 优化器通常会自动选择，但可以调整 join_collapse_limit

-- 3. 减少返回列
-- 不好
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- 好
SELECT u.id, u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_id;

-- 4. 在连接前过滤数据
-- 不好
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 AND o.created_at > '2024-01-01';
-- 好（使用子查询或 CTE 预过滤）
WITH active_users AS (
    SELECT id FROM users WHERE status = 1
),
recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT * FROM active_users u JOIN recent_orders o ON u.id = o.user_id;

-- 5. 避免笛卡尔积
-- 确保所有表都有连接条件

-- 6. 考虑 LATERAL JOIN 替代相关子查询
-- 不好（多次执行子查询）
SELECT u.*, (
    SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id
) AS order_count
FROM users u;
-- 好
SELECT u.*, o.order_count
FROM users u
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS order_count FROM orders WHERE user_id = u.id
) o ON true;

3.3 子查询优化

-- 1. 标量子查询优化
-- 避免在 SELECT 中使用相关子查询
-- 不好
SELECT u.*,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count,
    (SELECT MAX(amount) FROM orders o WHERE o.user_id = u.id) AS max_amount
FROM users u;

-- 好（使用 JOIN + GROUP BY）
SELECT u.*, COUNT(o.id) AS order_count, MAX(o.amount) AS max_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 2. 将 IN 子查询转换为 JOIN
-- 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- JOIN
SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id;
-- EXISTS（通常最优）
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 3. 使用 CTE 提高可读性和性能
WITH order_stats AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
)
SELECT u.*, s.order_count, s.total_amount
FROM users u
LEFT JOIN order_stats s ON u.id = s.user_id;

3.4 分页优化

-- 传统分页（OFFSET 效率低）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;
-- 需要扫描 10010 行，丢弃前 10000 行

-- 使用键集分页（Keyset Pagination）
-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;
-- 假设最后一条 id = 100
-- 下一页
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;
-- 只需要扫描 10 行

-- 带排序的键集分页
-- 第一页
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 10;
-- 下一页（需要记录上一页最后一条的 created_at 和 id）
SELECT * FROM orders
WHERE (created_at, id) < ('2024-01-15 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- 估算总数（大表避免 COUNT(*)）
-- 精确计数（慢）
SELECT COUNT(*) FROM orders;
-- 估算计数（快）
SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'orders';

3.5 聚合优化

-- 1. 使用部分索引优化计数
CREATE INDEX idx_orders_pending ON orders(id) WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- 2. 使用物化视图缓存聚合结果
CREATE MATERIALIZED VIEW mv_order_stats AS
SELECT
    DATE_TRUNC('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_TRUNC('day', created_at);

CREATE UNIQUE INDEX ON mv_order_stats (day);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_stats;

-- 3. 使用 FILTER 替代 CASE WHEN
-- 不好
SELECT
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count
FROM orders;

-- 好
SELECT
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_count
FROM orders;

-- 4. 使用 GROUPING SETS 一次计算多个分组
SELECT
    COALESCE(status, 'ALL') AS status,
    COALESCE(TO_CHAR(created_at, 'YYYY-MM'), 'ALL') AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY GROUPING SETS (
    (status, DATE_TRUNC('month', created_at)),
    (status),
    ()
);

四、索引优化

4.1 索引设计原则

-- 1. 为高频查询创建索引
-- 分析慢查询日志，找出高频 WHERE 条件

-- 2. 复合索引列顺序
-- 等值条件列在前，范围条件列在后
-- 高选择性列在前
CREATE INDEX idx_orders_status_user_created ON orders(status, user_id, created_at);

-- 3. 使用部分索引减小索引大小
-- 只索引常用数据子集
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;

-- 4. 使用覆盖索引避免回表
-- INCLUDE 子句包含非索引列
CREATE INDEX idx_orders_user_status ON orders(user_id, status) INCLUDE (amount, created_at);

-- 5. 使用表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

-- 6. 删除冗余索引
-- idx(a) 被 idx(a, b) 包含
-- idx(a, b) 和 idx(a, b, c) 保留后者

4.2 索引使用分析

-- 查看索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,      -- 索引扫描次数
    idx_tup_read,  -- 通过索引读取的元组数
    idx_tup_fetch  -- 通过索引获取的元组数
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- 查找未使用的索引
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查看索引与表大小比
SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    round(pg_indexes_size(relid) * 100.0 / pg_relation_size(relid), 2) AS index_ratio
FROM pg_stat_user_tables
ORDER BY pg_relation_size(relid) DESC;

4.3 索引膨胀处理

-- 查看索引膨胀（需要 pgstattuple 扩展）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    avg_leaf_density,  -- 叶子页填充率
    leaf_fragmentation -- 碎片率
FROM pg_stat_user_indexes
JOIN pgstatindex(indexrelname) ON true
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重建膨胀的索引
REINDEX INDEX idx_users_email;

-- 并发重建（不阻塞写入）
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;
REINDEX TABLE CONCURRENTLY users;

五、并行查询

5.1 并行查询配置

-- 查看并行查询配置
SHOW max_parallel_workers_per_gather;  -- 每个 Gather 节点的并行工作者数
SHOW max_parallel_workers;             -- 总并行工作者数
SHOW max_worker_processes;             -- 总后台工作者数
SHOW parallel_tuple_cost;              -- 并行传输元组的成本
SHOW parallel_setup_cost;              -- 启动并行工作者的成本
SHOW min_parallel_table_scan_size;     -- 触发并行扫描的最小表大小

-- 设置并行度
SET max_parallel_workers_per_gather = 4;

-- 表级别设置
ALTER TABLE large_table SET (parallel_workers = 4);

-- 禁用并行查询
SET max_parallel_workers_per_gather = 0;

5.2 并行查询类型

-- 1. 并行顺序扫描
EXPLAIN SELECT COUNT(*) FROM large_table;
-- Finalize Aggregate
--   -> Gather
--        Workers Planned: 2
--        -> Partial Aggregate
--             -> Parallel Seq Scan on large_table

-- 2. 并行索引扫描
EXPLAIN SELECT * FROM large_table WHERE indexed_col > 1000;
-- Gather
--   Workers Planned: 2
--   -> Parallel Index Scan using idx on large_table

-- 3. 并行连接
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
-- Gather
--   Workers Planned: 2
--   -> Parallel Hash Join
--        -> Parallel Seq Scan on t1
--        -> Parallel Hash
--             -> Parallel Seq Scan on t2

-- 4. 并行聚合
EXPLAIN SELECT status, COUNT(*) FROM large_table GROUP BY status;
-- Finalize GroupAggregate
--   -> Gather Merge
--        -> Partial GroupAggregate
--             -> Parallel Seq Scan on large_table

-- 不支持并行的场景
-- - 写操作（INSERT, UPDATE, DELETE）
-- - 使用 cursor
-- - 部分系统函数
-- - 用户定义函数（除非标记为 PARALLEL SAFE）

5.3 并行查询优化

-- 1. 确保表足够大
-- 小表不会触发并行查询
SELECT pg_size_pretty(pg_relation_size('users'));

-- 2. 标记函数为并行安全
CREATE OR REPLACE FUNCTION my_function(x INTEGER)
RETURNS INTEGER
LANGUAGE SQL
PARALLEL SAFE  -- 标记为并行安全
AS $$
    SELECT x * 2
$$;

-- 3. 避免序列化瓶颈
-- Gather 节点是单线程的，尽量减少返回行数

-- 4. 调整成本参数
-- 如果并行查询比预期少，可能是成本估算问题
SET parallel_tuple_cost = 0.01;
SET parallel_setup_cost = 100;

-- 强制使用并行
SET force_parallel_mode = on;

六、查询诊断

6.1 慢查询日志

-- 配置慢查询日志
-- postgresql.conf
-- log_min_duration_statement = 1000  -- 记录超过 1 秒的查询
-- log_statement = 'none'             -- 不记录所有语句

-- 查看当前配置
SHOW log_min_duration_statement;

-- 实时设置
SET log_min_duration_statement = '500ms';

-- 分析日志
-- 使用 pgBadger 分析日志
-- pgbadger /var/log/postgresql/postgresql.log -o report.html

-- 使用 pg_stat_statements 扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最耗时的查询
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((total_exec_time / sum(total_exec_time) OVER()) * 100, 2) AS percent,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 查看最频繁的查询
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

-- 重置统计
SELECT pg_stat_statements_reset();

6.2 实时查询监控

-- 查看当前活动查询
SELECT
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 查看长时间运行的查询
SELECT
    pid,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
AND query_start < now() - interval '5 minutes';

-- 查看等待的查询
SELECT
    pid,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

-- 终止查询
SELECT pg_cancel_backend(pid);    -- 取消查询
SELECT pg_terminate_backend(pid); -- 终止连接

-- 查看锁等待
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

6.3 查询性能基准

-- 使用 pgbench 进行基准测试
-- 初始化测试数据
-- pgbench -i -s 10 mydb

-- 运行测试
-- pgbench -c 10 -j 2 -T 60 mydb
-- -c: 并发客户端数
-- -j: 线程数
-- -T: 测试时间（秒）

-- 自定义测试脚本
-- 创建文件 test.sql
-- \set id random(1, 100000)
-- SELECT * FROM pgbench_accounts WHERE aid = :id;

-- 运行自定义测试
-- pgbench -c 10 -T 60 -f test.sql mydb

-- 查看 pgbench 表大小
SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relname LIKE 'pgbench%';

七、优化案例

7.1 案例：订单查询优化

-- 原始查询（慢）
EXPLAIN ANALYZE
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 问题分析
-- 1. 可能全表扫描
-- 2. 排序开销大

-- 优化步骤
-- 1. 创建复合索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- 2. 使用部分索引（如果 pending 订单占比小）
CREATE INDEX idx_orders_pending_created ON orders(created_at DESC)
WHERE status = 'pending';

-- 3. 确保 user_id 有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 优化后的执行计划
EXPLAIN ANALYZE
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;
-- 应该使用 Index Scan

7.2 案例：统计查询优化

-- 原始查询（慢）
EXPLAIN ANALYZE
SELECT
    DATE_TRUNC('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

-- 优化方案 1：创建表达式索引
CREATE INDEX idx_orders_day ON orders(DATE_TRUNC('day', created_at));

-- 优化方案 2：使用物化视图
CREATE MATERIALIZED VIEW mv_daily_orders AS
SELECT
    DATE_TRUNC('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE_TRUNC('day', created_at);

CREATE UNIQUE INDEX ON mv_daily_orders(day);

-- 查询物化视图
SELECT * FROM mv_daily_orders
WHERE day >= '2024-01-01' AND day < '2024-02-01'
ORDER BY day;

-- 定期刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_orders;

-- 优化方案 3：使用分区表
CREATE TABLE orders_partitioned (
    id BIGSERIAL,
    user_id BIGINT,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

八、总结

查询优化检查清单

检查项	说明
是否使用索引	EXPLAIN 确认执行计划
索引是否合适	检查选择性和覆盖度
统计信息是否准确	定期 ANALYZE
是否有锁等待	检查 pg_stat_activity
是否可以并行	调整并行参数
是否有 N+1 查询	使用 JOIN 或批量查询
分页是否优化	使用键集分页
聚合是否可缓存	考虑物化视图

性能优化原则

1. 先测量，再优化：使用 EXPLAIN ANALYZE 确认问题
2. 从最大瓶颈开始：优先处理最慢的查询
3. 索引是银弹：但不是万能的，要权衡写入性能
4. 减少数据量：过滤、分页、投影
5. 利用缓存：物化视图、应用层缓存
6. 持续监控：定期检查慢查询和索引使用