PostgreSQL 查询优化

使用 EXPLAIN 分析执行计划

EXPLAIN ANALYZE 是查询优化的起点，它展示 PostgreSQL 执行查询的详细步骤：

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

输出解读重点关注：

• 顺序扫描（Seq Scan） vs 索引扫描（Index Scan）
• 估算行数与实际行数的偏差（反映统计信息是否准确）
• 各步骤的实际执行时间

索引优化

B-Tree 索引

最常用的索引类型，支持等值查询和范围查询：

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

复合索引

对多个列的查询条件建立复合索引，注意列顺序（最左前缀原则）：

CREATE INDEX idx_orders_status_created ON orders(status, created_at);

查询 WHERE status = 'pending' ORDER BY created_at 可充分利用该索引。

部分索引

只对满足条件的数据建立索引，减少索引体积：

CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'active';

覆盖索引

通过包含所有需要字段的索引，避免回表查询：

CREATE INDEX idx_orders_covering ON orders(status) INCLUDE (id, total_amount);

查询优化技巧

避免 SELECT *

只查询需要的列，减少 I/O 和网络传输：

-- 不推荐
SELECT * FROM users;

-- 推荐
SELECT id, name, email FROM users;

使用 JOIN 而非子查询

PostgreSQL 对 JOIN 的优化通常优于子查询：

-- 子查询版本
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- JOIN 版本
SELECT orders.* FROM orders JOIN users ON orders.user_id = users.id WHERE users.status = 'active';

合理使用 LIMIT

使用 LIMIT 强制查询在取得足够结果后停止扫描：

SELECT * FROM products ORDER BY created_at DESC LIMIT 20;

统计信息维护

保持统计信息的准确性对查询优化器至关重要：

ANALYZE orders;                     -- 更新统计信息
SET default_statistics_target = 100; -- 提高统计精度（全局）
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 200; -- 针对特定列

配置调优

参数	说明	推荐值
shared_buffers	共享缓冲区大小	内存的 25%
work_mem	排序/哈希操作内存	根据查询复杂度调整
effective_cache_size	操作系统缓存估算	内存的 50%-75%
random_page_cost	随机 I/O 成本	SSD 设为 1.1

分区表

对于大表，使用分区提升查询和维护性能：

CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ,
    data JSONB
) PARTITION BY RANGE (created_at);

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

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');