PostgreSQL查询优化与执行计划深度解析

PostgreSQL查询优化与执行计划深度解析

一、执行计划基础

1.1 EXPLAIN命令详解

PostgreSQL的EXPLAIN命令是查询优化的核心工具，它显示查询执行器的计划。

基本用法

EXPLAIN SELECT * FROM users WHERE id = 1;
​

详细分析模式

-- ANALYZE：实际执行查询并获取真实执行时间
-- VERBOSE：显示详细信息
-- BUFFERS：显示缓冲区使用情况
-- FORMAT：指定输出格式(TEXT/XML/JSON/YAML)

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS OFF) 
SELECT * FROM orders WHERE user_id = 100;
​

输出字段说明

• Node Type：执行节点类型(Seq Scan、Index Scan、Nested Loop等)
• Relation Name：涉及的表名
• Alias：表的别名
• Startup Cost：启动成本(输出第一行前的成本)
• Total Cost：总成本
• Plan Rows：预计返回行数
• Plan Width：平均行宽度(字节)
• Actual Time：实际执行时间(ms)
• Actual Rows：实际返回行数
• Actual Loops：实际执行次数
• Buffers：缓冲区使用情况

1.2 执行节点类型解析

扫描节点(Scan Nodes)

1. Seq Scan(顺序扫描)

   • 全表扫描，适合小表或大表大部分数据
   • 成本：全表I/O

   -- 小表场景
   CREATE TABLE config (
       key VARCHAR(50) PRIMARY KEY,
       value TEXT
   );
   -- 只有几十条记录，Seq Scan是最佳选择
   ​

2. Index Scan(索引扫描)

   • 通过索引访问，适合返回少量数据
   • 成本：索引I/O + 表I/O

   -- 创建索引
   CREATE INDEX idx_orders_user_id ON orders(user_id);
   
   -- 查看执行计划
   EXPLAIN SELECT * FROM orders WHERE user_id = 100;
   -- 输出：Index Scan using idx_orders_user_id on orders
   ​

3. Index Only Scan(仅索引扫描)

   • 最优扫描方式，无需访问表数据
   • 要求：查询的所有字段都在索引中
   • PostgreSQL通过VM(Visibility Map)判断可见性

   -- 创建覆盖索引
   CREATE INDEX idx_orders_cover ON orders(user_id, order_no, amount);
   
   -- 执行计划
   EXPLAIN SELECT user_id, order_no, amount FROM orders WHERE user_id = 100;
   -- 输出：Index Only Scan using idx_orders_cover on orders
   ​

4. Bitmap Index Scan(位图索引扫描)

   • 适合多个索引条件的OR查询
   • 先通过位图找到符合条件的TID，再访问表

   CREATE INDEX idx_orders_status ON orders(status);
   CREATE INDEX idx_orders_create_time ON orders(create_time);
   
   EXPLAIN SELECT * FROM orders 
   WHERE status = 1 OR create_time > \2026-01-01'
   ​