verticapy.performance.vertica.qprof.QueryProfiler.get_qplan_tree¶

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)
thread_count
bytes_spilled
clock_time_us
cost
cstall_us
exec_time_us (default)
est_rows
mem_all_b
mem_res_b
proc_rows
prod_rows
pstall_us
rle_prod_rows
rows
blocks_filtered_sip
blocks_analyzed_sip
container_rows_filtered_sip
container_rows_filtered_pred
container_rows_pruned_sip
container_rows_pruned_pred
container_rows_pruned_valindex
hash_tables_spilled_sort
join_inner_clock_time_us
join_inner_exec_time_us
join_outer_clock_time_us
join_outer_exec_time_us
network_wait_us
producer_stall_us
producer_wait_us
request_wait_us
response_wait_us
recv_net_time_us
recv_wait_us
rows_filtered_sip
rows_pruned_valindex
rows_processed_sip
total_rows_read_join_sort
total_rows_read_sort

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_tree_obj: bool, optional

If set to True, the Tree object is returned.

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.

orientation:
horizontal or vertical. Default: vertical
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)
color_null:
Color used to represent NULL values. Default: ‘#EFEFEF’ (light gray)
threshold_metric1:
Threshold used to disable some specific path_id based on the first metric. If the path_id value is under this value: A minimalist representation of the corresponding path_id will be used. Default: None
threshold_metric2:
Threshold used to disable some specific path_id based on the first metric. If the path_id value is under this value: A minimalist representation of the corresponding path_id will be used. Default: None
op_filter:
list of operators used to disable some specific path_id. If the path_id does not include all the operators of the op_filter list: A minimalist representation of the corresponding path_id will be used. Default: None
tooltip_filter:
str used to disable some specific path_id. If the path_id description does not include the input information: A minimalist representation of the corresponding path_id will be used. Default: None
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_tree:
If set to True the entire tree is displayed. Default: True
display_legend:
If set to True the legend is displayed. Default: True
display_legend1:
If set to True the first legend is displayed. Default: True
legend1_min:
Legend 1 minimum.
legend1_max:
Legend 1 maximum.
display_legend2:
If set to True the second legend is displayed. Default: True
legend2_min:
Legend 2 minimum.
legend2_max:
Legend 2 maximum.
display_path_transition:
If set to True the path transition legend is displayed. Default: True
display_annotations:
If set to True the annotations are displayed. Default: True
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
display_metrics_i:
list of metrics to display. Default: [‘exec_time_us’, ‘clock_time_us’,

‘mem_res_b’, ‘mem_all_b’, ‘proc_rows’, ‘prod_rows’, ‘thread_count’,]
display_projections_dml:
If set to True and the operation is a DML all the target projections are displayed.
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: []

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="#FFFFFFDD"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Path transitions</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">I</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">INNER</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="#FFFFFFDD"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Links</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">___</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">LOCAL</FONT></td></tr><tr><td BGCOLOR="#FFFFFFDD"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Information</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">🟢</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">QUERY INITIATOR</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">🌐</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">ALL NODES</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">56</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">658</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">8K</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">88K</FONT></td></tr><tr><td BGCOLOR="#FF0000"><FONT COLOR="#000000">1M</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">10</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">173</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">3K</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">44K</FONT></td></tr><tr><td BGCOLOR="#FF0000"><FONT COLOR="#000000">694K</FONT></td></tr></table>>]\n\n\t0 [width=1.22, height=1.22, tooltip="SELECT  LIMIT 10 [Cost: 62K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)\n * Execution time in µs: 56\n * Produced row count: 10\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 1\n - Execution time in µs: 56\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 59\n - Reserved memory size in B: 130,932\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nTopK:\n - Number of threads: 1\n - Execution time in µs: 56\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 59\n - Reserved memory size in B: 128,064\n - Allocated memory size in B: NULL\n\nExprEval:\n - Number of threads: 1\n - Execution time in µs: 40\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 39\n - Reserved memory size in B: 130,932\n - Allocated memory size in B: NULL\n\nDescriptors\n------------\n\nOutput Only: 10 tuples\n\nExecute on: Query Initiator", 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: 62K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)\n * Execution time in µs: 112,161\n * Produced row count: 31,455\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 4\n - Execution time in µs: 112,161\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 111,958\n - Reserved memory size in B: 21,741,048\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nNetworkRecv:\n - Number of threads: 4\n - Execution time in µs: 380\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: NULL\n - Reserved memory size in B: 21,741,048\n - Allocated memory size in B: NULL\n\nNetworkSend:\n - Number of threads: 1\n - Execution time in µs: 67\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: NULL\n - Reserved memory size in B: 8,789,832\n - Allocated memory size in B: NULL\n\nTopK:\n - Number of threads: 2\n - Execution time in µs: 68,538\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 47,911\n - Reserved memory size in B: 768,392\n - Allocated memory size in B: NULL\n\nExprEval:\n - Number of threads: 1\n - Execution time in µs: 112,161\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 111,958\n - Reserved memory size in B: 130,932\n - Allocated memory size in B: NULL\n\nDescriptors\n------------\n\nOrder: query_requests.request_duration DESC\n\nOutput Only: 10 tuples\n\nExecute on: All Nodes\n\nExecute on: All Nodes", 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="#C53900" ><FONT COLOR="#C53900">.</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="#B74700"><FONT COLOR="#B74700">.</FONT></TD></TR></TABLE>>];\n\t3 [width=1.22, height=1.22, tooltip="JOIN HASH [LeftOuter] [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)\n * Execution time in µs: 1,024,494\n * Produced row count: 31,455\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 2\n - Execution time in µs: 1,024,494\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 1,442,725\n - Reserved memory size in B: 26,462,208\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nStorageUnion:\n - Number of threads: 1\n - Execution time in µs: 307,978\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: NULL\n - Reserved memory size in B: 8,385,604\n - Allocated memory size in B: NULL\n\nJoin:\n - Number of threads: 2\n - Execution time in µs: 1,024,494\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 1,442,725\n - Reserved memory size in B: 26,462,208\n - Allocated memory size in B: NULL\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\n\nExecute on: All Nodes", 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="#B74700"><FONT COLOR="#B74700">.</FONT></TD></TR></TABLE>>];\n\t4 [width=1.22, height=1.22, tooltip="Outer -> STORAGE ACCESS for ri [Cost: 649, Rows: 10K (NO STATISTICS)] (PATH ID: 4)\n * Execution time in µs: 5,941\n * Produced row count: 31,455\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 1\n - Execution time in µs: 5,941\n - Processed row count: 35,014\n - Produced row count: 31,455\n - Clock time in µs: 6,331\n - Reserved memory size in B: 135,392\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nScan:\n - Number of threads: 1\n - Execution time in µs: 5,941\n - Processed row count: 35,014\n - Produced row count: 31,455\n - Clock time in µs: 6,331\n - Reserved memory size in B: 135,392\n - Allocated memory size in B: NULL\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-10-31 10:42:24.287859-04\'::timestamptz)\n\nExecute on: All Nodes", 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="#788600" ><FONT COLOR="#788600">.</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="#B74700"><FONT COLOR="#B74700">.</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: 965, Rows: 10K (NO STATISTICS)] (PATH ID: 5)\n * Execution time in µs: 377,946\n * Produced row count: 694,456\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 1\n - Execution time in µs: 377,946\n - Processed row count: 694,456\n - Produced row count: 694,456\n - Clock time in µs: 199,835\n - Reserved memory size in B: 4,737,028\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nStorageUnion:\n - Number of threads: 1\n - Execution time in µs: 377,946\n - Processed row count: NULL\n - Produced row count: 694,456\n - Clock time in µs: NULL\n - Reserved memory size in B: 4,737,028\n - Allocated memory size in B: NULL\n\nScan:\n - Number of threads: 1\n - Execution time in µs: 183,262\n - Processed row count: 694,456\n - Produced row count: 694,456\n - Clock time in µs: 199,835\n - Reserved memory size in B: 141,024\n - Allocated memory size in B: NULL\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)\n\nExecute on: All Nodes", 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="#E51900" ><FONT COLOR="#E51900">.</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()

../_images/performance_vertica_qprof_QueryProfiler_get_qplan_tree_2.png

Note

For more details, please look at QueryProfiler.