Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

verticapy.performance.vertica.qprof_interface.QueryProfilerInterface.get_qplan_explain

QueryProfilerInterface.get_qplan_explain(display_trees: bool = True) → list

Returns the tree’s query explain plan as a list of titles and trees.

Note

A EXPLAIN VERBOSE query is executed.

Parameters

display_trees: bool, optional

If set to True, prints the result before returning the final output.

Returns

list

List of the different titles and trees.

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 explain plan:

qprof.get_qplan_explain()
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895d9120>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895dad70>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895d90f0>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895d9c90>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895d99c0>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895d9a50>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895d8a60>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd3895da800>
<IPython.core.display.HTML object>
Out[6]: 
['------------------------------ \nQUERY PLAN DESCRIPTION: \n------------------------------\n\nOpt Vertica Options\n--------------------\nPLAN_OUTPUT_SUPER_VERBOSE\n\n\nEXPLAIN VERBOSE 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\n\n\nEnableUniquenessOptimization is on\nThe following JGNodes/Tables have been pruned due to uniqueness guarantee:\nSubquery for Node 0 (v_internal.dc_requests_issued):\n+-Node 1 de (v_internal.dc_errors)\n+-Node 2 ra (v_internal.dc_resource_acquisitions)\n+-Node 3 rs (v_internal.vs_sessions)\n\nAccess Path:\n+-SELECT  LIMIT 10 [Cost: 62056.000000, Rows: 10.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000 (NO STATISTICS)] [OutRowSz (B): 64032] (PATH ID: 0)\n|  Output Only: 10 tuples\n|  Execute on: Query Initiator\n|  LDISTRIB_UNSEGMENTED\n| +---> SORT [TOPK] [Cost: 62056.000000, Rows: 9999.000000 Disk(B): 0.000000 CPU(B): 8506381878.896563 Memory(B): 640255968.000000 Netwrk(B): 0.000000 Parallelism: 4.000000 (NO STATISTICS)] [OutRowSz (B): 64032] (PATH ID: 1)\n| |      Order: query_requests.request_duration DESC\n| |      Output Only: 10 tuples\n| |      Execute on: All Nodes\n| |      LDISTRIB_UNSEGMENTED\n| |      Execute on: All Nodes\n| |      LDISTRIB_SEGMENTED\n| | +---> JOIN HASH [LeftOuter] [Cost: 2321.000000, Rows: 9999.000000 Disk(B): 9828434.313137 CPU(B): 11438264.000000 Memory(B): 3039088.000000 Netwrk(B): 0.000000 Parallelism: 4.000000 (NO STATISTICS)] [OutRowSz (B): 64040] (PATH ID: 3)\n| | |      Join 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| | |      Materialize at Output: ri."time", ri.transaction_id, ri.statement_id, ri.request\n| | |      Execute on: All Nodes\n| | |      LDISTRIB_SEGMENTED\n| | | +-- Outer -> STORAGE ACCESS for ri [Cost: 649.000000, Rows: 9999.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000 (NO STATISTICS)] [OutRowSz (B): 280] (PATH ID: 4)\n| | | |      Column Cost Aspects: [ Disk(B): 9828925.734853 CPU(B): 2457600.000000 Memory(B): 2719720.003199 Netwrk(B): 0.000000 Parallelism: 4.000000 ]\n| | | |      Projection: v_internal.dc_requests_issued_p\n| | | |      Materialize: ri.node_name, ri.session_id, ri.request_id\n| | | |      Filter: (ri."time" > \'2024-10-31 10:42:24.287859-04\'::timestamptz)/* sel=0.999800 ndv= 500 */\n| | | |      Execute on: All Nodes\n| | | |      LDISTRIB_SEGMENTED\n| | | +-- Inner -> STORAGE ACCESS for dc_requests_completed [Cost: 965.000000, Rows: 9997.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000 (NO STATISTICS)] [OutRowSz (B): 288] (PATH ID: 5)\n| | | |      Column Cost Aspects: [ Disk(B): 14745600.000000 CPU(B): 0.000000 Memory(B): 5278944.134373 Netwrk(B): 0.000000 Parallelism: 4.000000 ]\n| | | |      Projection: v_internal.dc_requests_completed_p\n| | | |      Materialize: dc_requests_completed.request_id, dc_requests_completed."time", dc_requests_completed.node_name, dc_requests_completed.session_id\n| | | |      Filter: (dc_requests_completed.node_name IS NOT NULL)/* sel=0.999800 ndv= 500 */\n| | | |      Filter: (dc_requests_completed.session_id IS NOT NULL)/* sel=0.999800 ndv= 500 */\n| | | |      Filter: (dc_requests_completed.request_id IS NOT NULL)/* sel=0.999800 ndv= 500 */\n| | | |      Execute on: All Nodes\n| | | |      LDISTRIB_SEGMENTED\n\n\n------------------------------\n----------------------------------------------- \nPlan Extras\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895d9120>,
 'Table Name -> JoinGraph Node Mappings: \nv_internal.dc_requests_issued: Node 0(JoinGraph ID: 1)\nv_internal.dc_requests_issued: Node 0(JoinGraph ID: 2)\nv_internal.dc_errors: Node 1(JoinGraph ID: 2)\nv_internal.dc_resource_acquisitions: Node 2(JoinGraph ID: 2)\nv_internal.vs_sessions: Node 3(JoinGraph ID: 2)\nv_internal.dc_requests_completed: Node 4(JoinGraph ID: 2)\nv_internal.dc_errors: Node 0(JoinGraph ID: 3)\nv_internal.dc_resource_acquisitions: Node 0(JoinGraph ID: 4)\nv_internal.vs_sessions: Node 0(JoinGraph ID: 5)\n\nJoin Descriptions: \n(v_internal.dc_requests_issued x v_internal.vs_sessions(JoinGraph ID: 2)\n(v_internal.dc_requests_issued x v_internal.dc_resource_acquisitions(JoinGraph ID: 2)\n(v_internal.dc_requests_issued x v_internal.dc_errors(JoinGraph ID: 2)\n(v_internal.dc_requests_issued x v_internal.dc_requests_completed(JoinGraph ID: 2)\n\n\nProjections chosen for tables: \nv_internal.dc_requests_issued: dc_requests_issued_p\nv_internal.dc_requests_completed: dc_requests_completed_p\n\nSimplified Join Order:\n-----------------------',
 <graphviz.sources.Source at 0x7fd3895dad70>,
 '----------------------------------------------- \nPLAN: BASE QUERY PLAN (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895d90f0>,
 'Estimated resources for plan:\n-----------------------------\nScratch Memory MB: 9149\nFile Handles:      28\nWorker Threads:    6\nBlocking Threads:  5\nExternalizing Ops: 0\nUnbounded Mem Ops: 1\nMax Threads:       72\n\nExtra Memory Reservation MB 9077\n-----------------------------\n----------------------------------------------- \nPLAN:  ANNOTATED BY DISTRIBUTOR  (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895d9c90>,
 'Estimated resources for plan:\n-----------------------------\nScratch Memory MB: 9149\nFile Handles:      28\nWorker Threads:    6\nBlocking Threads:  5\nExternalizing Ops: 0\nUnbounded Mem Ops: 1\nMax Threads:       72\n\nExtra Memory Reservation MB 9077\n-----------------------------\n----------------------------------------------- \nPLAN:  INITIATOR NODE  (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895d99c0>,
 'Estimated resources for plan:\n-----------------------------\nScratch Memory MB: 9149\nFile Handles:      28\nWorker Threads:    6\nBlocking Threads:  5\nExternalizing Ops: 0\nUnbounded Mem Ops: 1\nMax Threads:       72\n\nExtra Memory Reservation MB 9077\n-----------------------------\n----------------------------------------------- \nPLAN:  EXECUTOR NODE v_mldb_node0002  (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895d9a50>,
 'Estimated resources for plan:\n-----------------------------\nScratch Memory MB: 9149\nFile Handles:      24\nWorker Threads:    6\nBlocking Threads:  1\nExternalizing Ops: 0\nUnbounded Mem Ops: 1\nMax Threads:       72\n\nExtra Memory Reservation MB 9099\n-----------------------------\n----------------------------------------------- \nPLAN:  EXECUTOR NODE v_mldb_node0003  (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895d8a60>,
 'Estimated resources for plan:\n-----------------------------\nScratch Memory MB: 9149\nFile Handles:      24\nWorker Threads:    6\nBlocking Threads:  1\nExternalizing Ops: 0\nUnbounded Mem Ops: 1\nMax Threads:       72\n\nExtra Memory Reservation MB 9099\n-----------------------------\n----------------------------------------------- \nPLAN:  EXECUTOR NODE v_mldb_node0004  (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd3895da800>,
 'Estimated resources for plan:\n-----------------------------\nScratch Memory MB: 9149\nFile Handles:      24\nWorker Threads:    6\nBlocking Threads:  1\nExternalizing Ops: 0\nUnbounded Mem Ops: 1\nMax Threads:       72\n\nExtra Memory Reservation MB 9099\n-----------------------------']

Note

For more details, please look at QueryProfiler.