# EXPLAIN

EXPLAIN — 顯示執行計劃的內容

## 語法

``````EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }``````

## 說明

ANALYZE 選項讓語句實際執行，而不僅僅是計劃而已。然後將實際運行時的統計資訊加到顯示結果中，包括每個計劃節點中消耗的總耗用時間（以毫秒為單位）以及實際回傳的總資料列數。這對於了解規劃程序的估計是否接近現實非常有用。

### 重點

``````BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;``````

## 參數

`ANALYZE`

`VERBOSE`

`COSTS`

`BUFFERS`

`TIMING`

`SUMMARY`

`FORMAT`

`boolean`

`statement`

## 範例

``````EXPLAIN SELECT * FROM foo;

QUERY PLAN
---------------------------------------------------------
Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)``````

``````EXPLAIN (FORMAT JSON) SELECT * FROM foo;
QUERY PLAN
--------------------------------
[                             +
{                           +
"Plan": {                 +
"Node Type": "Seq Scan",+
"Relation Name": "foo", +
"Alias": "foo",         +
"Startup Cost": 0.00,   +
"Total Cost": 155.00,   +
"Plan Rows": 10000,     +
"Plan Width": 4         +
}                         +
}                           +
]
(1 row)``````

``````EXPLAIN SELECT * FROM foo WHERE i = 4;

QUERY PLAN
--------------------------------------------------------------
Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
Index Cond: (i = 4)
(2 rows)``````

``````EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
QUERY PLAN
-------------------------------
- Plan:                      +
Node Type: "Index Scan"  +
Scan Direction: "Forward"+
Index Name: "fi"         +
Relation Name: "foo"     +
Alias: "foo"             +
Startup Cost: 0.00       +
Total Cost: 5.98         +
Plan Rows: 1             +
Plan Width: 4            +
Index Cond: "(i = 4)"
(1 row)``````

XML 格式留給讀者練習。

``````EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;

QUERY PLAN
----------------------------
Index Scan using fi on foo
Index Cond: (i = 4)
(2 rows)``````

``````EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

QUERY PLAN
---------------------------------------------------------------------
Aggregate  (cost=23.93..23.93 rows=1 width=4)
->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
Index Cond: (i < 10)
(3 rows)``````

``````PREPARE query(int, int) AS SELECT sum(bar) FROM test
WHERE id > \$1 AND id < \$2
GROUP BY foo;

EXPLAIN ANALYZE EXECUTE query(100, 200);

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)
Group Key: foo
->  Index Scan using test_pkey on test  (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)
Index Cond: ((id > \$1) AND (id < \$2))
Planning time: 0.197 ms
Execution time: 0.225 ms
(6 rows)``````

## 相容性

SQL 標準中並沒有定義 EXPLAIN 語句。

ANALYZE

Last updated