EXPLAIN

Return the text version of the query plan for either a specific SELECT query or a statement that runs a query.

Use the SMC to see a graphical version of the plan.

Syntax

EXPLAIN ( [ ANALYZE ] [ , VERBOSE ] [ , STABLE ] [ , TYPE ] [ , NODE_OUTPUT ] [ , DISTRIBUTION ] [ , PARTITIONS ]) query
ANALYZE
Run the query and return both the query plan and execution details. Runtime statistics are reported, including rows returned, execution time (in milliseconds), memory use, and cache hits. The read efficiency statistic is a percentage that describes how effective the execution engine was in not reading rows from the storage engine early in the plan. A value of 0% means that no rows were discarded in this way. A high value means that the system was very efficient and incurred less I/O. Sorting a table on a column that is frequently constrained may influence read efficiency.

If you do not specify this option, EXPLAIN returns the query plan without running the query.

VERBOSE
Return a more detailed plan output. You can use this option with or without ANALYZE and/or STABLE.
STABLE
Strip out details that might vary in the plan. Only the basic structure of the plan is rendered, regardless of actual statistics. You can use this option to isolate real differences between plans when the same query is run multiple times under different conditions. You can use this option in conjunction with VERBOSE and/or ANALYZE.
Note: LIMIT queries that do not have an ORDER BY clause are non-deterministic; therefore, using the STABLE option for these queries is not recommended.
TYPE
Return the data types for the columns in the output.
NODE_OUTPUT
Return the list of columns that are projected from each plan node.
DISTRIBUTION
Return distribution information for each plan node.
PARTITIONS
Return information about partitioned table columns when the plan uses partitioned execution.
query
Any valid Yellowbrick query. You can run the EXPLAIN command on all of the following query statements:
  • SELECT
  • SELECT INTO
  • CREATE TABLE AS (CTAS)
  • INSERT
  • UPDATE
  • DELETE
You cannot run the EXPLAIN command on a TRUNCATE statement.

Return the plan for a SELECT * query

premdb=# explain select * from awayteam;
                   QUERY PLAN                   
------------------------------------------------
 id   rows_planned   workers   node   
  1             50    single   SELECT    
  3             50    single   SCAN awayteam   
 Database: premdb
 Version: 5.1.0-20201008122214
 Hostname: yb007-mgr0.yellowbrick.io
 
(7 rows)

Run the same query and return both the plan and statistics

premdb=# explain (analyze) select * from awayteam;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Planning time: 0.140 ms
 id   rows_planned   rows_actual   skew   workers   node   
  1             50            50   0.00    single   SELECT    
  3             50            50   0.00    single   SCAN awayteam   
                                                      read_efficiency=0.0%, rows_from_column_store=50, rows_from_row_store=0
 50 rows returned
 Read: 40.00kiB, Distributed: 893.00B
 Database: premdb
 Version: 5.1.0-20201008122214
 Hostname: yb007-mgr0.yellowbrick.io
 query_id: 2859092
 Lookup cache hit: false
 Code cache hit: false
 Execution time: 665.396 ms, End time: 2020-11-02 12:10:48
(14 rows)

Run the same query and add the NODE_OUTPUT option

premdb=# explain (analyze, node_output) select * from awayteam;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Planning time: 0.185 ms
 id   rows_planned   rows_actual   skew   workers   node   
  1             50            50   0.00    single   SELECT    
                                                      (awayteam.atid, awayteam.name)
  3             50            50   0.00    single   SCAN awayteam   
                                                      (awayteam.atid, awayteam.name)
                                                      read_efficiency=0.0%, rows_from_column_store=50, rows_from_row_store=0
 50 rows returned
 Read: 40.00kiB, Distributed: 893.00B
 Database: premdb
 Version: 5.1.0-20201008122214
 Hostname: yb007-mgr0.yellowbrick.io
 query_id: 43353441
 Lookup cache hit: false
 Code cache hit: true
 Execution time: 27.893 ms, End time: 2020-12-14 12:47:16
(16 rows)

Run the same query and add the VERBOSE option

premdb=# explain (analyze, node_output, verbose) select * from awayteam;
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Planning time: 0.178 ms
 id   rows_planned   rows_actual   skew   mem_planned   mem_actual   workers   node   
  1             50            50   0.00      8.00Mi H     256.14ki    single   SELECT    
                                                                                 (awayteam.atid, awayteam.name)
                                                                                 distribute single
  3             50            50   0.00     57.00Mi H       1.00Mi    single   SCAN awayteam   
                                                                                 (awayteam.atid, awayteam.name)
                                                                                 distribute single
                                                                                 read_efficiency=0.0%, rows_from_column_store=50, rows_from_row_store=0
 50 rows returned
 Memory planned (fixed): 71.00MiB, Memory planned (variable): 128.00MiB, High confidence, Memory actual: 16.00MiB
 Read: 40.00kiB, Distributed: 893.00B
 Database: premdb
 Version: 5.1.0-20201008122214
 Hostname: yb007-mgr0.yellowbrick.io
 query_id: 43353867
 Lookup cache hit: false
 Code cache hit: true
 Execution time: 30.014 ms, End time: 2020-12-14 12:48:04
