View an EXPLAIN Plan in JSON Format

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser

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!

Sign In or Register to comment.