Database Reference
In-Depth Information
What is EXPLAIN?
The
EXPLAIN
command is utilized to exhibit the execution plan of the query. It shows
how tables will be scanned and the estimated cost of the query. This is the irst step
to improve the slow query, because it will show what will happen when a query is
executed.
EXPLAIN
has the ability to show how PostgreSQL will genuinely execute
the query internally. By understanding the detail exposed by
EXPLAIN
, you can
eficiently optimize the queries.
There are several parts to the
EXPLAIN
command's output, such as, scan, type,
estimated start up cost, estimated query total execution time, estimated number of
rows, and average width of row. Understanding all the information is the irst step
towards query optimization.
Let's use the
EXPLAIN
command on
record.history
as follows:
warehouse_db=# EXPLAIN SELECT * FROM record.history;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on history
(cost=0.00..1934344.66 rows=99976466 width=46)
Planning time: 474.581 ms
(2 rows)
The irst part of the information is the most important information in the
EXPLAIN
output, which is the scan type, displayed as
Seq Scan
in the preceding example.
PostgreSQL devises the
query plan
for the query, which consists of plan nodes.
The query plan is like a tree and bottom-level scans return the raw rows of the table.
There are three types of scans:
sequential scans
,
index scans
, and
bitmap scans
.
We will discuss the detail of each scan later in the chapter.
There are two costs involved in culling the scan:
start up cost
and
execution cost
.
Both costs are estimated costs, predicated on unit of disk page fetch. In the preceding
example, the start up cost is
0.00
. This start up cost is the estimated start up cost of
the query, which denotes the estimated time taken before output scan commences,
for example, sorting time for the sort node. The estimated execution time is the total
time taken to execute the query, which is
1934344.66
in our irst example. The next
information is the estimated number of rows, which is
99976466
in the preceding
example.
For nested plans, the
EXPLAIN
command shows the complete list of plan and
subplans. The upper plan cost is the sum of all child plans. Here is an example
of the nested plan:
warehouse_db=# EXPLAIN SELECT COUNT(*)
FROM record.history
WHERE warehouse_id
