Loading...

verticapy.performance.vertica.qprof.QueryProfiler.get_qplan_tree#

QueryProfiler.get_qplan_tree(path_id: int | None = None, path_id_info: list | None = None, show_ancestors: bool = True, metric: Literal[None, 'cost', 'rows', 'exec_time_ms', 'est_rows', 'proc_rows', 'prod_rows', 'rle_prod_rows', 'clock_time_us', 'cstall_us', 'pstall_us', 'mem_res_mb', 'mem_all_mb'] | list | tuple = ['exec_time_ms', 'prod_rows'], pic_path: str | None = None, return_graphviz: bool = False, **tree_style) → Source | str#

Draws the Query Plan tree.

Parameters#

path_id: int, optional

A path ID used to filter the tree elements by starting from it.

path_id_info: list, optional

list of path_id used to display the different query information.

show_ancestors: bool, optional

If set to True the ancestors of path_id are also displayed.

metric: str | tuple | list, optional

The metric used to color the tree nodes. One of the following:

  • None (no specific color)

  • cost

  • rows

  • exec_time_ms

  • est_rows

  • proc_rows

  • prod_rows

  • rle_prod_rows

  • clock_time_us

  • cstall_us

  • pstall_us

  • mem_res_mb

  • mem_all_mb

It can also be a list or a tuple of two metrics.

pic_path: str, optional

Absolute path to save the image of the tree.

return_graphviz: bool, optional

If set to True, the str Graphviz tree is returned.

tree_style: dict, optional

dictionary used to customize the tree.

  • two_legend:

    If set to True and two metrics are used, two legends will be drawn. Default: True

  • color_low:

    Color used as the lower bound of the gradient. Default: ‘#00FF00’ (green)

  • color_high:

    Color used as the upper bound of the gradient. Default: ‘#FF0000’ (red)

  • fontcolor:

    Font color. Default (light-m): #000000 (black) Default (dark-m): #FFFFFF (white)

  • fontsize:

    Font size. Default: 22

  • fillcolor:

    Color used to fill the nodes in case no gradient is computed: metric=None. Default (light-m): #FFFFFF (white) Default (dark-m): #000000 (black)

  • edge_color:

    Edge color. Default (light-m): #000000 (black) Default (dark-m): #FFFFFF (white)

  • edge_style:

    Edge Style. Default: ‘solid’.

  • shape:

    Node shape. Default: ‘circle’.

  • width:

    Node width. Default: 0.6.

  • height:

    Node height. Default: 0.6.

  • info_color:

    Color of the information box. Default: #DFDFDF (lightgray)

  • info_fontcolor:

    Fontcolor of the information box. Default: #000000 (black)

  • info_rowsize:

    Maximum size of a line in the information box. Default: 30

  • info_fontsize:

    Information box font size. Default: 8

  • storage_access:

    Maximum number of chars of the storage access box. Default: 9

  • network_edge:

    If set to True the network edges will all have their own style: dotted for BROADCAST, dashed for RESEGMENT else solid.

  • display_operator:

    If set to True the PATH ID operator of each node will be displayed.

  • display_operator_edge:

    If set to True the operator edge of each node will be displayed.

  • display_proj:

    If set to True the projection of each STORAGE ACCESS PATH ID will be partially displayed.

  • display_etc:

    If set to True and path_is is not None the symbol “…” is used to represent the ancestors children when they have more than 1.

Returns#

graphviz.Source

graphviz object.

Examples#

First, let’s import the QueryProfiler object.

from verticapy.performance.vertica import QueryProfiler

Then we can create a query:

qprof = QueryProfiler(
    "select transaction_id, statement_id, request, request_duration"
    " from query_requests where start_timestamp > now() - interval'1 hour'"
    " order by request_duration desc limit 10;"
)

We can easily call the function to get the query plan Graphviz:

