Viewing Previously Generated Explain Plans

Jim_Knicely · October 2018

The EXPLAIN command returns a formatted description of the Vertica optimizer's plan for executing the specified statement.

Example:

dbadmin=> SELECT current_trans_id(), current_statement();
current_trans_id  | current_statement
-------------------+-------------------
45035996273868061 |                 1
(1 row)

dbadmin=> EXPLAIN SELECT * FROM fact JOIN dim USING (c2);
                                                                                                                                                 QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------

EXPLAIN SELECT * FROM fact JOIN dim USING (c2);

Access Path:
+-JOIN MERGEJOIN(inputs presorted) [Cost: 25, Rows: 1] (PATH ID: 1) Outer (RESEGMENT)
|  Join Cond: (fact.c2 = dim.c2)
|  Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for fact [Cost: 12, Rows: 1] (PATH ID: 2)
| |      Projection: public.fact_b0
| |      Materialize: fact.c2, fact.c1
| |      Execute on: All Nodes
| +-- Inner -> STORAGE ACCESS for dim [Cost: 12, Rows: 1] (PATH ID: 3)
| |      Projection: public.dim_b0
| |      Materialize: dim.c2, dim.c3
| |      Execute on: All Nodes

When you run the EXPLAIN command, Vertica will store the generated plan in the DC_EXPLAIN_PLANS data collector table. This is super helpful if you need to know at a later time, perhaps after performing some tuning, what the explain plan looked like before!

In the example above, I captured the Transaction ID and Statement number prior to running the EXPLAIN command so that I could easily find my plan in DC_EXPLAIN_PLANS.

dbadmin=> SELECT path_line FROM dc_explain_plans WHERE transaction_id = 45035996273868061 AND statement_id = 2;
                                       path_line
---------------------------------------------------------------------------------------
+-JOIN MERGEJOIN(inputs presorted) [Cost: 25, Rows: 1] (PATH ID: 1) Outer (RESEGMENT)
|  Join Cond: (fact.c2 = dim.c2)
|  Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for fact [Cost: 12, Rows: 1] (PATH ID: 2)
| |      Projection: public.fact_b0
| |      Materialize: fact.c2, fact.c1
| |      Execute on: All Nodes
| +-- Inner -> STORAGE ACCESS for dim [Cost: 12, Rows: 1] (PATH ID: 3)
| |      Projection: public.dim_b0
| |      Materialize: dim.c2, dim.c3
| |      Execute on: All Nodes
(11 rows)

Helpful Links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/EXPLAIN.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/Glossary/DataCollector.htm

Have fun!