IBM PureData System for Analytics, Version 7.1

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.

If you limit the number of tuples to return with a limit clause, the planner interpolates between the endpoint costs to estimate which plan is really the most efficient.

Syntax

Syntax for the EXPLAIN command:

EXPLAIN [VERBOSE] [DISTRIBUTION] [PLANTEXT|PLANGRAPH] <query>

Inputs

The EXPLAIN command takes the following inputs:

Table 1. EXPLAIN Input
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:

Table 2. EXPLAIN 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