(19 rows)

Return the plan for a query that filters rows returned from a sorted table

Note the read efficiency statistic in this example. (The table newmatchstats is sorted on seasonid.)
premdb=# premdb=# explain (analyze) select * from newmatchstats where seasonid=10;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Planning time: 0.244 ms
 id   rows_planned   rows_actual   skew   workers   node   
  1          33820         33820   1.00       all   SELECT    
  3          33820         33820   1.00       all   SCAN newmatchstats   
                                                      (newmatchstats.seasonid::INT4 = $0) AND scan_constraints: (newmatchstats.seasonid = $0)
                                                      read_efficiency=99.6%, rows_from_column_store=33820, rows_from_row_store=0
 33820 rows returned
 Read: 296.00kiB, Distributed: 726.60kiB
 Database: premdb
 Version: 5.1.0-20201008122214
 Hostname: yb007-mgr0.yellowbrick.io
 query_id: 2864664
 Lookup cache hit: false
 Code cache hit: false
 Execution time: 735.490 ms, End time: 2020-11-02 12:14:02
(15 rows)

Use the STABLE option on the previous query

explain (analyze, stable) select * from newmatchstats where seasonid=10;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 rows_actual   workers   node   
       33820       all   SELECT    
                           distribute on (newmatchstats.seasonid)
       33820       all   SCAN newmatchstats   
                           distribute on (newmatchstats.seasonid)
                           (newmatchstats.seasonid::INT4 = $0) AND scan_constraints: (newmatchstats.seasonid = $0)
 33820 rows returned
(7 rows)

Add the TYPE option to the previous query

premdb=# explain (analyze, stable, type) select * from newmatchstats where seasonid=10;
                                                                               QUERY PLAN                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 rows_actual   workers   node   
       33820       all   SELECT    
                           (newmatchstats.seasonid INT2, newmatchstats.matchday DATE, newmatchstats.htid INT2, newmatchstats.atid INT2, newmatchstats.moment VARCHAR(5))
                           distribute on (newmatchstats.seasonid)
       33820       all   SCAN newmatchstats   
                           (newmatchstats.seasonid INT2, newmatchstats.matchday DATE, newmatchstats.htid INT2, newmatchstats.atid INT2, newmatchstats.moment VARCHAR(5))
                           distribute on (newmatchstats.seasonid)
                           (newmatchstats.seasonid::INT4 = $0) AND scan_constraints: (newmatchstats.seasonid = $0)
 33820 rows returned
(9 rows)

Use the DISTRIBUTION and PARTITIONS options for a query on a partitioned table

Note that the newmatchstats table in this example is a partitioned table:
premdb=# describe newmatchstats;
                                                                                                          
----------------------------------------------------------------------------------------------------------
                Table "public.newmatchstats"
 ----------+----------------------+-----------+-------------
   Column  |         Type         | Modifiers | Description 
 ----------+----------------------+-----------+-------------
  seasonid | smallint             | not null  | 
  matchday | date                 |           | 
  htid     | smallint             |           | 
  atid     | smallint             |           | 
  moment   | character varying(5) |           | 
 
 Distribution: Hash (seasonid)
 Partition Columns: 
     "matchday"   RANGE (BETWEEN date '1992-08-01' AND date '2017-05-31' EACH interval '1 year', IS NULL)
     "htid"       RANGE (BETWEEN 2 AND 51 EACH 1, IS NULL, OUTSIDE RANGE)
 
(15 rows)
premdb=# explain (distribution, partitions) select matchday, count(*) from newmatchstats 
where matchday like '1996-%' group by matchday order by 1;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 id   rows_planned   workers   node   
  1             11    single   SELECT    
                                 distribute single
  3             11       all   DISTRIBUTE SORT    
                                 distribute single
  4             11       all   SORT ON (newmatchstats.matchday ASC NULLS LAST)   
                                 distribute on (newmatchstats.matchday)
  5             11       all   PARTITION BY (newmatchstats.matchday)    
                                 distribute on (newmatchstats.matchday)
                                 partition by (newmatchstats.matchday) 
  7              1       all   GROUP BY (newmatchstats.matchday)    
                                 distribute on (newmatchstats.matchday)
  8              1       all   DISTRIBUTE ON HASH(newmatchstats.matchday)   
                                 distribute on (newmatchstats.matchday)
  9              1       all   GROUP BY PARTIAL (newmatchstats.matchday)    
                                 distribute on (newmatchstats.seasonid)
 11            206       all   SCAN newmatchstats   
                                 distribute on (newmatchstats.seasonid)
                                 partition by (newmatchstats.matchday) 
                                 newmatchstats.matchday::VARCHAR(64000)  LIKE '$1' 
 Database: premdb
 Version: 5.1.0-20201008122214
 Hostname: yb007-mgr0.yellowbrick.io
 
(24 rows)

SMC Graphical EXPLAIN

Tip: You may also want to use the visual EXPLAIN feature in the System Management Console (SMC). After logging in, go to Monitor, then Active Queries or Query Performance, double-click the query you want to explain, and click Plan. For example: