EXPLAIN
Use the EXPLAIN command to display a query execution plan or distribution for a query. An execution plan specifies how the table or tables that are referenced by the query are scanned (for example, by plain sequential scan).
If several tables are referenced, the execution plan specifies
which join algorithms are used to bring together the required tuples
from each input table. The execution plan includes an estimate of
the query execution cost, that is, the number of milliseconds required
to run the query.
- The start time before the first tuple can be returned.
- The total time to return all the tuples.
Syntax
Syntax for the EXPLAIN command:
EXPLAIN [VERBOSE] [DISTRIBUTION] [PLANTEXT|PLANGRAPH] <query>
Inputs
The EXPLAIN command
takes the following inputs:
Input | Description |
---|---|
VERBOSE | Display the full internal representation of the plan tree, rather than just a summary, and send the plan to the postmaster log file. This option is useful for debugging. |
<query> | The query for which the execution plan is to be displayed. |
DISTRIBUTION | Display the distribution plan. If you use the DISTRIBUTION subcommand, the EXPLAIN command prints out the distribution that the planner used on a SELECT statement in a CTAS command that did not specify DISTRIBUTE ON(). |
PLANTEXT | Displays the plan as text. |
PLANGRAPH | Displays the plan as HTML. |
Outputs
The EXPLAIN command
has the following outputs:
Output | Description |
---|---|
NOTICE: query plan: plan | The lead in message for an explicit query plan. |
NOTICE: result-set distribution | The lead in message for a distribution query. |
EXPLAIN | Message that indicates the end of the explicit query plan or distribution query. |
Privileges
This command requires no special privileges.
Usage
The following provides
sample usage.
- Display a query plan for a simple query on a table with a single
int4 column and 128 rows:
MYDB.SCH1(USER)=> EXPLAIN SELECT * FROM foo; NOTICE: QUERY PLAN: Seq Scan on foo (cost=0.00..2.28 rows=128 width=4) EXPLAIN
- Display a query plan for a join between the emp and grp tables:
MYDB.SCH1(USER)=> EXPLAIN SELECT emp.* FROM emp,grp WHERE emp.id=grp.grp_id; NOTICE: QUERY PLAN: Hash Join (cost=0.01..0.04 rows=10000 width=28) -> Seq Scan on emp (cost=0.00..0.01 rows=1000 width=28) -> Hash (cost=0.01..0.01 rows=1000 width=4) -> Seq Scan on grp (cost=0.00..0.01 rows=1000 width=4) EXPLAIN
- Display a query plan for TPHC Query 3:
MYDB.SCH1(USER)=> EXPLAIN SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' and l_shipdate > date '1995-03-15' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue desc, o_orderdate LIMIT 10;
NOTICE: QUERY PLAN: Limit (cost=315799480130.29..315799480130.29 rows=10 width=28) -> Sort (cost=315799480130.29..315799480130.29 rows=360899981912 width=28) -> Aggregate (cost=19476.76..112854066.90 rows=360899981912 width=28) -> Group (cost=19476.76..72822.55 rows=360899981912 width=28) -> Hash Join (cost=19476.76..43432.67 rows=31349208 width=28) -> Seq Scan on lineitem (cost=0.00..17842.17 rows=322475815 width=20) -> Hash (cost=19476.76..19476.76 rows=14582120 width=12) -> Hash Join (cost=1347.66..19476.76 rows=14582120 width=12) -> Seq Scan on orders (cost=0.00..3606.62 rows=72910603 width=16) -> Hash (cost=550.60..550.60 rows=3000000 width=4) -> Seq Scan on customer (cost=0.00..550.60 rows=3000000 width=4) EXPLAIN
- Display the distribution from a SELECT statement in a CTAS command:
dev(admin)=> EXPLAIN DISTRIBUTION SELECT COUNT(*),grp FROM emp GROUP BY grp; NOTICE: Result-set distribution (for "CREATE TABLE AS") Distributed on hash: "grp" EXPLAIN