PostgreSQL 执行计划详解

一、PostgreSQL 执行计划怎么看

PostgreSQL 的执行计划是指执行 SQL 语句时数据库系统自动分析并生成的查询计划，它可以帮助我们了解 SQL 查询语句的执行过程，以及优化查询语句的性能。

在 PostgreSQL 中，我们可以使用 EXPLAIN 命令来获取执行计划，其语法如下：

EXPLAIN [ ANALYZE ] [ VERBOSE ] query

其中，ANALYZE 和 VERBOSE 都是可选参数。ANALYZE 参数表示执行语句并返回实际的执行计划，VERBOSE 参数可以输出更详细的执行信息。

例如，我们可以使用以下语句获取查询 employees 表的前 10 行数据的执行计划：

EXPLAIN SELECT * FROM employees LIMIT 10;

执行以上语句后，可以得到如下的执行计划：

                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Limit  (cost=0.43..1.27 rows=10 width=244)
   ->  Seq Scan on employees  (cost=0.43..9409.43 rows=300024 width=244)
(2 rows)

上述执行计划中，QUERY PLAN 表示查询计划的输出标志，Limit 表示由 Limit 子句引起的查询计划，cost 字段表示此计划的成本估计，rows 字段表示此计划返回的行数估计，width 表示此计划返回的每个元组的宽度。

二、PostgreSQL 的执行计划

PostgreSQL 的执行计划主要有以下几种类型：

1. Seq Scan（顺序扫描）

Seq Scan 是一种最简单的扫描方式，它逐行扫描数据表，将符合条件的记录返回，属于全表扫描操作。可以使用以下 SQL 语句获取 Seq Scan 的执行计划：

EXPLAIN SELECT * FROM employees WHERE age > 40;

执行以上语句后，可以得到如下的执行计划：

                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..13760.24 rows=14977 width=244)
   Filter: (age > 40)
(2 rows)

上述执行计划中，Seq Scan 表示全表扫描，Filter 表示过滤条件。

2. Index Scan（索引扫描）

Index Scan 是一种基于索引的扫描方式，可以快速地定位符合查询条件的数据，它通常会伴随着一个 Seq Scan 或者 Index Only Scan 来使用。可以使用以下 SQL 语句获取 Index Scan 的执行计划：

EXPLAIN SELECT * FROM employees WHERE department_id = 1;

执行以上语句后，可以得到如下的执行计划：

                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using employees_department_id_idx on employees  (cost=0.43..1017.34 rows=9963 width=244)
   Index Cond: (department_id = 1)
(2 rows)

上述执行计划中，Index Scan 表示索引扫描，Index Cond 表示索引查询条件。

3. Bitmap Index Scan（位图索引扫描）

Bitmap Index Scan 是一种利用位图进行索引扫描的方式，适用于多个索引条件的查询。可以使用以下 SQL 语句获取 Bitmap Index Scan 的执行计划：

EXPLAIN SELECT * FROM employees WHERE age > 40 AND salary > 5000;

执行以上语句后，可以得到如下的执行计划：

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Bitmap Heap Scan on employees  (cost=1577.93..22515.68 rows=2948 width=244)
   Recheck Cond: ((age > 40) AND (salary > 5000))
   ->  BitmapAnd  (cost=1577.93..1577.93 rows=2948 width=0)
         ->  Bitmap Index Scan on employees_age_idx  (cost=0.00..596.46 rows=14977 width=0)
               Index Cond: (age > 40)
         ->  Bitmap Index Scan on employees_salary_idx  (cost=0.00..981.17 rows=30038 width=0)
               Index Cond: (salary > 5000)
(7 rows)

上述执行计划中，Bitmap Heap Scan 表示位图索引扫描，Recheck Cond 表示进行索引查询时重新检查的查询条件，BitmapAnd 表示使用两个位图取交集。

三、PostgreSQL 执行计划顺序

在 PostgreSQL 中，执行查询语句时，查询优化器会先生成一个初始的执行计划，然后逐步进行运算符重组、优化和代价估算等操作，最终生成完整的执行计划。可以使用以下 SQL 语句获取执行计划顺序：

EXPLAIN SELECT * FROM employees WHERE age > 40 AND salary > 5000;

执行以上语句后，可以得到如下的执行计划顺序：

                             QUERY PLAN                              
---------------------------------------------------------------------
 Bitmap Heap Scan on employees  (cost=1577.93..22515.68 rows=2948 width=244)
   Recheck Cond: ((age > 40) AND (salary > 5000))
   ->  BitmapAnd  (cost=1577.93..1577.93 rows=2948 width=0)
         ->  Bitmap Index Scan on employees_age_idx  (cost=0.00..596.46 rows=14977 width=0)
               Index Cond: (age > 40)
         ->  Bitmap Index Scan on employees_salary_idx  (cost=0.00..981.17 rows=30038 width=0)
               Index Cond: (salary > 5000)
(7 rows)

上述执行计划中，查询优化器首先进行了 Index Scan，在此基础上进行了 Bitmap Index Scan，最终进行了 Bitmap Heap Scan。

四、PostgreSQL 列转行

在 PostgreSQL 中，我们可以使用 UNNEST 函数将列转行，生成新的行。可以使用以下 SQL 语句进行列转行：

SELECT unnest(array_column) FROM employees;

上述语句中，将 employees 表中的 array_column 栏位的值进行列转行操作，生成新的行。

五、PostgreSQL 命令行选取

在 PostgreSQL 的命令行工具中，可以使用以下命令来获取查询语句的执行计划：

EXPLAIN (FORMAT JSON) SELECT * FROM employees WHERE age > 40;

上述命令中，FORMAT JSON 表示以 JSON 格式输出执行计划。

六、总结

通过本文对 PostgreSQL 的执行计划进行详细的讲解，读者可以更好地了解 SQL 查询语句的执行过程，并能够优化查询语句的性能。