EXPLAIN

The EXPLAIN command outputs the query plan.

Syntax

EXPLAIN { SELECT... | INSERT... | UPDATE... }

Output

The EXPLAIN command is provided as a support feature and is not fully described here. For information on how to interpret the output, contact Technical Support.

• A compact human-readable representation of the query plan, laid out hierarchically. For example:

  Vertica QUERY PLAN DESCRIPTION:

  ------------------------------

  ID:1 Cost:2.7 Card:-1

  Projection: P0

  ID:2 Cost:0.1 Card:-1

  DS: Value Idx

  ProjCol:c_state, Table Oid.Attr#:25424.4

  Pred: Y Out: P

  ID:3 Cost:0.3 Card:-1

  DS: Position Filtered by ID:2

  ProjCol:c_gender, Table Oid.Attr#:25424.2

  Pred: Y Out: P

  ID:4 Cost:0.3 Card:-1

  DS: Position Filtered by ID:3

  ProjCol:c_name, Table Oid.Attr#:25424.3

  Pred: Y Out: P

  ID:5 Cost:1 Card:-1

  DS: Position Filtered by ID:4

  ProjCol:c_cid, Table Oid.Attr#:25424.1

  Pred: N Out: V

  ID:6 Cost:1 Card:-1

  DS: Position Filtered by ID:4

  ProjCol:c_state, Table Oid.Attr#:25424.4

  Pred: N Out: V

• A GraphViz format of the graph for display in a graphical format. Graphviz is a graph plotting utility with layout algorithms, etc. You can obtain a Fedora Core 4 RPM for GraphViz from:

  yum -y install graphviz

  A example of a GraphViz graph for a Vertica plan:

  digraph G {

  graph [rankdir=BT]

  0[label="Root"];

  1[label="ValExpNode"];

  2[label="VDS:DVIDX(P0.c_state)"];

  3[label="PDS(P0.c_gender)"];

  4[label="PDS(P0.c_name)"];

  5[label="Copy"];

  6[label="PDS(P0.c_cid)"];

  7[label="PDS(P0.c_state)"];

  1->0 [label="V"];

  1->0 [label="V"];

  2->3 [label="P"];

  3->4 [label="P"];

  4->5 [label="P"];

  5->6 [label="P"];

  5->7 [label="P"];

  6->1 [label="P+V"];

  7->1 [label="P+V"]; }

• To create a picture of the plan, copy the output above to a file, in this example /tmp/x.txt:
  1. dot -Tps /tmp/x.txt > /tmp/x.ps
  2. ggv x.ps [evince x.ps works if you don't have ggv]
  3. Alternative: dot -Tps | ghostview - and paste in the digraph.
  4. Alternative: generate jpg using -Tjpg.
  5. To scale an image for printing (8.5"x11" in this example):
  6. Portrait: dot -Tps -Gsize="7.5,10" -Gmargin="0.5" ...
  7. Landscape: dot -Tps -Gsize="10,7.5" -Gmargin="0.5" -Grotate="90" ...

  Example:

GraphViz Information

http://www.graphviz.org/Documentation.php