qprof.get_qplan_tree(return_graphviz = True)
Out[1]: 'digraph Tree {\n\tgraph [bgcolor="#FFFFFF00"]\n\tnode [shape=plaintext, fillcolor=white]\tedge [color="#666666", style=solid];\n\tlegend0 [shape=plaintext, fillcolor=white, label=<<table border="0" cellborder="1" cellspacing="0"><tr><td BGCOLOR="#666666"><FONT COLOR="#000000">Execution time in ms</FONT></td></tr><tr><td BGCOLOR="#00FF00"><FONT COLOR="#000000">0</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">5</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">43</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">290</FONT></td></tr><tr><td BGCOLOR="#FF0000"><FONT COLOR="#000000">2K</FONT></td></tr></table>>]\n\n\tlegend1 [shape=plaintext, fillcolor=white, label=<<table border="0" cellborder="1" cellspacing="0"><tr><td BGCOLOR="#666666"><FONT COLOR="#000000">Produced row count</FONT></td></tr><tr><td BGCOLOR="#00FF00"><FONT COLOR="#000000">20</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">343</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">6K</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">93K</FONT></td></tr><tr><td BGCOLOR="#FF0000"><FONT COLOR="#000000">2M</FONT></td></tr></table>>]\n\n\t0 [width=1.22, height=1.22, tooltip="SELECT  LIMIT 10 [Cost: 592K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)\n\nOutput Only: 10 tuples\n\nExecution time in ms: 0.048\nProduced row count: 20.0", fixedsize=true, URL="#path_id=0", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#00FF00" ><FONT COLOR="#00FF00">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">0</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">S</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#00FF00"><FONT COLOR="#00FF00">.</FONT></TD></TR></TABLE>>];\n\t1 [width=1.22, height=1.22, tooltip="SORT [TOPK] [Cost: 592K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)\n\nOrder: query_requests.request_duration DESC\n\nOutput Only: 10 tuples\n\nExecution time in ms: 189.244\nProduced row count: 71067.0", fixedsize=true, URL="#path_id=1", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#B04E00" ><FONT COLOR="#B04E00">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">1</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">S</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#B94500"><FONT COLOR="#B94500">.</FONT></TD></TR></TABLE>>];\n\t3 [width=1.22, height=1.22, tooltip="JOIN HASH [LeftOuter] [Cost: 9K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)\n\nJoin Cond: (ri.node_name = dc_requests_completed.node_name) AND (ri.session_id = dc_requests_completed.session_id) AND (ri.request_id = dc_requests_completed.request_id)\n\nMaterialize at Output: ri.\'time\', ri.transaction_id, ri.statement_id, ri.request\n\nExecution time in ms: 1903.26\nProduced row count: 142114.0", fixedsize=true, URL="#path_id=3", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FF0000" ><FONT COLOR="#FF0000">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">3</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">J</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#C83600"><FONT COLOR="#C83600">.</FONT></TD></TR></TABLE>>];\n\t4 [width=1.22, height=1.22, tooltip="Outer -> STORAGE ACCESS for ri [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)\n\nProjection: v_internal.dc_requests_issued_p\n\nMaterialize: ri.node_name, ri.session_id, ri.request_id\n\nFilter: (ri.\'time\' > \'2024-03-14 17:10:46.597023+00\'::timestamptz)\n\nExecution time in ms: 10.289\nProduced row count: 71057.0", fixedsize=true, URL="#path_id=4", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#50AE00" ><FONT COLOR="#50AE00">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">4</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">SA</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#B94500"><FONT COLOR="#B94500">.</FONT></TD></TR><TR><TD COLSPAN="4" WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00" ><FONT POINT-SIZE="9.0" COLOR="#666666">dc_reques..</FONT></TD></TR></TABLE>>];\n\t5 [width=1.22, height=1.22, tooltip="Inner -> STORAGE ACCESS for dc_requests_completed [Cost: 4K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)\n\nProjection: v_internal.dc_requests_completed_p\n\nMaterialize: dc_requests_completed.request_id, dc_requests_completed.\'time\', dc_requests_completed.node_name, dc_requests_completed.session_id\n\nFilter: (dc_requests_completed.node_name IS NOT NULL)\n\nFilter: (dc_requests_completed.session_id IS NOT NULL)\n\nFilter: (dc_requests_completed.request_id IS NOT NULL)\n\nExecution time in ms: 547.514\nProduced row count: 1526212.0", fixedsize=true, URL="#path_id=5", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#D42A00" ><FONT COLOR="#D42A00">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">5</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00"><FONT POINT-SIZE="22.0" COLOR="#666666">SA</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FF0000"><FONT COLOR="#FF0000">.</FONT></TD></TR><TR><TD COLSPAN="4" WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFF00" ><FONT POINT-SIZE="9.0" COLOR="#666666">dc_reques..</FONT></TD></TR></TABLE>>];\n\n\t0 -> 1 [dir=back, label="  ", style=solid, fontcolor="#666666"];\n\t1 -> 3 [dir=back, label="  ", style=solid, fontcolor="#666666"];\n\t3 -> 4 [dir=back, label=" O ", style=solid, fontcolor="#666666"];\n\t3 -> 5 [dir=back, label=" I-H ", style=solid, fontcolor="#666666"];\n\n}'

We can conveniently get the Query Plan tree:

qprof.get_qplan_tree()
../_images/performance_get_qplan_tree_1.png

Note

For more details, please look at QueryProfiler.