The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
View an EXPLAIN Plan in JSON Format
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
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!
1