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 | int | tuple | list[int] | list[tuple[int, int]] | list[str] = 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, print_info: bool = True)

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

Searching the performance tables...
Setting the requests and queries durations...
Checking all the tables consistency using a single SQL query...
Checking all the tables data types...

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=45035996275851310;sid=2

Or simply:

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

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

qprof = QueryProfiler((tid, sid))
Searching the performance tables...
Setting the requests and queries durations...
Checking all the tables consistency using a single SQL query...
Checking all the tables data types...

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-08-08 16:42:51.347633+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	27	68734	QUERY	verticapy_json			153759		❌	-2739771977645684132
2	2024-08-08 16:42:51.373739+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	28	68735	QUERY				153759		❌	1173375758661070897
3	2024-08-08 16:42:51.397080+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	29	68736	QUERY	get_data_types			153759		❌	-6803372202362044141
4	2024-08-08 16:42:51.437976+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	30	68737	QUERY	verticapy_json			153759		❌	-2739771977645684132
5	2024-08-08 16:42:51.459713+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	31	68738	QUERY	_version			153759		❌	8903476768692993780
6	2024-08-08 16:42:51.482054+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	32	68739	QUERY	vDataframe.train_test_split			153759		❌	-6060496486953227029
7	2024-08-08 16:42:51.510432+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	33	68740	QUERY	verticapy_json			153759		❌	-2739771977645684132
8	2024-08-08 16:42:51.533256+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	34	68741	QUERY				153759		❌	6814844219549955390
9	2024-08-08 16:42:51.557521+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	35	68742	QUERY	verticapy_json			153759		❌	-2739771977645684132
10	2024-08-08 16:42:51.579922+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	36	68743	QUERY				153759		❌	6266927469521968618
11	2024-08-08 16:42:51.607922+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	37	68744	QUERY	_version			153759		❌	8903476768692993780
12	2024-08-08 16:42:51.631732+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	38	68745	QUERY	verticapy_json			153759		❌	-2739771977645684132
13	2024-08-08 16:42:51.650471+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	39	68746	QUERY				153759		❌	7011138784295650856
14	2024-08-08 16:42:51.669162+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	40	68747	QUERY	_version			153759		❌	8903476768692993780
15	2024-08-08 16:42:51.691807+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	41	68748	QUERY	learn.tools._is_already_stored			153759		❌	5835180282835082858
16	2024-08-08 16:42:51.734866+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	42	68749	QUERY				153759		❌	7011138784295650856
17	2024-08-08 16:42:51.754923+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844076	43	68750	QUERY				153759		❌	7011138784295650856
18	2024-08-08 16:42:51.777708+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844120	1	68751	DDL				153759		❌	[null]
19	2024-08-08 16:42:51.781022+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844120	-1	68752	UTILITY	learn.VerticaModel.fit			153759		❌	[null]
20	2024-08-08 16:42:51.785874+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844123	1	68753	QUERY				153759		❌	-5861902804668782526
21	2024-08-08 16:42:51.826198+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844125	1	68754	QUERY				153759		❌	8988585509947565202
22	2024-08-08 16:42:51.855476+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68755	UTILITY				153759		❌	[null]
23	2024-08-08 16:42:51.871969+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844129	1	68756	QUERY				153760		❌	-4278133178688953219
24	2024-08-08 16:42:51.904913+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844131	1	68757	QUERY				153760		❌	-4021881530365329931
25	2024-08-08 16:42:51.924520+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68758	UTILITY				153760		❌	[null]
26	2024-08-08 16:42:51.964319+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844136	1	68759	QUERY				153761		❌	-5861036256698212095
27	2024-08-08 16:42:51.994613+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844138	1	68760	DDL				153761		❌	[null]
28	2024-08-08 16:42:51.997126+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	1	68761	QUERY	_version			153761		❌	8903476768692993780
29	2024-08-08 16:42:52.018692+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	2	68762	QUERY				153761		❌	4841572230508595135
30	2024-08-08 16:42:52.041354+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	3	68763	QUERY	_version			153761		❌	8903476768692993780
31	2024-08-08 16:42:52.064245+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	4	68764	QUERY	learn.VerticaModel.__repr__			153761		❌	-8887346284077529213
32	2024-08-08 16:42:52.091399+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	5	68765	QUERY	_version			153761		❌	8903476768692993780
33	2024-08-08 16:42:52.114547+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	6	68766	QUERY				153761		❌	8041259019814449157
34	2024-08-08 16:42:52.146857+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	7	68767	QUERY	_version			153761		❌	8903476768692993780
35	2024-08-08 16:42:52.172339+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	8	68768	QUERY				153761		❌	4841572230508595135
36	2024-08-08 16:42:52.288546+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	9	68769	QUERY	verticapy_json			153761		❌	-2739771977645684132
37	2024-08-08 16:42:52.311794+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	10	68770	QUERY	verticapy_json			153761		❌	-2739771977645684132
38	2024-08-08 16:42:52.336719+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	11	68771	QUERY				153761		❌	1173375758661070897
39	2024-08-08 16:42:52.360927+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	12	68772	QUERY	get_data_types			153761		❌	-6803372202362044141
40	2024-08-08 16:42:52.393517+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	13	68773	QUERY	verticapy_json			153761		❌	-2739771977645684132
41	2024-08-08 16:42:52.416613+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	14	68774	QUERY	_version			153761		❌	8903476768692993780
42	2024-08-08 16:42:52.440423+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	15	68775	QUERY	vDataframe.train_test_split			153761		❌	-6060496486953227029
43	2024-08-08 16:42:52.464766+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	16	68776	QUERY	verticapy_json			153761		❌	-2739771977645684132
44	2024-08-08 16:42:52.487590+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	17	68777	QUERY				153761		❌	6814844219549955390
45	2024-08-08 16:42:52.508775+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	18	68778	QUERY	verticapy_json			153761		❌	-2739771977645684132
46	2024-08-08 16:42:52.530915+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	19	68779	QUERY				153761		❌	6266927469521968618
47	2024-08-08 16:42:52.559617+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	20	68780	QUERY	_version			153761		❌	8903476768692993780
48	2024-08-08 16:42:52.582031+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	21	68781	QUERY	verticapy_json			153761		❌	-2739771977645684132
49	2024-08-08 16:42:52.602737+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	22	68782	QUERY				153761		❌	7011138784295650856
50	2024-08-08 16:42:52.628084+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	23	68783	QUERY	learn.tools._is_already_stored			153761		❌	5835180282835082858
51	2024-08-08 16:42:52.675301+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	24	68784	QUERY				153761		❌	7011138784295650856
52	2024-08-08 16:42:52.697800+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844139	25	68785	QUERY				153761		❌	7011138784295650856
53	2024-08-08 16:42:52.720967+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844165	1	68786	DDL				153761		❌	[null]
54	2024-08-08 16:42:52.725299+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844165	-1	68787	UTILITY	learn.VerticaModel.fit			153761		❌	[null]
55	2024-08-08 16:42:52.730882+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844168	1	68788	QUERY				153761		❌	-8430327111529963382
56	2024-08-08 16:42:52.762960+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68789	UTILITY				153761		❌	[null]
57	2024-08-08 16:42:52.783698+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844172	1	68790	QUERY				153762		❌	101314740696625926
58	2024-08-08 16:42:52.825274+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844174	1	68791	QUERY				153762		❌	6493630772050899526
59	2024-08-08 16:42:52.854059+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68792	UTILITY				153762		❌	[null]
60	2024-08-08 16:42:52.870820+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844178	1	68793	QUERY				153763		❌	-1032109414052218166
61	2024-08-08 16:42:52.926383+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68794	UTILITY				153763		❌	[null]
62	2024-08-08 16:42:52.942136+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844182	1	68795	QUERY				153764		❌	2595799903314186095
63	2024-08-08 16:42:52.961319+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68796	UTILITY				153764		❌	[null]
64	2024-08-08 16:42:52.975021+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844186	1	68797	QUERY				153765		❌	-1630482069323377711
65	2024-08-08 16:42:52.995428+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	0	-1	68798	UTILITY				153765		❌	[null]
66	2024-08-08 16:42:53.188897+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844190	1	68799	QUERY				153766		❌	-5861036256698212095
67	2024-08-08 16:42:53.224381+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844192	1	68800	QUERY				153766		❌	-5861036256698212095
68	2024-08-08 16:42:53.260141+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844194	1	68801	DDL				153766		❌	[null]
69	2024-08-08 16:42:53.263005+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	1	68802	QUERY	learn.VerticaModel.fit			153766		❌	-7938121625303578740
70	2024-08-08 16:42:53.293309+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	2	68803	QUERY	_version			153766		❌	8903476768692993780
71	2024-08-08 16:42:53.317776+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	3	68804	QUERY				153766		❌	806487162735703196
72	2024-08-08 16:42:53.336041+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	4	68805	QUERY	_version			153766		❌	8903476768692993780
73	2024-08-08 16:42:53.362156+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	5	68806	QUERY				153766		❌	806487162735703196
74	2024-08-08 16:42:53.394421+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	6	68807	QUERY	_version			153766		❌	8903476768692993780
75	2024-08-08 16:42:53.420513+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	7	68808	QUERY				153766		❌	806487162735703196
76	2024-08-08 16:42:53.454063+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	8	68809	QUERY	_version			153766		❌	8903476768692993780
77	2024-08-08 16:42:53.481144+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	9	68810	QUERY				153766		❌	806487162735703196
78	2024-08-08 16:42:53.514634+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	10	68811	QUERY	_version			153766		❌	8903476768692993780
79	2024-08-08 16:42:53.536197+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	11	68812	QUERY				153766		❌	806487162735703196
80	2024-08-08 16:42:53.563473+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	12	68813	QUERY	_version			153766		❌	8903476768692993780
81	2024-08-08 16:42:53.584998+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	13	68814	QUERY				153766		❌	806487162735703196
82	2024-08-08 16:42:53.611726+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	14	68815	QUERY	_version			153766		❌	8903476768692993780
83	2024-08-08 16:42:53.634334+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	15	68816	QUERY				153766		❌	806487162735703196
84	2024-08-08 16:42:53.660155+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	16	68817	QUERY	_version			153766		❌	8903476768692993780
85	2024-08-08 16:42:53.682203+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	17	68818	QUERY				153766		❌	806487162735703196
86	2024-08-08 16:42:53.709312+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	18	68819	QUERY	_version			153766		❌	8903476768692993780
87	2024-08-08 16:42:53.730732+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	19	68820	QUERY				153766		❌	806487162735703196
88	2024-08-08 16:42:53.757355+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	20	68821	QUERY	_version			153766		❌	8903476768692993780
89	2024-08-08 16:42:53.781431+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	21	68822	QUERY				153766		❌	806487162735703196
90	2024-08-08 16:42:53.813594+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	22	68823	QUERY	_version			153766		❌	8903476768692993780
91	2024-08-08 16:42:53.837248+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	23	68824	QUERY	learn.VerticaModel.__repr__			153766		❌	-8887346284077529213
92	2024-08-08 16:42:53.904002+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	24	68825	QUERY	verticapy_json			153766		❌	-2739771977645684132
93	2024-08-08 16:42:53.926976+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	25	68826	QUERY	verticapy_json			153766		❌	-2739771977645684132
94	2024-08-08 16:42:53.948661+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	26	68827	QUERY				153766		❌	1173375758661070897
95	2024-08-08 16:42:53.972131+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	27	68828	QUERY	get_data_types			153766		❌	-6803372202362044141
96	2024-08-08 16:42:54.006605+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	28	68829	QUERY	verticapy_json			153766		❌	-2739771977645684132
97	2024-08-08 16:42:54.027828+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	29	68830	QUERY	_version			153766		❌	8903476768692993780
98	2024-08-08 16:42:54.048318+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	30	68831	QUERY	vDataframe.train_test_split			153766		❌	-6060496486953227029
99	2024-08-08 16:42:54.072171+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	31	68832	QUERY	verticapy_json			153766		❌	-2739771977645684132
100	2024-08-08 16:42:54.092802+00:00	v_demo_node0001	v_demo_node0001-145:0x13172	45035996273704962	dbadmin	45035996275844195	32	68833	QUERY				153766		❌	6814844219549955390

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_explain_plans',
 'dc_query_executions',
 'dc_plan_activities',
 'dc_scan_events',
 'dc_slow_events',
 'execution_engine_profiles',
 'host_resources',
 'query_events',
 'query_plan_profiles',
 'query_profiles',
 'projection_storage',
 'projection_usage',
 'resource_pool_status',
 'storage_containers',
 'dc_lock_attempts',
 'dc_plan_resources',
 'configuration_parameters',
 'query_consumption',
 'projections',
 'projection_columns',
 'resource_acquisitions',
 '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)	📅 start_timestamp Timestamp(29)	📅 end_timestamp Timestamp(29)
