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