Feedback

×

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

verticapy.performance.vertica.qprof.QueryProfiler

class verticapy.performance.vertica.qprof.QueryProfiler(transactions: None | str | list[int] | list[tuple[int, int]] = None, key_id: str | None = None, resource_pool: str | None = None, target_schema: None | str | dict = None, overwrite: bool = False, add_profile: bool = True, check_tables: bool = True, iterchecks: bool = False)

Base class to profile queries.

Important

This class is currently under development and remains in beta. Please note that the parameters may undergo changes in the future. We plan to introduce a stable version in VerticaPy 1.0.3 or later.

Important

Most of the classes are not available in Version 1.0.0. Please use Version 1.0.1 or higher. Alternatively, you can use the help function to explore the functionalities of your current documentation.

The QueryProfiler is a valuable tool for anyone seeking to comprehend the reasons behind a query’s lack of performance. It incorporates a set of functions inspired by the original QPROF project, while introducing an enhanced feature set. This includes the capability to generate graphics and dashboards, facilitating a comprehensive exploration of the data.

Moreover, it offers greater convenience by allowing interaction with an object that encompasses various methods and expanded possibilities. To initiate the process, all that’s required is a transaction_id and a statement_id, or simply a query to execute.

Parameters

transactions: str | tuple | list, optional

Six options are possible for this parameter:

• An integer:

  It will represent the transaction_id, the statement_id will be set to 1.

• A tuple:

  (transaction_id, statement_id).

• A list of tuples:

  (transaction_id, statement_id).

• A list of integers:

  the transaction_id; the statement_id will automatically be set to 1.

• A str:

  The query to execute.

• A list of str:

  The list of queries to execute. Each query will be execute iteratively.

  Warning

  It’s important to exercise caution; if the query is time-consuming, it will require a significant amount of time to execute before proceeding to the next steps.

Note

A combination of the three first options can also be used in a list.

key_id: int, optional

This parameter is utilized to load information from another target_schema. It is considered a good practice to save the queries you intend to profile.

resource_pool: str, optional

Specify the name of the resource pool to utilize when executing the query. Refer to the Vertica documentation for a comprehensive list of available options.

Note

This parameter is used only when request is defined.

target_schema: str | dict, optional

Name of the schemas to use to store all the Vertica monitor and internal meta-tables. It can be a single schema or a dictionary of schema used to map all the Vertica DC tables. If the tables do not exist, VerticaPy will try to create them automatically.

overwrite: bool, optional

If set to True overwrites the existing performance tables.

add_profile: bool, optional

If set to True and the request does not include a profile, this option adds the profile keywords at the beginning of the query before executing it.

Note

This parameter is used only when request is defined.

check_tables: bool, optional

If set to True all the transactions of the different Performance tables will be checked and a warning will be raised in case of incomplete data.

Warning

This parameter will aggregate on many tables using many parameters. It will make the process much more expensive.

iterchecks: bool, optional

If set to True, the checks are done iteratively instead of using a unique SQL query. Usually checks are faster when this parameter is set to False.

Note

This parameter is used only when check_tables is True.

Attributes

transactions: list

list of tuples: (transaction_id, statement_id). It includes all the transactions of the current schema.

requests: list

list of str: Transactions Queries.

request_labels: list

list of str: Queries Labels.

qdurations: list

list of int: Queries Durations (seconds).

key_id: int

Unique ID used to build up the different Performance tables savings.

request: str

Current Query.

qduration: int

Current Query Duration (seconds).

transaction_id: int

Current Transaction ID.

statement_id: int

Current Statement ID.

target_schema: dict

Name of the schema used to store all the Vertica monitor and internal meta-tables.

target_tables: dict

Name of the tables used to store all the Vertica monitor and internal meta-tables.

v_tables_dtypes: list

Datatypes of all the performance tables.

tables_dtypes: list

Datatypes of all the loaded performance tables.

overwrite: bool

If set to True overwrites the existing performance tables.

Examples

Initialization

First, let’s import the QueryProfiler object.

from verticapy.performance.vertica import QueryProfiler

There are multiple ways how we can use the QueryProfiler.

• From transaction_id and statement_id

• From SQL generated from verticapy functions

• Directly from SQL query

Transaction ID and Statement ID

In this example, we run a groupby command on the amazon dataset.

First, let us import the dataset:

from verticapy.datasets import load_amazon

amazon = load_amazon()

Then run the command:

query = amazon.groupby(
    columns = ["date"],
    expr = ["MONTH(date) AS month, AVG(number) AS avg_number"],
)