0	✅	45035996275851310	2			8.409364	2024-08-08 16:45:36.608497	2024-08-08 16:45:45.012267

Rows: 1-1 | Columns: 9

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]: 8.409364

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-08-08 15:45:36.599122+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-08-08 15:45:36.599122+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="#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()

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	82
3	v_demo_node0001	0	17
4	v_demo_node0001	0	18
5	v_demo_node0001	1	95076
6	v_demo_node0001	1	148686
7	v_demo_node0001	3	1359630
8	v_demo_node0001	2	413115
9	v_demo_node0001	3	889985
10	v_demo_node0001	4	11628
11	v_demo_node0001	5	415800
12	v_demo_node0001	5	131469

Rows: 1-12 | Columns: 3

Resource Acquisition Report

To obtain a comprehensive resource acquisition report, including specific details such as queue_wait_time, pool_name… utilize the following syntax:

qprof.get_resource_acquisition()

	Abc node_name Varchar(128)	Abc operator_name Varchar(128)	123 path_id Integer	123 exec_time_us 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)	123 bytes_spilled Integer
1	v_demo_node0001	Join	3	889.985	9999	[null]	95368	95368	[null]	[null]	1055960	[null]	[null]	[null]
2	v_demo_node0001	ExprEval	2	413.115	9999	[null]	95368	95368	[null]	[null]	458930	2.2	[null]	[null]
3	v_demo_node0001	Scan	4	11.628	9999	109494	95368	95368	[null]	[null]	12046	0.0	[null]	[null]
4	v_demo_node0001	TopK	1	95.076	9999	[null]	10	10	[null]	[null]	71803	0.8	[null]	[null]
5	v_demo_node0001	Scan	5	131.469	9997	752791	752791	752791	[null]	[null]	140804	0.0	[null]	[null]
6	v_demo_node0001	ExprEval	0	0.018	10	[null]	10	10	[null]	[null]	17	0.1	[null]	[null]
7	v_demo_node0001	TopK	0	0.017	10	[null]	10	10	[null]	[null]	18	0.1	[null]	[null]
8	v_demo_node0001	ExprEval	1	148.686	[null]	[null]	95368	95368	[null]	[null]	149094	0.1	[null]	[null]
9	v_demo_node0001	StorageUnion	5	415.8	[null]	[null]	752791	752791	[null]	[null]	[null]	4.7	[null]	[null]
10	v_demo_node0001	StorageUnion	3	1359.63	[null]	[null]	95368	95368	[null]	[null]	[null]	8.4	[null]	[null]
11	v_demo_node0001	Root	-1	0.059	[null]	[null]	10	[null]	[null]	[null]	60	[null]	[null]	[null]
12	v_demo_node0001	NewEENode	-1	0.082	[null]	[null]	10	10	[null]	[null]	83	0.4	[null]	[null]

