# EXPLAIN

## 名称

EXPLAIN -- 显示一个语句的执行规划

## 大纲

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

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

## 描述

ANALYZE选项导致查询被实际执行，而不仅仅是规划。显示中加入了实际的运行时间统计， 包括在每个规划节点内部花掉的总时间(以毫秒计)和它实际返回的行数。 这些数据对搜索该规划器的预期是否和现实相近很有帮助。

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

ANALYZE

VERBOSE

COSTS

BUFFERS

TIMING

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=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)
->  Index Scan using test_pkey on test  (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)
Index Cond: ((id > \$1) AND (id < \$2))
Total runtime: 0.851 ms
(4 rows)```

ANALYZE