使用EXPLAIN命令分析PostgreSQL查询
理解 PostgreSQL 中的 EXPLAIN 命令
在 PostgreSQL 数据库管理系统中，EXPLAIN 命令是一个强大的工具，用于分析查询计划。查询计划是数据库在执行查询时所采取的具体步骤，了解查询计划有助于优化查询性能。通过 EXPLAIN，数据库管理员和开发人员可以深入了解数据库如何解析、规划和执行 SQL 查询，从而找出性能瓶颈并进行针对性优化。
基本语法
EXPLAIN 的基本语法非常简单：
EXPLAIN [ ( option [, ...] ) ] statement;
其中 statement 是任何有效的 SQL 查询语句，比如 SELECT、INSERT、UPDATE 或 DELETE 等。option 是可选的，用于指定额外的输出格式或详细程度。常见的选项包括：
ANALYZE：除了显示查询计划，还实际执行查询并收集关于实际执行时间和返回行数的统计信息。VERBOSE：提供更详细的查询计划信息，包括表别名、列信息等。COSTS：显示查询计划中每个操作的估计成本，包括启动成本和总运行成本。成本是基于 PostgreSQL 的内部成本模型计算的，它考虑了诸如磁盘 I/O、CPU 处理等因素。
示例：简单的 SELECT 查询
假设我们有一个简单的 employees 表，包含 employee_id、name 和 salary 列。我们想查询所有工资大于 5000 的员工：
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2)
);
INSERT INTO employees (name, salary) VALUES
('Alice', 6000),
('Bob', 4500),
('Charlie', 7000);
EXPLAIN SELECT * FROM employees WHERE salary > 5000;
执行上述 EXPLAIN 命令后，我们会得到类似以下的输出：
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..15.10 rows=5 width=24)
Filter: (salary > '5000'::numeric)
(2 rows)
这里，Seq Scan on employees 表示 PostgreSQL 将对 employees 表进行顺序扫描。cost=0.00..15.10 是估计的启动成本和总运行成本。rows=5 是估计返回的行数，width=24 是估计每行的宽度（以字节为单位）。Filter: (salary > '5000'::numeric) 显示了应用的过滤条件。
深入理解查询计划组件
扫描类型
- 顺序扫描（Seq Scan）：正如前面示例中看到的，顺序扫描会逐行读取表中的每一条记录。这是最简单的扫描方式，但在大数据量和有特定过滤条件时可能效率低下。它适用于表较小或者没有合适索引的情况。例如，如果
employees表非常小，顺序扫描可能是最快的方式来找到符合条件的记录。 - 索引扫描（Index Scan）：当表上有合适的索引时，PostgreSQL 可能会选择索引扫描。假设我们在
salary列上创建一个索引：
CREATE INDEX idx_salary ON employees (salary);
EXPLAIN SELECT * FROM employees WHERE salary > 5000;
输出可能如下：
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_salary on employees (cost=0.29..8.31 rows=5 width=24)
Index Cond: (salary > '5000'::numeric)
(2 rows)
这里 Index Scan using idx_salary on employees 表示使用了 idx_salary 索引进行扫描。索引扫描通常比顺序扫描快，因为它可以直接定位到符合条件的记录，而不需要读取整个表。Index Cond 显示了索引条件。
3. 位图扫描（Bitmap Scan）：位图扫描通常在多个索引条件组合时使用。假设我们还有一个 department 列，并在其上创建索引，同时查询工资大于 5000 且在特定部门的员工：
ALTER TABLE employees ADD COLUMN department VARCHAR(50);
UPDATE employees SET department = 'HR' WHERE employee_id = 1;
UPDATE employees SET department = 'IT' WHERE employee_id = 2;
UPDATE employees SET department = 'HR' WHERE employee_id = 3;
CREATE INDEX idx_department ON employees (department);
EXPLAIN SELECT * FROM employees WHERE salary > 5000 AND department = 'HR';
输出可能包含位图扫描相关信息：
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on employees (cost=13.31..25.72 rows=1 width=34)
Recheck Cond: ((salary > '5000'::numeric) AND (department = 'HR'::character varying))
-> BitmapAnd (cost=13.31..13.31 rows=1 width=0)
-> Bitmap Index Scan on idx_salary (cost=0.00..5.96 rows=5 width=0)
Index Cond: (salary > '5000'::numeric)
-> Bitmap Index Scan on idx_department (cost=0.00..7.32 rows=1 width=0)
Index Cond: (department = 'HR'::character varying)
(6 rows)
位图扫描首先通过多个索引扫描生成位图，然后通过位图定位到实际的表行。BitmapAnd 操作将多个位图合并，以找到符合所有条件的行。
连接类型
当查询涉及多个表时，连接操作变得至关重要。PostgreSQL 支持多种连接类型，每种连接类型在不同场景下有不同的性能表现。
- 嵌套循环连接（Nested Loop Join）：这是最基本的连接类型。它会对一个表（外表）的每一行与另一个表（内表）的每一行进行匹配。假设我们有
employees表和departments表，departments表包含department_id和department_name列，employees表通过department_id关联到departments表：
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50)
);
INSERT INTO departments (department_name) VALUES
('HR'),
('IT');
UPDATE employees SET department_id = 1 WHERE employee_id IN (1, 3);
UPDATE employees SET department_id = 2 WHERE employee_id = 2;
EXPLAIN SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;
输出可能如下：
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=0.00..21.30 rows=3 width=54)
-> Seq Scan on employees (cost=0.00..15.10 rows=3 width=34)
-> Index Scan using departments_pkey on departments (cost=0.00..2.07 rows=1 width=20)
Index Cond: (department_id = employees.department_id)
(4 rows)
这里，外层循环对 employees 表进行顺序扫描，内层循环对 departments 表进行索引扫描，根据连接条件匹配行。
2. 哈希连接（Hash Join）：哈希连接适用于大数据量的连接操作。它首先在内存中构建一个哈希表（通常是较小的表），然后对另一个表进行扫描，通过哈希值快速找到匹配的行。假设 employees 表和 departments 表数据量较大：
-- 插入更多数据
INSERT INTO employees (name, salary, department_id) VALUES
('David', 5500, 1),
('Eve', 6500, 2);
INSERT INTO departments (department_name) VALUES
('Finance');
EXPLAIN SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;
输出可能显示哈希连接：
QUERY PLAN
-----------------------------------------------------------------------------
Hash Join (cost=15.10..26.32 rows=5 width=54)
Hash Cond: (employees.department_id = departments.department_id)
-> Seq Scan on employees (cost=0.00..15.10 rows=5 width=34)
-> Hash (cost=10.10..10.10 rows=3 width=20)
-> Seq Scan on departments (cost=0.00..10.10 rows=3 width=20)
(5 rows)
这里，departments 表首先被扫描并构建哈希表，然后 employees 表通过哈希连接与该哈希表匹配。
3. 合并连接（Merge Join）：合并连接要求连接的两个表在连接列上已经排序。如果表没有排序，PostgreSQL 可能会先对表进行排序操作。例如：
-- 假设表已经在连接列上排序（实际可能需要先排序）
EXPLAIN SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id ORDER BY employees.department_id, departments.department_id;
输出可能显示合并连接：
QUERY PLAN
-----------------------------------------------------------------------------
Merge Join (cost=15.10..26.32 rows=5 width=54)
Merge Cond: (employees.department_id = departments.department_id)
-> Seq Scan on employees (cost=0.00..15.10 rows=5 width=34)
-> Seq Scan on departments (cost=0.00..10.10 rows=3 width=20)
(4 rows)
合并连接通过同时扫描两个已排序的表，根据连接条件合并行。
解读 EXPLAIN 输出中的成本信息
成本模型基础
PostgreSQL 使用一个成本模型来估计查询计划中每个操作的成本。成本主要由两部分组成：启动成本（startup cost）和总运行成本（total cost）。启动成本是执行操作前的准备工作所需的成本，例如打开文件、初始化索引扫描等。总运行成本是完成整个操作所需的总成本，包括启动成本和实际执行操作的成本。
成本模型考虑了多种因素，如磁盘 I/O 成本、CPU 成本等。磁盘 I/O 成本通常基于读取的数据块数量和磁盘的读写速度。CPU 成本则基于执行操作所需的 CPU 周期，例如比较操作、排序操作等。
影响成本的因素
- 表大小：表越大，顺序扫描的成本越高。例如，一个包含数百万行的表进行顺序扫描的成本将远高于一个只有几百行的表。
- 索引使用：合适的索引可以显著降低查询成本。索引扫描通常比顺序扫描成本低，因为它减少了需要读取的数据量。但创建和维护索引也有一定成本，所以需要权衡。
- 连接操作：不同的连接类型成本不同。嵌套循环连接在小表连接时可能成本较低，但在大数据量时成本会急剧上升。哈希连接和合并连接在大数据量时通常更高效，但哈希连接需要额外的内存来构建哈希表，合并连接要求表在连接列上排序。
示例分析成本变化
假设我们有一个 orders 表和 customers 表，orders 表包含 order_id、customer_id 和 order_amount 列，customers 表包含 customer_id 和 customer_name 列。
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT,
order_amount DECIMAL(10, 2)
);
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100)
);
-- 插入一些数据
INSERT INTO customers (customer_name) VALUES
('Alice'),
('Bob');
INSERT INTO orders (customer_id, order_amount) VALUES
(1, 100.00),
(2, 200.00);
首先，我们进行一个简单的连接查询并查看成本：
EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
输出可能如下：
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=0.00..21.30 rows=2 width=64)
-> Seq Scan on orders (cost=0.00..15.10 rows=2 width=34)
-> Index Scan using customers_pkey on customers (cost=0.00..2.07 rows=1 width=30)
Index Cond: (customer_id = orders.customer_id)
(4 rows)
现在，假设 orders 表数据量大幅增加：
-- 插入更多订单数据
DO $$
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO orders (customer_id, order_amount) VALUES
(1, i * 1.00);
END LOOP;
END $$;
再次执行 EXPLAIN：
EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
输出的成本可能会显著增加，因为顺序扫描 orders 表的成本随着数据量增加而上升。
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=0.00..15021.30 rows=10002 width=64)
-> Seq Scan on orders (cost=0.00..15015.10 rows=10002 width=34)
-> Index Scan using customers_pkey on customers (cost=0.00..2.07 rows=1 width=30)
Index Cond: (customer_id = orders.customer_id)
(4 rows)
如果我们在 orders 表的 customer_id 列上创建索引：
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
输出可能显示成本降低，因为索引扫描 orders 表的成本低于顺序扫描：
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=0.29..20008.31 rows=10002 width=64)
-> Index Scan using idx_orders_customer_id on orders (cost=0.29..15008.31 rows=10002 width=34)
-> Index Scan using customers_pkey on customers (cost=0.00..2.07 rows=1 width=30)
Index Cond: (customer_id = orders.customer_id)
(4 rows)
使用 EXPLAIN ANALYZE 获得实际执行信息
基本用法
EXPLAIN ANALYZE 不仅显示查询计划，还会实际执行查询，并收集实际的执行时间和返回行数等信息。例如：
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 5000;
输出可能如下：
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..15.10 rows=5 width=24) (actual time=0.021..0.021 rows=2 loops=1)
Filter: (salary > '5000'::numeric)
Rows Removed by Filter: 1
(3 rows)
这里，(actual time=0.021..0.021 rows=2 loops=1) 显示了实际执行时间（开始时间和结束时间）以及实际返回的行数。Rows Removed by Filter 显示了在过滤过程中被移除的行数。
实际执行信息的重要性
实际执行信息可以帮助我们验证查询计划的准确性，并发现潜在的性能问题。有时，估计的查询计划成本与实际执行情况可能有较大差异。例如，由于统计信息不准确，PostgreSQL 可能选择了一个并非最优的查询计划。通过 EXPLAIN ANALYZE，我们可以看到实际执行时间和返回行数，从而判断是否需要调整查询或优化数据库配置。
假设我们有一个复杂的查询，涉及多个表连接和子查询：
EXPLAIN ANALYZE SELECT customer_name, SUM(order_amount)
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_name;
输出可能包含实际执行时间、循环次数等详细信息：
QUERY PLAN
-----------------------------------------------------------------------------
HashAggregate (cost=21.30..21.32 rows=2 width=40) (actual time=0.035..0.037 rows=2 loops=1)
-> Hash Join (cost=15.10..21.30 rows=2 width=40)
Hash Cond: (orders.customer_id = customers.customer_id)
-> Seq Scan on orders (cost=0.00..15.10 rows=2 width=34)
-> Hash (cost=10.10..10.10 rows=2 width=6)
-> Seq Scan on customers (cost=0.00..10.10 rows=2 width=6)
(6 rows)
通过分析这些实际执行信息，我们可以确定哪个操作花费的时间最长，例如如果 HashAggregate 的实际执行时间过长，我们可以考虑优化分组操作，如添加合适的索引或调整查询逻辑。
基于 EXPLAIN 分析进行查询优化
索引优化
- 添加索引：根据
EXPLAIN输出，如果发现顺序扫描成本过高且有合适的列可以创建索引，那么添加索引可能会显著提高查询性能。例如，如果EXPLAIN显示对某个表的顺序扫描，且查询条件频繁使用某个列，就可以在该列上创建索引。
-- 假设 EXPLAIN 显示对 products 表的顺序扫描，且查询经常使用 price 列
CREATE INDEX idx_price ON products (price);
- 删除不必要的索引：虽然索引可以提高查询性能，但过多的索引会增加插入、更新和删除操作的成本，因为每次数据变化时，所有相关索引都需要更新。通过
EXPLAIN分析，如果发现某些索引从未被使用，就可以考虑删除它们。
-- 假设发现 idx_unused 索引从未被使用
DROP INDEX idx_unused;
查询重写
- 简化子查询：复杂的子查询有时可以重写为连接操作，以提高查询性能。例如，一个相关子查询：
SELECT * FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders);
可以重写为连接和聚合操作：
SELECT o1.*
FROM orders o1
JOIN (SELECT AVG(order_amount) AS avg_amount FROM orders) o2
ON o1.order_amount > o2.avg_amount;
- 避免全表扫描：如果
EXPLAIN显示全表扫描，可以尝试通过添加索引、调整查询条件等方式避免全表扫描。例如，如果查询条件中有多个条件，可以确保至少有一个条件上有索引。
-- 假设原查询
EXPLAIN SELECT * FROM employees WHERE department = 'HR' AND salary > 5000;
-- 如果 department 列和 salary 列都没有索引，可以创建索引
CREATE INDEX idx_department_salary ON employees (department, salary);
配置优化
- 调整内存参数：对于哈希连接等操作，适当增加内存参数（如
shared_buffers、work_mem等）可以提高性能。shared_buffers控制 PostgreSQL 用于缓存数据库页面的内存量，增加它可以减少磁盘 I/O。work_mem用于排序和哈希表构建等操作，适当增加可以避免临时文件的创建，提高性能。
-- 修改 postgresql.conf 文件
shared_buffers = '2GB'
work_mem = '64MB'
- 更新统计信息：PostgreSQL 使用统计信息来生成查询计划。如果数据发生了显著变化（如大量数据插入、删除或更新），统计信息可能过时，导致查询计划不佳。可以使用
ANALYZE命令更新统计信息。
ANALYZE employees;
通过以上基于 EXPLAIN 分析的优化方法，可以显著提高 PostgreSQL 查询的性能，确保数据库系统高效运行。在实际应用中，需要综合考虑各种因素，不断优化查询和数据库配置，以满足业务需求。
通过深入理解 EXPLAIN 命令及其输出，开发人员和数据库管理员可以更好地优化 PostgreSQL 查询，提高数据库性能，从而提升整个应用系统的响应速度和稳定性。在处理复杂查询和大数据量时，EXPLAIN 及其相关分析优化方法是必不可少的工具。同时，持续关注数据库性能，定期进行 EXPLAIN 分析和优化，可以有效避免性能问题的积累，确保数据库长期稳定运行。在面对不同的业务场景和数据特点时，灵活运用 EXPLAIN 提供的信息，选择合适的优化策略，是实现高效数据库管理的关键。无论是简单的单表查询还是复杂的多表连接、子查询组合，EXPLAIN 都能为我们揭示查询执行的奥秘，帮助我们打造性能卓越的 PostgreSQL 数据库应用。