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: None | str | tuple[str, str] | list[str] = ['exec_time_us', 'prod_rows'], pic_path: str | None = None, return_graphviz: bool = False, return_html: bool = True, idx: None | int | tuple = None, **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)

  • bytes_spilled

  • clock_time_us

  • cost

  • cstall_us

  • exec_time_us (default)

  • est_rows

  • mem_all_mb

  • mem_res_mb

  • proc_rows

  • prod_rows

  • pstall_us

  • rle_prod_rows

  • rows

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.

return_html: bool, optional

If set to True, the HTML tree representation is returned.

idx: int / tuple, optional

If not None, it represents the index of the transaction we want to visualize. It is similar to running set_position method before displaying the final tree.

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

  • display_legend:

    If set to True the legend is displayed. Default: True

  • display_annotations:

    If set to True the annotations are displayed. 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.

  • display_tooltip_descriptors:

    If set to True, the tooltip’s descriptors will be displayed. Default: True

  • display_tooltip_agg_metrics:

    If set to True, the tooltip’s aggregated metrics will be displayed. Default: True

  • display_tooltip_op_metrics:

    If set to True, the tooltip’s operator metrics will be displayed. Default: True

  • donot_display_op_metrics_i:

    dictionary of list, each key should represent an operator (ex: Scan, StorageUnion, NetworkSend…) and each value is a list of metrics to not display. Default: {}

  • temp_relation_access:

    list of the temporary tables to display. main represents the main relation plan. Ex: ['TREL8', 'main'] will only display the temporary relation 8 and the main relation. Default: []

  • display_projections_dml:

    If set to True and the operation is a DML all the target projections are displayed.

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="#FFFFFFDD"]\n\tnode [shape=plaintext, fillcolor=white]\tedge [color="#000000", style=solid];\n\tlegend_annotations [shape=plaintext, fillcolor=white, label=<<table border="0" cellborder="1" cellspacing="0"><tr><td BGCOLOR="#DFDFDF"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Path transition</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">🚫</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">NO STATISTICS</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">H</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">HASH</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">O</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">OUTER</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">I</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">INNER</FONT></td></tr></table>>]\n\n\n\tlegend0 [shape=plaintext, fillcolor=white, label=<<table border="0" cellborder="1" cellspacing="0"><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Execution time in µs</FONT></td></tr><tr><td BGCOLOR="#00FF00"><FONT COLOR="#000000">0</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">6</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">47</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">328</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="#DFDFDF"><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">342</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">6K</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">92K</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\nAggregated metrics:\n---------------------\n\n - Execution time in µs: 0.035\n - Estimated row count: 20\n - Processed row count: 0\n - Produced row count: 20\n - Produced RLE row count: 20\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 35\n - Reserved memory size in MB: 0.2\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n - Execution time in µs: 0.035\n - Produced row count: 20\n\nMetrics per operator\n---------------------\n\nExprEval:\n - Execution time in µs: 0.018\n - Estimated row count: 10\n - Processed row count: 0\n - Produced row count: 10\n - Produced RLE row count: 10\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 17\n - Reserved memory size in MB: 0.1\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nTopK:\n - Execution time in µs: 0.017\n - Estimated row count: 10\n - Processed row count: 0\n - Produced row count: 10\n - Produced RLE row count: 10\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 18\n - Reserved memory size in MB: 0.1\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nDescriptors\n------------\n\nOutput Only: 10 tuples", fixedsize=true, URL="#path_id=0", xlabel="🚫", 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="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">0</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">🔍</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\nAggregated metrics:\n---------------------\n\n - Execution time in µs: 243.762\n - Estimated row count: 9,999\n - Processed row count: 0\n - Produced row count: 95,378\n - Produced RLE row count: 95,378\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 220,897\n - Reserved memory size in MB: 0.9\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n - Execution time in µs: 243.762\n - Produced row count: 95,378\n\nMetrics per operator\n---------------------\n\nExprEval:\n - Execution time in µs: 148.686\n - Estimated row count: 0\n - Processed row count: 0\n - Produced row count: 95,368\n - Produced RLE row count: 95,368\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 149,094\n - Reserved memory size in MB: 0.1\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nTopK:\n - Execution time in µs: 95.076\n - Estimated row count: 9,999\n - Processed row count: 0\n - Produced row count: 10\n - Produced RLE row count: 10\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 71,803\n - Reserved memory size in MB: 0.8\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nDescriptors\n------------\n\nOrder: query_requests.request_duration DESC\n\nOutput Only: 10 tuples", fixedsize=true, URL="#path_id=1", xlabel="🚫", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#B54900" ><FONT COLOR="#B54900">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">1</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">🔀</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#C03E00"><FONT COLOR="#C03E00">.</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\nAggregated metrics:\n---------------------\n\n - Execution time in µs: 2,249.615\n - Estimated row count: 9,999\n - Processed row count: 0\n - Produced row count: 190,736\n - Produced RLE row count: 190,736\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 1,055,960\n - Reserved memory size in MB: 8.4\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n - Execution time in µs: 2,249.615\n - Produced row count: 190,736\n\nMetrics per operator\n---------------------\n\nJoin:\n - Execution time in µs: 889.985\n - Estimated row count: 9,999\n - Processed row count: 0\n - Produced row count: 95,368\n - Produced RLE row count: 95,368\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 1,055,960\n - Reserved memory size in MB: 0\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nStorageUnion:\n - Execution time in µs: 1,359.63\n - Estimated row count: 0\n - Processed row count: 0\n - Produced row count: 95,368\n - Produced RLE row count: 95,368\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 0\n - Reserved memory size in MB: 8.4\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nDescriptors\n------------\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", fixedsize=true, URL="#path_id=3", xlabel="🚫", 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="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">3</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">🔗</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#CF2F00"><FONT COLOR="#CF2F00">.</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\nAggregated metrics:\n---------------------\n\n - Execution time in µs: 11.628\n - Estimated row count: 9,999\n - Processed row count: 109,494\n - Produced row count: 95,368\n - Produced RLE row count: 95,368\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 12,046\n - Reserved memory size in MB: 0\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n - Execution time in µs: 11.628\n - Produced row count: 95,368\n\nMetrics per operator\n---------------------\n\nScan:\n - Execution time in µs: 11.628\n - Estimated row count: 9,999\n - Processed row count: 109,494\n - Produced row count: 95,368\n - Produced RLE row count: 95,368\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 12,046\n - Reserved memory size in MB: 0\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nDescriptors\n------------\n\nProjection: v_internal.dc_requests_issued_p\n\nMaterialize: ri.node_name, ri.session_id, ri.request_id\n\nFilter: (ri.\'time\' > \'2024-08-08 15:45:36.599122+00\'::timestamptz)", fixedsize=true, URL="#path_id=4", xlabel="🚫", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#53AB00" ><FONT COLOR="#53AB00">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">4</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">🗄️</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#C03E00"><FONT COLOR="#C03E00">.</FONT></TD></TR><TR><TD COLSPAN="4" WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD" ><FONT POINT-SIZE="9.0" COLOR="#000000">v_interna..</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\nAggregated metrics:\n---------------------\n\n - Execution time in µs: 547.269\n - Estimated row count: 9,997\n - Processed row count: 752,791\n - Produced row count: 1,505,582\n - Produced RLE row count: 1,505,582\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 140,804\n - Reserved memory size in MB: 4.7\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n - Execution time in µs: 547.269\n - Produced row count: 1,505,582\n\nMetrics per operator\n---------------------\n\nScan:\n - Execution time in µs: 131.469\n - Estimated row count: 9,997\n - Processed row count: 752,791\n - Produced row count: 752,791\n - Produced RLE row count: 752,791\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 140,804\n - Reserved memory size in MB: 0\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nStorageUnion:\n - Execution time in µs: 415.8\n - Estimated row count: 0\n - Processed row count: 0\n - Produced row count: 752,791\n - Produced RLE row count: 752,791\n - Network consumer stall time in µs: 0\n - Network producer stall time in µs: 0\n - Clock time in µs: 0\n - Reserved memory size in MB: 4.7\n - Allocated memory size in MB: 0\n - Number of bytes spilled: 0\n\nDescriptors\n------------\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)", fixedsize=true, URL="#path_id=5", xlabel="🚫", label=<<TABLE border="1" cellborder="1" cellspacing="0" cellpadding="0"><TR><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#D02E00" ><FONT COLOR="#D02E00">.</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">5</FONT></TD><TD WIDTH="18.0" HEIGHT="36.0" BGCOLOR="#FFFFFFDD"><FONT POINT-SIZE="22.0" COLOR="#000000">🗄️</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="#FFFFFFDD" ><FONT POINT-SIZE="9.0" COLOR="#000000">v_interna..</FONT></TD></TR></TABLE>>];\n\n\t0 -> 1 [dir=back, label="  ", style=solid, fontcolor="#000000"];\n\t1 -> 3 [dir=back, label="  ", style=solid, fontcolor="#000000"];\n\t3 -> 4 [dir=back, label=" O ", style=solid, fontcolor="#000000"];\n\t3 -> 5 [dir=back, label=" I-H ", style=solid, fontcolor="#000000"];\n\n}'

We can conveniently get the Query Plan tree:

qprof.get_qplan_tree()
Tree legend_annotations Path transition 🚫 NO STATISTICS H HASH O OUTER I INNER legend0 Execution time in µs 0 6 47 328 2K legend1 Produced row count 20 342 6K 92K 2M 0 . 0 🔍 . 🚫 1 . 1 🔀 . 🚫 0->1   3 . 3 🔗 . 🚫 1->3   4 . 4 🗄️ . v_interna.. 🚫 3->4 O 5 . 5 🗄️ . v_interna.. 🚫 3->5 I-H

Note

For more details, please look at QueryProfiler.