Vertica Quick Tip: EXPLAIN Plan in JSON Format

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!