오라클에서는 explain plan for 구문으로 예상 실행계획을 살펴봅니다.
PostgreSQL 에서는 어떻게 볼까요?
EXPLAIN 과 EXPLAIN ANALYZE 가 있습니다.
EXPLAIN 은 단순히 예상실행계획이며, SQL 을 수행하지 않습니다.
그러나, EXPLAIN ANALYZE 는 SQL 을 실제 수행합니다.
실 예를 살펴보시죠.
scott@cloud-00:5432 scottdb#SQL> explain select * from emp;
QUERY PLAN
-------------------------------------------------------
Seq Scan on emp (cost=0.00..16.30 rows=630 width=98)
(1 row)
Time: 0.372 ms
scott@cloud-00:5432 scottdb#SQL> explain analyze select * from emp;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on emp (cost=0.00..16.30 rows=630 width=98) (actual time=0.006..0.008 rows=14 loops=1)
Total runtime: 0.023 ms
(2 rows)
Time: 0.355 ms
scott@cloud-00:5432 scottdb#SQL> explain select * from emp limit 1;
QUERY PLAN
-------------------------------------------------------------
Limit (cost=0.00..0.03 rows=1 width=98)
-> Seq Scan on emp (cost=0.00..16.30 rows=630 width=98)
(2 rows)
Time: 0.360 ms
scott@cloud-00:5432 scottdb#SQL> explain analyze select * from emp limit 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.03 rows=1 width=98) (actual time=0.006..0.006 rows=1 loops=1)
-> Seq Scan on emp (cost=0.00..16.30 rows=630 width=98) (actual time=0.005..0.005 rows=1 loops=1)
Total runtime: 0.022 ms
(3 rows)
Time: 0.394 ms
scott@cloud-00:5432 scottdb#SQL>
아래와 같은 포맷의 출력도 가능합니다. (PostgreSQL version >= 9.0)
scott@pg-00:5432:scottdb]
SQL> explain (format yaml) select * from dept;
QUERY PLAN
---------------------------
- Plan: +
Node Type: "Seq Scan"+
Relation Name: "dept"+
Alias: "dept" +
Startup Cost: 0.00 +
Total Cost: 16.50 +
Plan Rows: 650 +
Plan Width: 94
(1 row)
Time: 6.923 ms
scott@pg-00:5432:scottdb]
SQL> explain (format xml) select * from dept;
QUERY PLAN
----------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain">+
<Query> +
<Plan> +
<Node-Type>Seq Scan</Node-Type> +
<Relation-Name>dept</Relation-Name> +
<Alias>dept</Alias> +
<Startup-Cost>0.00</Startup-Cost> +
<Total-Cost>16.50</Total-Cost> +
<Plan-Rows>650</Plan-Rows> +
<Plan-Width>94</Plan-Width> +
</Plan> +
</Query> +
</explain>
(1 row)
Time: 3.218 ms
scott@pg-00:5432:scottdb]
SQL> explain (format json) select * from dept;
QUERY PLAN
--------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan",+
"Relation Name": "dept",+
"Alias": "dept", +
"Startup Cost": 0.00, +
"Total Cost": 16.50, +
"Plan Rows": 650, +
"Plan Width": 94 +
} +
} +
]
(1 row)
Time: 1.185 ms
scott@pg-00:5432:scottdb]
