Feedback

×

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

verticapy.performance.vertica.qprof.QueryProfiler.get_qplan_explain

QueryProfiler.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 0x7fd389fa2320>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa22f0>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa16c0>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa2350>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa23b0>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa2410>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa3130>
<IPython.core.display.HTML object>
<graphviz.sources.Source at 0x7fd389fa2f20>
<IPython.core.display.HTML object>
Out[1]: 
['------------------------------ \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 0x7fd389fa2320>,
 '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 0x7fd389fa22f0>,
 '----------------------------------------------- \nPLAN: BASE QUERY PLAN (GraphViz Format)\n-----------------------------------------------',
 <graphviz.sources.Source at 0x7fd389fa16c0>,
 '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 0x7fd389fa2350>,
 '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 0x7fd389fa23b0>,
 '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 0x7fd389fa2410>,
 '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 0x7fd389fa3130>,
 '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 0x7fd389fa2f20>,
 '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.