Vertica Quick Tip: EXPLAIN Plan in JSON Format

Posted March 22, 2018 by Soniya Shah, Information Developer

white cloud in vault type room representing cloud computing
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!