For every command that is run, a query is logged in the query_requests table. We can use this table to fetch the transaction_id and statement_id. In order to access this table we can use SQL Magic.

%load_ext verticapy.sql

%%sql
SELECT *
FROM query_requests
WHERE request LIKE '%avg_number%';

Hint

Above we use the WHERE command in order to filter only those results that match our query above. You can use these filters to sift through the list of queries.

Once we have the transaction_id and statement_id we can directly use it:

qprof = QueryProfiler((45035996273800581, 48))

Important

To save the different performance tables in a specific schema use target_schema='MYSCHEMA', ‘MYSCHEMA’ being the targetted schema. To overwrite the tables, use: overwrite=True. Finally, if you just need local temporary table, use the v_temp_schema schema.

Example:

qprof = QueryProfiler(
    (45035996273800581, 48),
    target_schema='v_temp_schema',
    overwrite=True,
)

Multiple Transactions ID and Statements ID

You can also construct an object based on multiple transactions and statement IDs by using a list of transactions and statements.

qprof = QueryProfiler(
    [(tr1, st2), (tr2, st2), (tr3, st3)],
    target_schema='MYSCHEMA',
    overwrite=True,
)

A key_id will be generated, which you can then use to reload the object.

qprof = QueryProfiler(
    key_id='MYKEY',
    target_schema='MYSCHEMA',
)

You can access all the transactions of a specific schema by utilizing the ‘transactions’ attribute.

qprof.transactions

SQL generated from VerticaPy functions

In this example, we can use the Titanic dataset:

from verticapy.datasets import load_titanic

titanic= load_titanic()

Let us run a simple command to get the average values of the two columns:

titanic["age","fare"].mean()

We can use the current_relation attribute to extract the generated SQL and this can be directly input to the Query Profiler:

qprof = QueryProfiler(
    "SELECT * FROM " + titanic["age","fare"].fillna().current_relation()
)

Directly From SQL Query

The last and most straight forward method is by directly inputting the SQL to the Query Profiler:

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;"
)

The query is then executed, and you can easily retrieve the statement and transaction IDs.

tid = qprof.transaction_id

sid = qprof.statement_id

print(f"tid={tid};sid={sid}")
tid=45035996274866665;sid=2

Or simply:

print(qprof.transactions)
[(45035996274866665, 2)]

To avoid recomputing a query, you can also directly use its statement ID and its transaction ID.

qprof = QueryProfiler((tid, sid))

Accessing the different Performance Tables

We can easily look at any Vertica Performance Tables easily:

qprof.get_table('dc_requests_issued')

	📅 time Timestamptz(35)	Abc node_name Varchar(128)	Abc session_id Varchar(128)	123 user_id Integer	Abc user_name Varchar(128)	123 transaction_id Integer	123 statement_id Integer	123 request_id Integer	Abc request_type Varchar(128)	Abc label Varchar(128)	Abc Varchar(64000)	Abc Varchar(64000)	123 query_start_epoch Integer	Abc Varchar(64000)	010 is_retry Boolean	123 digest Integer
