View Explain Plan

[JeffSatler]

Is there a way to view explain plans for queries?

[JimKnicely]

Josh,

Quick answer is you can use the EXPLAIN command in vsql to show the query plan.

Example:

Code: Select all

dbadmin=> EXPLAIN SELECT COUNT(*) FROM athena_transaction a JOIN date_dim b ON b.date_key = a.post_date_key;

                                  QUERY PLAN

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

 EXPLAIN SELECT COUNT(*) FROM athena_transaction a JOIN date_dim b ON b.date_key = a.post_date_key;

 Access Path:
 +-GROUPBY NOTHING [Cost: 27K, Rows: 1] (PATH ID: 1)
 |  Aggregates: count(*)
 |  Execute on: Query Initiator
 | +---> JOIN HASH [Cost: 26K, Rows: 8M] (PATH ID: 2)
 | |      Join Cond: (b.date_key = a.post_date_key)
 | |      Execute on: Query Initiator
 | | +-- Outer -> STORAGE ACCESS for a [Cost: 20K, Rows: 8M] (PATH ID: 3)
 | | |      Projection: vertica_tst.athena_transaction_node0001
 | | |      Materialize: a.post_date_key
 | | |      Execute on: Query Initiator
 | | +-- Inner -> STORAGE ACCESS for b [Cost: 17, Rows: 2K] (PATH ID: 4)
 | | |      Projection: vertica_tst.date_dim_node0001
 | | |      Materialize: b.date_key
 | | |      Execute on: Query Initiator


 ------------------------------
 -----------------------------------------------
 PLAN: BASE QUERY PLAN (GraphViz Format)
 -----------------------------------------------
 digraph G {
 graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: EXPLAIN SELECT COUNT(*) FROM athena_transaction a JOIN date_dim b ON b.date_key = a.po
st_date_key;\n\nAll Nodes Vector: \n\n  node[0]=v_intersect_node0001 (initiator) Up\n  node[1]=v_intersect_node0002 (executor) Up\n  node[2
]=v_intersect_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out]
 0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"];
 1[label = "NewEENode \nOutBlk=[UncTuple]", color = "green", shape = "box"];
 2[label = "GroupByPipe: 0 keys\nAggs:\n  count(*)\nUnc: Integer(8)", color = "green", shape = "box"];
 3[label = "StorageUnionStep: athena_transaction_node0001\nUnc: Integer(8)", color = "purple", shape = "box"];
 4[label = "GroupByPipe: 0 keys\nAggs:\n  count(*)\nUnc: Integer(8)", color = "brown", shape = "box"];
 5[label = "ExprEval: \n  1\nUnc: Integer(8)", color = "brown", shape = "box"];
 6[label = "Join: Hash-Join: \n(vertica_tst.athena_transaction x vertica_tst.date_dim) using athena_transaction_node0001 and date_dim_nod
e0001 (PATH ID: 2)\n\nUnc: Integer(8)", color = "brown", shape = "box"];
 7[label = "ScanStep: athena_transaction_node0001\nSIP1(HashJoin): a.post_date_key\ncontext_id (not emitted)\npost_date_key\nUnc: Integer(8
)", color = "brown", shape = "box"];
 8[label = "StorageUnionStep: date_dim_node0001\nUnc: Integer(8)", color = "purple", shape = "box"];
 9[label = "ScanStep: date_dim_node0001\ndate_key\nUnc: Integer(8)", color = "brown", shape = "box"];
 1->0 [label = "V[0]",color = "black"];
 2->1 [label = "0",color = "blue"];
 3->2 [label = "0",color = "blue"];
 4->3 [label = "0",color = "blue"];
 5->4 [label = "0",color = "blue"];
 6->5 [label = "0",color = "blue"];
 7->6 [label = "0",color = "blue"];
 8->6 [label = "1",color = "blue"];
 9->8 [label = "0",color = "blue"];
 }
(49 rows)

Please feel free to ask any questions about the your specific query's plan if you'd like.

[Jbaskin]

What is the puepose of the "GraphViz Format" section of the explain plan results?

[JimKnicely]

HI,

Graphviz is open source graph visualization software. Graph visualization is a way of representing structural information as diagrams of abstract graphs and networks.

You can download the GVEdit software from http://www.graphviz.org/.

For instance, I installed the Windows version and cut and pasted Vertica's "GraphViz Format" output from the Explain command in the previous post into the GraphViz Format editor which produced the graph shown in the attachment...

[rajasekhart]

Hi,

Im unable to Understand the Explain Statement Result..

What is the acceptable cost?

What cost if it exceed lead to low performance?

Please tell me , how to understand the EXPLAIN statement result.

Thanks,
Raj.

[JimKnicely]

Hi Raj,

The cost is just an estimate of the resources (CPU, disk, memory, network, etc.) that the query plan requires to complete. Its a relative number that can only be compared to possible execution plans for the same query. There are usually multiple strategies that Vertica can use to get us the same results from a query. The one with the lowest "cost" is usually the one that Vertica chooses in the end.

Hope this helps.

[scutter]

I suggest sticking with the "Access Path" textual version of the explain plan, and skipping the digraph. In current versions of Vertica the textual version is pretty readable, and you can use the PATH ID numbers to correlate profiling data to portions of the plan.

You can get a quick idea from the plan on how well-suited your projections are for your query - do you get a merge join or a hash join, group by pipe or group by hash, etc.

Besides the costs, the explain plan also includes estimated row counts derived from the statistics. Those can be helpful for seeing how efficient the query is, or if you have unintentionally written an incorrect query when the row counts explode.

--Sharon