Rows: 1-12 | Columns: 14

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_us 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)	123 bytes_spilled Integer
1	v_demo_node0001	TopK	1	95.076	9999	[null]	10	10	[null]	[null]	71803	0.8	[null]	[null]
2	v_demo_node0001	Join	3	889.985	9999	[null]	95368	95368	[null]	[null]	1055960	[null]	[null]	[null]
3	v_demo_node0001	ExprEval	2	413.115	9999	[null]	95368	95368	[null]	[null]	458930	2.2	[null]	[null]
4	v_demo_node0001	Scan	4	11.628	9999	109494	95368	95368	[null]	[null]	12046	0.0	[null]	[null]
5	v_demo_node0001	Scan	5	131.469	9997	752791	752791	752791	[null]	[null]	140804	0.0	[null]	[null]
6	v_demo_node0001	ExprEval	0	0.018	10	[null]	10	10	[null]	[null]	17	0.1	[null]	[null]
7	v_demo_node0001	TopK	0	0.017	10	[null]	10	10	[null]	[null]	18	0.1	[null]	[null]
8	v_demo_node0001	StorageUnion	3	1359.63	[null]	[null]	95368	95368	[null]	[null]	[null]	8.4	[null]	[null]
9	v_demo_node0001	NewEENode	-1	0.082	[null]	[null]	10	10	[null]	[null]	83	0.4	[null]	[null]
10	v_demo_node0001	Root	-1	0.059	[null]	[null]	10	[null]	[null]	[null]	60	[null]	[null]	[null]
11	v_demo_node0001	ExprEval	1	148.686	[null]	[null]	95368	95368	[null]	[null]	149094	0.1	[null]	[null]
12	v_demo_node0001	StorageUnion	5	415.8	[null]	[null]	752791	752791	[null]	[null]	[null]	4.7	[null]	[null]

