Viewing Previously Generated Explain Plans: Quick Tip

Jim Knicely authored this post. 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/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/EXPLAIN.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/Glossary/DataCollector.htm Have fun!