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 查詢語句的執行過程，並能夠優化查詢語句的性能。