Vertica Quick Tip: EXPLAIN Plan in JSON Format
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!