Rows: 1-12 | Columns: 14

Query Activity Time Report

To obtain a comprehensive report including Elapsed, exec_time and I/O by node, activity & path_id, utilize the following syntax:

qprof.get_activity_time()

	Abc node_name Varchar(128)	123 path_id Integer	Abc activity Varchar(1024)	Abc abl_id Varchar(62)	123 elaps_us Integer	123 exec_us Integer	Abc Varchar(1024)	123 input_rows Integer	123 input_mb Numeric(38)	123 proc_rows Integer	123 proc_mb Numeric(38)
1	v_demo_node0001	-1	Send Data to Client	1000,0,0	269	59		[null]	[null]	[null]	[null]
2	v_demo_node0001	0	Unused	1011,3,3	247	18		[null]	[null]	[null]	[null]
3	v_demo_node0001	0	TopK	1012,2,2	245	17		[null]	[null]	[null]	[null]
4	v_demo_node0001	1	Unused	1008,5,5	4543625	148686		[null]	[null]	[null]	[null]
5	v_demo_node0001	1	TopK HEAP	1009,4,4	4543623	95076		[null]	[null]	[null]	[null]
6	v_demo_node0001	1	Emit TopK	1010,4,4	258	[null]		[null]	[null]	[null]	[null]
7	v_demo_node0001	2	Evaluate Expressions	1006,7,7	4588090	413115		[null]	[null]	[null]	[null]
8	v_demo_node0001	3	Build Join Inner	1004,8,8	1766300	[null]		[null]	[null]	[null]	[null]
9	v_demo_node0001	3	Join (hash)	1005,8,8	4588151	889985		[null]	[null]	[null]	[null]
10	v_demo_node0001	3	Combine Storage Chunks	1007,6,6	4543627	1359630		0	[null]	[null]	[null]
11	v_demo_node0001	3	reserveVTLoaderResources	1013,6,6	[null]	[null]		[null]	[null]	[null]	[null]
12	v_demo_node0001	4	Scan Projection	1003,9,9	4588180	11628		[null]	[null]	109494	[null]
13	v_demo_node0001	5	Scan Projection	1001,11,11	1105122	131469		[null]	[null]	752791	[null]
14	v_demo_node0001	5	Combine Storage Chunks	1002,10,10	1104958	415800		0	[null]	[null]	[null]
15	v_demo_node0001	5	reserveVTLoaderResources	1014,10,10	[null]	[null]		[null]	[null]	[null]	[null]

