Vertica Blog

Vertica Quick Tip: EXPLAIN Plan in JSON Format

Written by

This blog post 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!