1	2024-03-14 18:08:14.442377+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	5	66190	QUERY				83306		❌	806487162735703196
2	2024-03-14 18:08:14.465958+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	6	66191	QUERY	_version			83306		❌	8903476768692993780
3	2024-03-14 18:08:14.485541+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	7	66192	QUERY				83306		❌	806487162735703196
4	2024-03-14 18:08:14.513034+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	8	66193	QUERY	_version			83306		❌	8903476768692993780
5	2024-03-14 18:08:14.533344+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	9	66194	QUERY				83306		❌	806487162735703196
6	2024-03-14 18:08:14.558524+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	10	66195	QUERY	_version			83306		❌	8903476768692993780
7	2024-03-14 18:08:14.580659+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	11	66196	QUERY				83306		❌	806487162735703196
8	2024-03-14 18:08:14.607386+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	12	66197	QUERY	_version			83306		❌	8903476768692993780
9	2024-03-14 18:08:14.626347+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	13	66198	QUERY				83306		❌	806487162735703196
10	2024-03-14 18:08:14.655108+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	14	66199	QUERY	_version			83306		❌	8903476768692993780
11	2024-03-14 18:08:14.675759+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	15	66200	QUERY				83306		❌	806487162735703196
12	2024-03-14 18:08:14.706953+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	16	66201	QUERY	_version			83306		❌	8903476768692993780
13	2024-03-14 18:08:14.727490+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	17	66202	QUERY				83306		❌	806487162735703196
14	2024-03-14 18:08:14.755884+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	18	66203	QUERY	_version			83306		❌	8903476768692993780
15	2024-03-14 18:08:14.776627+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	19	66204	QUERY				83306		❌	806487162735703196
16	2024-03-14 18:08:14.802138+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	20	66205	QUERY	_version			83306		❌	8903476768692993780
17	2024-03-14 18:08:14.823421+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	21	66206	QUERY				83306		❌	806487162735703196
18	2024-03-14 18:08:14.848783+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	22	66207	QUERY	_version			83306		❌	8903476768692993780
19	2024-03-14 18:08:14.867163+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	23	66208	QUERY	learn.VerticaModel.__repr__			83306		❌	-8887346284077529213
20	2024-03-14 18:08:14.964337+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	24	66209	QUERY	verticapy_json			83306		❌	-2739771977645684132
21	2024-03-14 18:08:14.983991+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	25	66210	QUERY	verticapy_json			83306		❌	-2739771977645684132
22	2024-03-14 18:08:15.003144+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	26	66211	QUERY				83306		❌	1173375758661070897
23	2024-03-14 18:08:15.030401+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	27	66212	QUERY	get_data_types			83306		❌	-6803372202362044141
24	2024-03-14 18:08:15.074542+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	28	66213	QUERY	verticapy_json			83306		❌	-2739771977645684132
25	2024-03-14 18:08:15.094655+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	29	66214	QUERY				83306		❌	7011138784295650856
26	2024-03-14 18:08:15.119530+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	30	66215	QUERY	learn.tools._is_already_stored			83306		❌	5835180282835082858
27	2024-03-14 18:08:15.153132+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	31	66216	QUERY				83306		❌	7011138784295650856
28	2024-03-14 18:08:15.173929+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	32	66217	QUERY				83306		❌	3109024326454217980
29	2024-03-14 18:08:15.220744+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	33	66218	QUERY	_version			83306		❌	8903476768692993780
30	2024-03-14 18:08:15.242456+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	34	66219	QUERY	verticapy_json			83306		❌	-2739771977645684132
31	2024-03-14 18:08:15.266791+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	35	66220	QUERY				83306		❌	6749285220492401136
32	2024-03-14 18:08:15.369751+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	36	66221	QUERY	verticapy_json			83306		❌	-2739771977645684132
33	2024-03-14 18:08:15.391335+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	37	66222	QUERY	verticapy_json			83306		❌	-2739771977645684132
34	2024-03-14 18:08:15.413806+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	38	66223	QUERY				83306		❌	1173375758661070897
35	2024-03-14 18:08:15.434886+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	39	66224	QUERY	get_data_types			83306		❌	-6803372202362044141
36	2024-03-14 18:08:15.468739+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	40	66225	QUERY	verticapy_json			83306		❌	-2739771977645684132
37	2024-03-14 18:08:15.489438+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	41	66226	QUERY	_version			83306		❌	8903476768692993780
38	2024-03-14 18:08:15.512750+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	42	66227	QUERY	vDataframe.train_test_split			83306		❌	5551329239234976887
39	2024-03-14 18:08:15.535806+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	43	66228	QUERY	verticapy_json			83306		❌	-2739771977645684132
40	2024-03-14 18:08:15.557237+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	44	66229	QUERY				83306		❌	-8884034717893991551
41	2024-03-14 18:08:15.583585+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	45	66230	QUERY	verticapy_json			83306		❌	-2739771977645684132
42	2024-03-14 18:08:15.602941+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	46	66231	QUERY				83306		❌	-1011520196416268869
43	2024-03-14 18:08:15.633132+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	47	66232	QUERY	verticapy_json			83306		❌	-2739771977645684132
44	2024-03-14 18:08:15.652513+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	48	66233	QUERY				83306		❌	7011138784295650856
45	2024-03-14 18:08:15.676953+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	49	66234	QUERY	learn.tools._is_already_stored			83306		❌	5835180282835082858
46	2024-03-14 18:08:15.709652+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	50	66235	QUERY				83306		❌	7011138784295650856
47	2024-03-14 18:08:15.732660+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	51	66236	QUERY				83306		❌	-4293459230001215180
48	2024-03-14 18:08:15.776958+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	52	66237	QUERY	verticapy_json			83306		❌	-2739771977645684132
49	2024-03-14 18:08:15.798542+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	53	66238	QUERY				83306		❌	4892056614916329937
50	2024-03-14 18:08:15.820171+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	54	66239	QUERY				83306		❌	-6494634706701554542
51	2024-03-14 18:08:15.887920+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	55	66240	QUERY	verticapy_json			83306		❌	-2739771977645684132
52	2024-03-14 18:08:15.908410+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	56	66241	QUERY	verticapy_json			83306		❌	-2739771977645684132
53	2024-03-14 18:08:15.927625+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	57	66242	QUERY				83306		❌	1173375758661070897
54	2024-03-14 18:08:15.949842+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	58	66243	QUERY	get_data_types			83306		❌	-6803372202362044141
55	2024-03-14 18:08:15.985830+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	59	66244	QUERY	verticapy_json			83306		❌	-2739771977645684132
56	2024-03-14 18:08:16.006242+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	60	66245	QUERY	_version			83306		❌	8903476768692993780
57	2024-03-14 18:08:16.026298+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	61	66246	QUERY	vDataframe.train_test_split			83306		❌	5551329239234976887
58	2024-03-14 18:08:16.052125+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	62	66247	QUERY	verticapy_json			83306		❌	-2739771977645684132
59	2024-03-14 18:08:16.074106+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	63	66248	QUERY				83306		❌	-8884034717893991551
60	2024-03-14 18:08:16.096516+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	64	66249	QUERY	verticapy_json			83306		❌	-2739771977645684132
61	2024-03-14 18:08:16.116088+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	65	66250	QUERY				83306		❌	-1011520196416268869
62	2024-03-14 18:08:16.147190+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	66	66251	QUERY	verticapy_json			83306		❌	-2739771977645684132
63	2024-03-14 18:08:16.167523+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	67	66252	QUERY				83306		❌	7011138784295650856
64	2024-03-14 18:08:16.192197+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	68	66253	QUERY	learn.tools._is_already_stored			83306		❌	5835180282835082858
65	2024-03-14 18:08:16.224842+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	69	66254	QUERY				83306		❌	7011138784295650856
66	2024-03-14 18:08:16.247963+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	70	66255	QUERY				83306		❌	-4293459230001215180
67	2024-03-14 18:08:16.293294+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	71	66256	QUERY	verticapy_json			83306		❌	-2739771977645684132
68	2024-03-14 18:08:16.313925+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	72	66257	QUERY				83306		❌	247689392616137863
69	2024-03-14 18:08:16.330427+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	73	66258	QUERY	verticapy_json			83306		❌	-2739771977645684132
70	2024-03-14 18:08:16.352269+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	74	66259	QUERY				83306		❌	-7645496191171572853
71	2024-03-14 18:08:16.370868+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	75	66260	QUERY	verticapy_json			83306		❌	-2739771977645684132
72	2024-03-14 18:08:16.394503+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	76	66261	QUERY				83306		❌	2497958035241366679
73	2024-03-14 18:08:16.419120+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	77	66262	QUERY				83306		❌	2641900196224620659
74	2024-03-14 18:08:16.530099+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	78	66263	QUERY	verticapy_json			83306		❌	-2739771977645684132
75	2024-03-14 18:08:16.550386+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	79	66264	QUERY	verticapy_json			83306		❌	-2739771977645684132
76	2024-03-14 18:08:16.569549+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	80	66265	QUERY				83306		❌	1173375758661070897
77	2024-03-14 18:08:16.591070+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	81	66266	QUERY	get_data_types			83306		❌	-6803372202362044141
78	2024-03-14 18:08:16.621965+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	82	66267	QUERY	verticapy_json			83306		❌	-2739771977645684132
79	2024-03-14 18:08:16.643345+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	83	66268	QUERY	_version			83306		❌	8903476768692993780
80	2024-03-14 18:08:16.663228+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	84	66269	QUERY	vDataframe.train_test_split			83306		❌	5551329239234976887
81	2024-03-14 18:08:16.686826+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	85	66270	QUERY	verticapy_json			83306		❌	-2739771977645684132
82	2024-03-14 18:08:16.706219+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	86	66271	QUERY				83306		❌	-8884034717893991551
83	2024-03-14 18:08:16.730863+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	87	66272	QUERY	verticapy_json			83306		❌	-2739771977645684132
84	2024-03-14 18:08:16.749452+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	88	66273	QUERY				83306		❌	-1011520196416268869
85	2024-03-14 18:08:16.787786+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	89	66274	QUERY	verticapy_json			83306		❌	-2739771977645684132
86	2024-03-14 18:08:16.808948+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	90	66275	QUERY				83306		❌	7011138784295650856
87	2024-03-14 18:08:16.833553+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	91	66276	QUERY	learn.tools._is_already_stored			83306		❌	5835180282835082858
88	2024-03-14 18:08:16.865485+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	92	66277	QUERY				83306		❌	7011138784295650856
89	2024-03-14 18:08:16.888262+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	93	66278	QUERY				83306		❌	-4293459230001215180
90	2024-03-14 18:08:16.936651+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	94	66279	QUERY	verticapy_json			83306		❌	-2739771977645684132
91	2024-03-14 18:08:16.956804+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	95	66280	QUERY	_version			83306		❌	8903476768692993780
92	2024-03-14 18:08:16.978326+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	96	66281	QUERY	verticapy_json			83306		❌	-2739771977645684132
93	2024-03-14 18:08:17.002317+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	97	66282	QUERY				83306		❌	-125179491804461608
94	2024-03-14 18:08:17.033710+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	98	66283	QUERY	verticapy_json			83306		❌	-2739771977645684132
95	2024-03-14 18:08:17.056041+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	99	66284	QUERY	verticapy_json			83306		❌	-2739771977645684132
96	2024-03-14 18:08:17.078710+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	100	66285	QUERY				83306		❌	-1991255985308434521
97	2024-03-14 18:08:17.177648+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	101	66286	QUERY	verticapy_json			83306		❌	-2739771977645684132
98	2024-03-14 18:08:17.198292+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	102	66287	QUERY	verticapy_json			83306		❌	-2739771977645684132
99	2024-03-14 18:08:17.219421+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	103	66288	QUERY				83306		❌	3791173225426651648
100	2024-03-14 18:08:17.273144+00:00	v_demo_node0001	v_demo_node0001-144:0xbe	45035996273704962	dbadmin	45035996274860006	104	66289	QUERY	verticapy_json			83306		❌	-2739771977645684132