Rows: 1-15 | Columns: 11

Projection Data Distribution

To obtain the projection data distribution, utilize the following syntax:

qprof.get_proj_data_distrib()

	Abc table_name Varchar(257)	Abc projection_name Varchar(128)	Abc node_name Varchar(128)	123 row_count Integer	123 used_bytes Integer	123 ROS_count Integer	123 del_rows Integer	123 DV_count Integer

Rows: 0 | Columns: 8

Note

This report is really useful to check if the data are correctly segmented.

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_us",
    kind = "pie",
)

To use multiple nodes:

qprof.get_qexecution(
    node_name = [
        "v_vdash_node0001",
        "v_vdash_node0003",
    ],
    metric = "exec_time_us",
    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	255422	0	1	20		6	10	10	33485598720	13736443904	315035648	5809098752	8589934592	8589934592	953822	77195	1031018	3880	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	✅	24674538	24674538	0	0	23440810	24674538	[null]	0	13	[null]	✅	relativedelta(minutes=+5)	300	AUTO	0	MEDIUM	2	[null]	false	1803139		fffff	ANY
2	v_demo_node0001	45035996273705006	sysquery	✅	1048576	1048576	40348	0	24631332	25927718	[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	26623626	28024870	[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	23635184	24879142	[null]	0	4	[null]	❌	relativedelta(minutes=+5)	300	AUTO	-10	MEDIUM	60	[null]	true	5860202		fffff	ANY
5	v_demo_node0001	45035996273705012	recovery	✅	0	0	0	0	23635184	24879142	[null]	0	22	11	❌	relativedelta(minutes=+5)	300	AUTO	107	MEDIUM	60	[null]	true	1065491		fffff	ANY
6	v_demo_node0001	45035996273705014	dbd	✅	0	0	0	0	23635184	24879142	[null]	0	4	[null]	❌	relativedelta()	0	AUTO	0	MEDIUM	0	[null]	true	5860202		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	2761976	2907344	[null]	0	2	0	❌	relativedelta()	0	AUTO	0	HIGH	0	[null]	false	[null]		fffff	ANY
9	v_demo_node0001	45035996273705104	metadata	✅	204604	204604	0	0	23635184	24879142	[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 | int | tuple | list[int] | list[tuple[int, int]] | list[str] = 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, print_info: bool = True) → 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_activity_time()	Returns the a report including: Elapsed, exec_time and I/O by node, activity & path_id.
get_cluster_config()	Returns the Cluster configuration.
get_cpu_time([kind, reverse, categoryorder, ...])	Returns the CPU Time by node and path_id chart.
get_proj_data_distrib()	Returns the Projection Data Distribution.
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_explain([display_trees])	Returns the tree's query explain plan as a list of titles and trees.
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_resource_acquisition()	Returns the a report including the resource acquisition.
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.
insert(transactions)	Functions to insert new transactions to the copy of the performance tables.
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.
to_html([path])	Creates an HTML report.