View an EXPLAIN Plan in JSON Format

[Deleted User] · March 2018

This tip was authored by Jim Knicely.

The EXPLAIN command returns the optimizer’s query plan for executing a specified query.

Example:

dbadmin=> EXPLAIN SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN '09-19-2017' AND '09-19-2018';

------------------------------
QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN '09-19-2017' AND '09-19-2018';

Access Path:
+-STORAGE ACCESS for big_date_table [Cost: 5M, Rows: 25M] (PATH ID: 1)
|  Projection: public.big_date_table_super
|  Materialize: big_date_table.the_date
|  Filter: ((big_date_table.the_date_as_date >= '2017-09-19'::date) AND (big_date_table.the_date_as_date <= '2018-09-19'::date))
------------------------------

You can have the EXPLAIN command produce the query plan in JSON format using the JSON option!

Example:

dbadmin=> EXPLAIN JSON SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN '09-19-2017' AND '09-19-2018';

------------------------------
QUERY PLAN DESCRIPTION:
------------------------------

Opt Vertica Options
--------------------
PLAN_OUTPUT_JSON

EXPLAIN JSON SELECT the_date FROM big_date_table WHERE the_date_as_date BETWEEN '09-19-2017' AND '09-19-2018';
-----------------------------------------------------------------
JSON format:

{
     "PATH_ID" : 0,
     "PATH_NAME" : "SELECT",
     "COST" : 4504109.000000,
     "ROWS" : 25125629.000000,
     "COST_STATUS" : "GOOD",
     "INPUT" : {
         "PATH_ID" : 1,
         "PATH_NAME" : "STORAGE ACCESS",
         "EXTRA" : "for big_date_table",
         "COST" : 4504109.000000,
         "ROWS" : 25125629.000000,
         "COST_STATUS" : "GOOD",
         "PROJECTION" : "public.big_date_table_super",
         "MATERIALIZE" : ["big_date_table.the_date"],
         "FILTER" : ["((big_date_table.the_date_as_date >= '2017-09-19'::date) AND (big_date_table.the_date_as_date <= '2018-09-19'::date))"]
     }
}
End JSON format
------------------------------
(36 rows)

Have Fun!