Rows: 1-100 | Columns: 16

Note

You can use the method without parameters to obtain a list of all available tables.

qprof.get_table()
Out[8]: 
['dc_requests_issued',
 'dc_query_executions',
 'dc_explain_plans',
 'execution_engine_profiles',
 'query_events',
 'query_plan_profiles',
 'query_profiles',
 'resource_pool_status',
 'host_resources',
 'dc_plan_activities',
 'dc_lock_attempts',
 'dc_plan_resources',
 'configuration_parameters',
 'projection_storage',
 'projection_usage',
 'query_consumption',
 'resource_acquisitions',
 'storage_containers',
 'projections',
 'projection_columns',
 'resource_pools']

We can also look at all the object queries information:

qprof.get_queries()

	010 is_current Boolean	123 transaction_id Integer	123 statement_id Integer	Abc request_label Varchar	Abc Varchar(179)	123 qduration Numeric(9)
0	✅	45035996274866665	2			7.277719

Rows: 1-1 | Columns: 7

Executing a QPROF step

Numerous QPROF steps are accessible by directly using the corresponding methods. For instance, step 0 corresponds to the Vertica version, which can be executed using the associated method get_version.

qprof.get_version()
Out[9]: (23, 4, 0, 0)

Note

To explore all available methods, please refer to the ‘Methods’ section. For additional information, you can also utilize the help function.

It is possible to access the same step by using the step method.

qprof.step(idx = 0)
Out[10]: (23, 4, 0, 0)

Note

By changing the idx value above, you can check out all the steps of the QueryProfiler.

SQL Query

SQL query can be conveniently reproduced in a color formatted version:

qprof.get_request()

Query Performance Details

Query Execution Time

To get the execution time of the entire query:

qprof.get_qduration(unit="s")
Out[11]: 7.277719

Note

You can change the unit to “m” to get the result in minutes.

Query Execution Time Plots

To get the time breakdown of all the steps in a graphical output, we can call the get_qsteps attribute.

qprof.get_qsteps(kind="bar")

Loading....

Note

The same plot can also be plotted using a bar plot by setting kind='bar'.

Note

For charts, it is possible to pass many parameters to customize them. Example: You can use categoryorder to sort the chart or width and height to manage the size.

Query Plan

To get the entire query plan:

qprof.get_qplan()
+-SELECT  LIMIT 10 [Cost: 592K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)
|  Output Only: 10 tuples
| +---> SORT [TOPK] [Cost: 592K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
| |      Order: query_requests.request_duration DESC
| |      Output Only: 10 tuples
| | +---> JOIN HASH [LeftOuter] [Cost: 9K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| | |      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)
| | |      Materialize at Output: ri."time", ri.transaction_id, ri.statement_id, ri.request
| | | +-- Outer -> STORAGE ACCESS for ri [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | | |      Projection: v_internal.dc_requests_issued_p
| | | |      Materialize: ri.node_name, ri.session_id, ri.request_id
| | | |      Filter: (ri."time" > '2024-03-14 17:10:46.597023+00'::timestamptz)
| | | +-- Inner -> STORAGE ACCESS for dc_requests_completed [Cost: 4K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
| | | |      Projection: v_internal.dc_requests_completed_p
| | | |      Materialize: dc_requests_completed.request_id, dc_requests_completed."time", dc_requests_completed.node_name, dc_requests_completed.session_id
| | | |      Filter: (dc_requests_completed.node_name IS NOT NULL)
| | | |      Filter: (dc_requests_completed.session_id IS NOT NULL)
| | | |      Filter: (dc_requests_completed.request_id IS NOT NULL)
Out[12]: '+-SELECT  LIMIT 10 [Cost: 592K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)\n|  Output Only: 10 tuples\n| +---> SORT [TOPK] [Cost: 592K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)\n| |      Order: query_requests.request_duration DESC\n| |      Output Only: 10 tuples\n| | +---> JOIN HASH [LeftOuter] [Cost: 9K, Rows: 10K (NO STATISTICS)] (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| | | +-- Outer -> STORAGE ACCESS for ri [Cost: 3K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)\n| | | |      Projection: v_internal.dc_requests_issued_p\n| | | |      Materialize: ri.node_name, ri.session_id, ri.request_id\n| | | |      Filter: (ri."time" > \'2024-03-14 17:10:46.597023+00\'::timestamptz)\n| | | +-- Inner -> STORAGE ACCESS for dc_requests_completed [Cost: 4K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)\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)\n| | | |      Filter: (dc_requests_completed.session_id IS NOT NULL)\n| | | |      Filter: (dc_requests_completed.request_id IS NOT NULL)'

Query Plan Tree

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

qprof.get_qplan_tree(return_graphviz = True)
Out[13]: '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()

We can easily customize the tree:

qprof.get_qplan_tree(
    metric='cost',
    shape='square',
    color_low='#0000FF',
    color_high='#FFC0CB',
)

We can look at a specific path ID, and look at some specific paths information:

qprof.get_qplan_tree(
    path_id=1,
    path_id_info=[1, 3],
    metric='cost',
    shape='square',
    color_low='#0000FF',
    color_high='#FFC0CB',
)

Query Plan Profile

To visualize the time consumption of query profile plan:

qprof.get_qplan_profile(kind = "pie")

Note

The same plot can also be plotted using a bar plot by switching the kind to “bar”.

Query Events

We can easily look at the query events:

qprof.get_query_events()

	📅 event_timestamp Timestamptz(35)	Abc node_name Varchar(128)	Abc event_category Varchar(12)	Abc event_type Varchar(64000)	Abc event_description Varchar(64000)	Abc operator_name Varchar(128)	123 path_id Integer	Abc event_details Varchar(64000)	Abc suggested_action Varchar(64000)

Rows: 0 | Columns: 9

CPU Time by Node and Path ID

Another very important metric could be the CPU time spent by each node. This can be visualized by:

qprof.get_cpu_time(kind="bar")

In order to get the results in a tabular form, just switch the show option to False.

qprof.get_cpu_time(show=False)

	Abc node_name Varchar(128)	Abc path_id Varchar(20)	123 counter_value Integer
1	v_demo_node0001	-1	59
2	v_demo_node0001	-1	93
3	v_demo_node0001	0	20
4	v_demo_node0001	0	28
5	v_demo_node0001	1	73041
6	v_demo_node0001	1	116203
7	v_demo_node0001	3	1075972
8	v_demo_node0001	2	335353
9	v_demo_node0001	3	827288
10	v_demo_node0001	4	10289
11	v_demo_node0001	5	413575
12	v_demo_node0001	5	133939

Rows: 1-12 | Columns: 3

Query Execution Report

To obtain a comprehensive performance report, including specific details such as which node executed each operation and the corresponding timing information, utilize the following syntax:

qprof.get_qexecution_report()

	Abc node_name Varchar(128)	Abc operator_name Varchar(128)	123 path_id Integer	123 exec_time_ms Numeric(38)	123 est_rows Integer	123 proc_rows Integer	123 prod_rows Integer	123 rle_prod_rows Integer	123 cstall_us Integer	123 pstall_us Integer	123 clock_time_us Integer	123 mem_res_mb Numeric(38)	123 mem_all_mb Numeric(38)
1	v_demo_node0001	TopK	1	73.041	9999	[null]	10	10	[null]	[null]	55110	0.8	[null]
2	v_demo_node0001	Scan	4	10.289	9999	77198	71057	71057	[null]	[null]	10746	0.0	[null]
3	v_demo_node0001	Join	3	827.288	9999	[null]	71057	71057	[null]	[null]	986868	[null]	[null]
4	v_demo_node0001	ExprEval	2	335.353	9999	[null]	71057	71057	[null]	[null]	336539	2.2	[null]
5	v_demo_node0001	Scan	5	133.939	9997	763106	763106	763106	[null]	[null]	143072	0.0	[null]
6	v_demo_node0001	TopK	0	0.02	10	[null]	10	10	[null]	[null]	21	0.1	[null]
7	v_demo_node0001	ExprEval	0	0.028	10	[null]	10	10	[null]	[null]	29	0.1	[null]
8	v_demo_node0001	ExprEval	1	116.203	[null]	[null]	71057	71057	[null]	[null]	116398	0.1	[null]
9	v_demo_node0001	StorageUnion	5	413.575	[null]	[null]	763106	763106	[null]	[null]	[null]	4.7	[null]
10	v_demo_node0001	NewEENode	-1	0.093	[null]	[null]	10	10	[null]	[null]	93	0.4	[null]
11	v_demo_node0001	StorageUnion	3	1075.972	[null]	[null]	71057	71057	[null]	[null]	[null]	8.4	[null]
12	v_demo_node0001	Root	-1	0.059	[null]	[null]	10	[null]	[null]	[null]	59	[null]	[null]

Rows: 1-12 | Columns: 13

Node/Cluster Information

Nodes

To get node-wise performance information, get_qexecution can be used:

qprof.get_qexecution()

Note

To use one specific node:

qprof.get_qexecution(
    node_name = "v_vdash_node0003",
    metric = "exec_time_ms",
    kind = "pie",
)

To use multiple nodes:

qprof.get_qexecution(
    node_name = [
        "v_vdash_node0001",
        "v_vdash_node0003",
    ],
    metric = "exec_time_ms",
    kind = "pie",
)

The node name is different for different configurations. You can search for the node names in the full report.

Cluster

To get cluster configuration details, we can use:

qprof.get_cluster_config()

	Abc host_name Varchar(128)	123 open_files_limit Integer	123 threads_limit Integer	123 core_file_limit_max_size_bytes Integer	123 processor_count Integer	123 processor_core_count Integer	Abc Varchar(8192)	123 opened_file_count Integer	123 opened_socket_count Integer	123 opened_nonfile_nonsocket_count Integer	123 total_memory_bytes Integer	123 total_memory_free_bytes Integer	123 total_buffer_memory_bytes Integer	123 total_memory_cache_bytes Integer	123 total_swap_memory_bytes Integer	123 total_swap_memory_free_bytes Integer	123 disk_space_free_mb Integer	123 disk_space_used_mb Integer	123 disk_space_total_mb Integer	123 system_open_files Integer	123 system_max_files Integer
1	127.0.0.1	1048576	255424	0	1	20		6	7	10	33485697024	14690299904	717975552	7771045888	8589934592	8589934592	956339	74679	1031018	2960	9223372036854775807

Rows: 1-1 | Columns: 21

The Cluster Report can also be conveniently extracted:

qprof.get_rp_status()

	Abc node_name Varchar(128)	123 pool_oid Integer	Abc pool_name Varchar(128)	010 is_internal Boolean	123 memory_size_kb Integer	123 memory_size_actual_kb Integer	123 memory_inuse_kb Integer	123 general_memory_borrowed_kb Integer	123 queueing_threshold_kb Integer	123 max_memory_size_kb Integer	123 max_query_memory_size_kb Integer	123 running_query_count Integer	123 planned_concurrency Integer	123 max_concurrency Integer	010 is_standalone Boolean	📅 queue_timeout Interval day to second	123 queue_timeout_in_seconds Integer	Abc execution_parallelism Varchar(128)	123 priority Integer	Abc runtime_priority Varchar(128)	123 runtime_priority_threshold Integer	123 runtimecap_in_seconds Integer	Abc single_initiator Varchar(128)	123 query_budget_kb Integer	Abc cpu_affinity_set Varchar(256)	Abc cpu_affinity_mask Varchar(1024)	Abc cpu_affinity_mode Varchar(128)
1	v_demo_node0001	45035996273705004	general	✅	24731401	24731401	0	0	23494830	24731401	[null]	0	13	[null]	✅	relativedelta(minutes=+5)	300	AUTO	0	MEDIUM	2	[null]	false	1807294		fffff	ANY
2	v_demo_node0001	45035996273705006	sysquery	✅	1048576	1048576	40348	0	24631420	25927810	[null]	1	13	[null]	❌	relativedelta(minutes=+5)	300	AUTO	110	HIGH	0	[null]	false	80659		fffff	ANY
3	v_demo_node0001	45035996273705008	tm	✅	3145728	3145728	0	0	26623714	28024962	[null]	0	7	7	❌	relativedelta(minutes=+5)	300	AUTO	105	MEDIUM	60	[null]	true	449389		fffff	ANY
4	v_demo_node0001	45035996273705010	refresh	✅	0	0	0	0	23635272	24879234	[null]	0	4	[null]	❌	relativedelta(minutes=+5)	300	AUTO	-10	MEDIUM	60	[null]	true	5873707		fffff	ANY
5	v_demo_node0001	45035996273705012	recovery	✅	0	0	0	0	23635272	24879234	[null]	0	22	11	❌	relativedelta(minutes=+5)	300	AUTO	107	MEDIUM	60	[null]	true	1067946		fffff	ANY
6	v_demo_node0001	45035996273705014	dbd	✅	0	0	0	0	23635272	24879234	[null]	0	4	[null]	❌	relativedelta()	0	AUTO	0	MEDIUM	0	[null]	true	5873707		fffff	ANY
7	v_demo_node0001	45035996273705092	jvm	✅	0	0	0	0	1992294	2097152	[null]	0	13	[null]	❌	relativedelta(minutes=+5)	300	AUTO	0	MEDIUM	2	[null]	false	153253		fffff	ANY
8	v_demo_node0001	45035996273705102	blobdata	✅	0	0	0	0	2761985	2907353	[null]	0	2	0	❌	relativedelta()	0	AUTO	0	HIGH	0	[null]	false	[null]		fffff	ANY
9	v_demo_node0001	45035996273705104	metadata	✅	147833	147833	0	0	23635272	24879234	[null]	0	1	0	❌	relativedelta()	0	AUTO	108	HIGH	0	[null]	false	[null]		fffff	ANY

Rows: 1-9 | Columns: 27

Important

Each method may have multiple parameters and options. It is essential to refer to the documentation of each method to understand its usage.

__init__(transactions: None | str | list[int] | list[tuple[int, int]] = None, key_id: str | None = None, resource_pool: str | None = None, target_schema: None | str | dict = None, overwrite: bool = False, add_profile: bool = True, check_tables: bool = True, iterchecks: bool = False) → None

Methods

__init__([transactions, key_id, ...])
export_profile(filename)	The export_profile() method provides a high-level interface for creating an export bundle of parquet files from a QueryProfiler instance.
get_cluster_config()	Returns the Cluster configuration.
get_cpu_time([kind, reverse, categoryorder, ...])	Returns the CPU Time by node and path_id chart.
get_qduration([unit])	Returns the Query duration.
get_qexecution([node_name, metric, path_id, ...])	Returns the Query execution chart.
get_qexecution_report()	Returns the Query execution report.
get_qplan([return_report, print_plan])	Returns the Query Plan chart.
get_qplan_profile([unit, kind, ...])	Returns the Query Plan chart.
get_qplan_tree([path_id, path_id_info, ...])	Draws the Query Plan tree.
get_qsteps([unit, kind, categoryorder, show])	Returns the Query Execution Steps chart.
get_queries()	Returns all the queries and their respective information, of a QueryProfiler object.
get_query_events()	Returns a :py:class`vDataFrame` that contains a table listing query events.
get_request([indent_sql, print_sql, return_html])	Returns the query linked to the object with the specified transaction ID and statement ID.
get_rp_status()	Returns the RP status.
get_table([table_name])	Returns the associated Vertica Table.
get_version()	Returns the current Vertica version.
import_profile(target_schema, key_id, filename)	The static method import_profile can be used to create new QueryProfiler object from the contents of a export bundle.
next()	A utility function to utilize the next transaction from the QueryProfiler stack.
previous()	A utility function to utilize the previous transaction from the QueryProfiler stack.
set_position(idx)	A utility function to utilize a specific transaction from the QueryProfiler stack.
step(idx, *args, **kwargs)	Function to return the QueryProfiler Step.