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, session_control: None | dict | list[dict] | str | list[str] = None, overwrite: bool = False, add_profile: bool = True, check_tables: bool = True, ignore_operators_check: bool = True, iterchecks: bool = False, print_info: bool = True, run_only_session: bool = True)¶
Base class to profile queries.
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
helpfunction to explore the functionalities of your current documentation.The
QueryProfileris 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_idand astatement_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, thestatement_idwill be set to 1.
- An
- A
tuple: (transaction_id, statement_id).
- A
- A
listoftuples: (transaction_id, statement_id).
- A
- A
listofintegers: the
transaction_id; thestatement_idwill automatically be set to 1.
- A
- A
str: The query to execute. If the
strends with ‘.sql’, it is considered a SQL file, and the query inside will be executed.
- A
- A
listofstr: The
listof 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.
- A
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
requestis 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
dictionaryof schema used to map all the Vertica DC tables. If the tables do not exist, VerticaPy will try to create them automatically.- session_control: str | dict | list, optional
List of parameters used to alter the session. Example:
[{"param1": "val1"}, {"param2": "val2"}, {"param3": "val3"},]. Please note that each input query will be executed with the different sets of parameters.It can also be a
listofstreach one representing a query to execute before running the main ones. Example:ALTER SESSION SET param = val- overwrite: bool, optional
If set to
Trueoverwrites the existing performance tables.- add_profile: bool, optional
If set to
Trueand 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
requestis defined.- check_tables: bool, optional
If set to
Trueall 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.
- ignore_operators_check: bool, optional
If set to
Falseadditional tests are done onoperator_id- 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 toFalse.Note
This parameter is used only when
check_tables is True.- run_only_session: bool, optional
If set to
True, the queries will not be run using the current session parameters. They will be altered usingsession_controlparameter first.Note
This parameter is used only when
session_control is not None.
Attributes¶
- transactions: list
listoftuples:(transaction_id, statement_id). It includes all the transactions of the current schema.- requests: list
listofstr: Transactions Queries.- request_labels: list
listofstr: Queries Labels.- qdurations: list
listofint: 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.
- session_params_non_default_current: dict
Non Default Session Parameters used to run the current/active query.
- 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.
- session_params_non_default: list
listof Non Default Session Parameters used all the queries that are profiled.- overwrite: bool
If set to
Trueoverwrites the existing performance tables.
Examples¶
Initialization¶
First, let’s import the
QueryProfilerobject.from verticapy.performance.vertica import QueryProfiler
There are multiple ways how we can use the
QueryProfiler.From
transaction_idandstatement_idFrom 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_requeststable. We can use this table to fetch thetransaction_idandstatement_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
WHEREcommand 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_idandstatement_idwe 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 thev_temp_schemaschema.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_idwill 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_relationattribute 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=45035996276913960;sid=3
Or simply:
print(qprof.transactions) [(45035996276913960, 3)]
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')
📅timeAbcnode_nameAbcsession_id123user_idAbcuser_name123transaction_id123statement_id123request_idAbcrequest_typeAbclabelAbcAbc123query_start_epochAbc010is_retry123digest1 2024-10-31 11:39:54.166330-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 28 72874 QUERY _version 191466 ❌ 8903476768692993780 2 2024-10-31 11:39:54.270493-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 29 72875 QUERY 191466 ❌ -1608247381113320213 3 2024-10-31 11:39:54.374382-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 30 72876 QUERY _version 191466 ❌ 8903476768692993780 4 2024-10-31 11:39:54.477668-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 31 72877 QUERY 191466 ❌ -1608247381113320213 5 2024-10-31 11:39:54.581248-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 32 72878 QUERY verticapy_json 191466 ❌ -2739771977645684132 6 2024-10-31 11:39:54.686602-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 33 72879 QUERY 191466 ❌ -3785912666495148182 7 2024-10-31 11:39:54.788389-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 34 72880 QUERY _version 191466 ❌ 8903476768692993780 8 2024-10-31 11:39:54.892125-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 35 72881 QUERY 191466 ❌ -1608247381113320213 9 2024-10-31 11:39:54.989293-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 36 72882 QUERY verticapy_json 191466 ❌ -2739771977645684132 10 2024-10-31 11:39:55.077605-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 37 72883 QUERY verticapy_json 191466 ❌ -2739771977645684132 11 2024-10-31 11:39:55.215448-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 38 72884 QUERY verticapy_json 191466 ❌ -2739771977645684132 12 2024-10-31 11:39:55.323092-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 39 72885 QUERY 191466 ❌ 6291359946069853830 13 2024-10-31 11:39:55.480401-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 40 72886 QUERY 191466 ❌ -1511400477213475818 14 2024-10-31 11:39:55.694486-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 41 72887 QUERY _version 191466 ❌ 8903476768692993780 15 2024-10-31 11:39:55.832621-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 42 72888 QUERY 191466 ❌ -1608247381113320213 16 2024-10-31 11:39:55.938363-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 43 72889 QUERY _version 191466 ❌ 8903476768692993780 17 2024-10-31 11:39:56.039421-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 44 72890 QUERY 191466 ❌ -1608247381113320213 18 2024-10-31 11:39:56.140210-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 45 72891 QUERY _version 191466 ❌ 8903476768692993780 19 2024-10-31 11:39:56.239132-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 46 72892 QUERY 191466 ❌ -1608247381113320213 20 2024-10-31 11:39:56.328837-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 47 72893 QUERY verticapy_json 191466 ❌ -2739771977645684132 21 2024-10-31 11:39:56.416225-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 48 72894 QUERY 191466 ❌ -3785912666495148182 22 2024-10-31 11:39:56.575830-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 49 72895 QUERY _version 191466 ❌ 8903476768692993780 23 2024-10-31 11:39:56.662761-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 50 72896 QUERY 191466 ❌ -1608247381113320213 24 2024-10-31 11:39:56.750831-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 51 72897 QUERY verticapy_json 191466 ❌ -2739771977645684132 25 2024-10-31 11:39:56.836362-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 52 72898 QUERY verticapy_json 191466 ❌ -2739771977645684132 26 2024-10-31 11:39:56.920826-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 53 72899 QUERY verticapy_json 191466 ❌ -2739771977645684132 27 2024-10-31 11:39:57.010089-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 54 72900 QUERY 191466 ❌ -2966772127294261944 28 2024-10-31 11:39:57.154934-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 55 72901 QUERY 191466 ❌ -5801875823206420603 29 2024-10-31 11:39:57.305103-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 56 72902 QUERY _version 191466 ❌ 8903476768692993780 30 2024-10-31 11:39:57.447558-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 57 72903 QUERY 191466 ❌ -1608247381113320213 31 2024-10-31 11:39:57.582879-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 58 72904 QUERY _version 191466 ❌ 8903476768692993780 32 2024-10-31 11:39:57.725882-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 59 72905 QUERY 191466 ❌ -1608247381113320213 33 2024-10-31 11:39:57.831957-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 60 72906 QUERY verticapy_json 191466 ❌ -2739771977645684132 34 2024-10-31 11:39:57.936088-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 61 72907 QUERY 191466 ❌ -3785912666495148182 35 2024-10-31 11:39:58.036803-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 62 72908 QUERY _version 191466 ❌ 8903476768692993780 36 2024-10-31 11:39:58.164376-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 63 72909 QUERY 191466 ❌ -1608247381113320213 37 2024-10-31 11:39:58.297094-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 64 72910 QUERY verticapy_json 191466 ❌ -2739771977645684132 38 2024-10-31 11:39:58.412499-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 65 72911 QUERY verticapy_json 191466 ❌ -2739771977645684132 39 2024-10-31 11:39:58.521503-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 66 72912 QUERY verticapy_json 191466 ❌ -2739771977645684132 40 2024-10-31 11:39:58.655320-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 67 72913 QUERY 191466 ❌ 6291359946069853830 41 2024-10-31 11:39:58.815239-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 68 72914 QUERY 191466 ❌ -1511400477213475818 42 2024-10-31 11:39:58.985144-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 69 72915 QUERY _version 191466 ❌ 8903476768692993780 43 2024-10-31 11:39:59.091641-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 70 72916 QUERY 191466 ❌ -1608247381113320213 44 2024-10-31 11:39:59.197532-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 71 72917 QUERY _version 191466 ❌ 8903476768692993780 45 2024-10-31 11:39:59.302978-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 72 72918 QUERY 191466 ❌ -1608247381113320213 46 2024-10-31 11:39:59.409460-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 73 72919 QUERY verticapy_json 191466 ❌ -2739771977645684132 47 2024-10-31 11:39:59.518423-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 74 72920 QUERY 191466 ❌ -3785912666495148182 48 2024-10-31 11:39:59.620678-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 75 72921 QUERY _version 191466 ❌ 8903476768692993780 49 2024-10-31 11:39:59.734175-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 76 72922 QUERY 191466 ❌ -1608247381113320213 50 2024-10-31 11:39:59.846604-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 77 72923 QUERY _version 191466 ❌ 8903476768692993780 51 2024-10-31 11:39:59.950714-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 78 72924 QUERY 191466 ❌ -1608247381113320213 52 2024-10-31 11:40:00.054377-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 79 72925 QUERY verticapy_json 191466 ❌ -2739771977645684132 53 2024-10-31 11:40:00.152746-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 80 72926 QUERY verticapy_json 191466 ❌ -2739771977645684132 54 2024-10-31 11:40:00.306257-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 81 72927 QUERY 191466 ❌ 446954750923653105 55 2024-10-31 11:40:00.497522-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 82 72928 QUERY verticapy_json 191466 ❌ -2739771977645684132 56 2024-10-31 11:40:00.601231-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 83 72929 QUERY verticapy_json 191466 ❌ -2739771977645684132 57 2024-10-31 11:40:00.711160-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 84 72930 QUERY 191466 ❌ 6366091046319804507 58 2024-10-31 11:40:00.867119-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 85 72931 QUERY _version 191466 ❌ 8903476768692993780 59 2024-10-31 11:40:00.972587-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 86 72932 QUERY 191466 ❌ -1608247381113320213 60 2024-10-31 11:40:01.077417-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 87 72933 QUERY _version 191466 ❌ 8903476768692993780 61 2024-10-31 11:40:01.181696-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 88 72934 QUERY 191466 ❌ -1608247381113320213 62 2024-10-31 11:40:01.287498-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 89 72935 QUERY verticapy_json 191466 ❌ -2739771977645684132 63 2024-10-31 11:40:01.393194-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 90 72936 QUERY 191466 ❌ -7383708702660407858 64 2024-10-31 11:40:01.499097-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 91 72937 QUERY 191466 ❌ -7383708702660407858 65 2024-10-31 11:40:01.620086-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 92 72938 QUERY _version 191466 ❌ 8903476768692993780 66 2024-10-31 11:40:01.769392-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 93 72939 QUERY 191466 ❌ -1608247381113320213 67 2024-10-31 11:40:01.922751-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 94 72940 QUERY _version 191466 ❌ 8903476768692993780 68 2024-10-31 11:40:02.059694-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 95 72941 QUERY 191466 ❌ -1608247381113320213 69 2024-10-31 11:40:02.174911-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 96 72942 QUERY 191466 ❌ -4202417065770681422 70 2024-10-31 11:40:02.284279-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 97 72943 QUERY verticapy_json 191466 ❌ -2739771977645684132 71 2024-10-31 11:40:02.395887-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 98 72944 QUERY 191466 ❌ -7649610067607386940 72 2024-10-31 11:40:02.556101-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 99 72945 QUERY 191466 ❌ -7649610067607386940 73 2024-10-31 11:40:02.743191-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 100 72946 QUERY _version 191466 ❌ 8903476768692993780 74 2024-10-31 11:40:02.852667-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 101 72947 QUERY 191466 ❌ -1608247381113320213 75 2024-10-31 11:40:02.961584-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 102 72948 QUERY _version 191466 ❌ 8903476768692993780 76 2024-10-31 11:40:03.066649-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 103 72949 QUERY 191466 ❌ -1608247381113320213 77 2024-10-31 11:40:03.158244-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 104 72950 QUERY 191466 ❌ -4202417065770681422 78 2024-10-31 11:40:03.253528-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 105 72951 QUERY verticapy_json 191466 ❌ -2739771977645684132 79 2024-10-31 11:40:03.361351-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 106 72952 QUERY 191466 ❌ -7649610067607386940 80 2024-10-31 11:40:03.474847-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 107 72953 QUERY 191466 ❌ -7649610067607386940 81 2024-10-31 11:40:03.636203-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 108 72954 QUERY _version 191466 ❌ 8903476768692993780 82 2024-10-31 11:40:03.739478-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 109 72955 QUERY 191466 ❌ -1608247381113320213 83 2024-10-31 11:40:03.840249-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 110 72956 QUERY _version 191466 ❌ 8903476768692993780 84 2024-10-31 11:40:03.941101-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 111 72957 QUERY 191466 ❌ -1608247381113320213 85 2024-10-31 11:40:04.048766-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 112 72958 QUERY verticapy_json 191466 ❌ -2739771977645684132 86 2024-10-31 11:40:04.188521-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 113 72959 QUERY 191466 ❌ -7383708702660407858 87 2024-10-31 11:40:04.291514-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 114 72960 QUERY _version 191466 ❌ 8903476768692993780 88 2024-10-31 11:40:04.395484-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 115 72961 QUERY 191466 ❌ -1608247381113320213 89 2024-10-31 11:40:04.496551-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 116 72962 QUERY _version 191466 ❌ 8903476768692993780 90 2024-10-31 11:40:04.641235-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 117 72963 QUERY 191466 ❌ -1608247381113320213 91 2024-10-31 11:40:04.786700-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 118 72964 QUERY verticapy_json 191466 ❌ -2739771977645684132 92 2024-10-31 11:40:04.892481-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 119 72965 QUERY verticapy_json 191466 ❌ -2739771977645684132 93 2024-10-31 11:40:05.003042-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 120 72966 QUERY 191466 ❌ -5027268506192616290 94 2024-10-31 11:40:05.110443-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 121 72967 QUERY verticapy_json 191466 ❌ -2739771977645684132 95 2024-10-31 11:40:05.214704-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 122 72968 QUERY 191466 ❌ 2586552547480450508 96 2024-10-31 11:40:05.317302-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 123 72969 QUERY verticapy_json 191466 ❌ -2739771977645684132 97 2024-10-31 11:40:05.418442-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 124 72970 QUERY verticapy_json 191466 ❌ -2739771977645684132 98 2024-10-31 11:40:05.522808-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 125 72971 QUERY 191466 ❌ -3237197799321656504 99 2024-10-31 11:40:05.626419-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 126 72972 QUERY verticapy_json 191466 ❌ -2739771977645684132 100 2024-10-31 11:40:05.730480-04:00 v_mldb_node0001 v_mldb_node0001-123346:0xf2be5b 45035996273704962 release 45035996276912034 127 72973 QUERY verticapy_json 191466 ❌ -2739771977645684132 Rows: 1-100 | Columns: 16Note
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()
010is_current123transaction_id123statement_idAbcrequest_labelAbc123qduration📅start_timestamp📅end_timestamp0 ✅ 45035996276913960 3 5.102154 2024-10-31 11:42:24.515589 2024-10-31 11:42:29.595475 Rows: 1-1 | Columns: 9Executing 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]: (24, 3, 0, 20240423)
Note
To explore all available methods, please refer to the ‘Methods’ section. For additional information, you can also utilize the
helpfunction.It is possible to access the same step by using the
stepmethod.qprof.step(idx = 0) Out[10]: (24, 3, 0, 20240423)
Note
By changing the
idxvalue above, you can check out all the steps of theQueryProfiler.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]: 5.102154
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_qstepsattribute.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
categoryorderto sort the chart orwidthandheightto manage the size.Query Plan
To get the entire query plan:
qprof.get_qplan() +-SELECT LIMIT 10 [Cost: 62K, Rows: 10 (NO STATISTICS)] (PATH ID: 0) | Output Only: 10 tuples | Execute on: Query Initiator | +---> SORT [TOPK] [Cost: 62K, Rows: 10K (NO STATISTICS)] (PATH ID: 1) | | Order: query_requests.request_duration DESC | | Output Only: 10 tuples | | Execute on: All Nodes | | Execute on: All Nodes | | +---> JOIN HASH [LeftOuter] [Cost: 2K, 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 | | | Execute on: All Nodes | | | +-- Outer -> STORAGE ACCESS for ri [Cost: 649, 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-10-31 10:42:24.287859-04'::timestamptz) | | | | Execute on: All Nodes | | | +-- Inner -> STORAGE ACCESS for dc_requests_completed [Cost: 965, 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) | | | | Execute on: All Nodes Out[12]: '+-SELECT LIMIT 10 [Cost: 62K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)\n| Output Only: 10 tuples\n| Execute on: Query Initiator\n| +---> SORT [TOPK] [Cost: 62K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)\n| | Order: query_requests.request_duration DESC\n| | Output Only: 10 tuples\n| | Execute on: All Nodes\n| | Execute on: All Nodes\n| | +---> JOIN HASH [LeftOuter] [Cost: 2K, 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| | | Execute on: All Nodes\n| | | +-- Outer -> STORAGE ACCESS for ri [Cost: 649, 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-10-31 10:42:24.287859-04\'::timestamptz)\n| | | | Execute on: All Nodes\n| | | +-- Inner -> STORAGE ACCESS for dc_requests_completed [Cost: 965, 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)\n| | | | Execute on: All Nodes'
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="#FFFFFFDD"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Path transitions</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">I</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">INNER</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="#FFFFFFDD"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Links</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">___</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">LOCAL</FONT></td></tr><tr><td BGCOLOR="#FFFFFFDD"></td><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">Information</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">🟢</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">QUERY INITIATOR</FONT></td></tr><tr><td BGCOLOR="#DFDFDF"><FONT COLOR="#000000">🌐</FONT></td><td BGCOLOR="#FFFFFFDD"><FONT COLOR="#000000">ALL NODES</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">56</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">658</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">8K</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">88K</FONT></td></tr><tr><td BGCOLOR="#FF0000"><FONT COLOR="#000000">1M</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">10</FONT></td></tr><tr><td BGCOLOR="#3FBF00"><FONT COLOR="#000000">173</FONT></td></tr><tr><td BGCOLOR="#7F7F00"><FONT COLOR="#000000">3K</FONT></td></tr><tr><td BGCOLOR="#BF3F00"><FONT COLOR="#000000">44K</FONT></td></tr><tr><td BGCOLOR="#FF0000"><FONT COLOR="#000000">694K</FONT></td></tr></table>>]\n\n\t0 [width=1.22, height=1.22, tooltip="SELECT LIMIT 10 [Cost: 62K, Rows: 10 (NO STATISTICS)] (PATH ID: 0)\n * Execution time in µs: 56\n * Produced row count: 10\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 1\n - Execution time in µs: 56\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 59\n - Reserved memory size in B: 130,932\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nTopK:\n - Number of threads: 1\n - Execution time in µs: 56\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 59\n - Reserved memory size in B: 128,064\n - Allocated memory size in B: NULL\n\nExprEval:\n - Number of threads: 1\n - Execution time in µs: 40\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 39\n - Reserved memory size in B: 130,932\n - Allocated memory size in B: NULL\n\nDescriptors\n------------\n\nOutput Only: 10 tuples\n\nExecute on: Query Initiator", 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: 62K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)\n * Execution time in µs: 112,161\n * Produced row count: 31,455\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 4\n - Execution time in µs: 112,161\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 111,958\n - Reserved memory size in B: 21,741,048\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nNetworkRecv:\n - Number of threads: 4\n - Execution time in µs: 380\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: NULL\n - Reserved memory size in B: 21,741,048\n - Allocated memory size in B: NULL\n\nNetworkSend:\n - Number of threads: 1\n - Execution time in µs: 67\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: NULL\n - Reserved memory size in B: 8,789,832\n - Allocated memory size in B: NULL\n\nTopK:\n - Number of threads: 2\n - Execution time in µs: 68,538\n - Processed row count: NULL\n - Produced row count: 10\n - Clock time in µs: 47,911\n - Reserved memory size in B: 768,392\n - Allocated memory size in B: NULL\n\nExprEval:\n - Number of threads: 1\n - Execution time in µs: 112,161\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 111,958\n - Reserved memory size in B: 130,932\n - Allocated memory size in B: NULL\n\nDescriptors\n------------\n\nOrder: query_requests.request_duration DESC\n\nOutput Only: 10 tuples\n\nExecute on: All Nodes\n\nExecute on: All Nodes", 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="#C53900" ><FONT COLOR="#C53900">.</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="#B74700"><FONT COLOR="#B74700">.</FONT></TD></TR></TABLE>>];\n\t3 [width=1.22, height=1.22, tooltip="JOIN HASH [LeftOuter] [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)\n * Execution time in µs: 1,024,494\n * Produced row count: 31,455\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 2\n - Execution time in µs: 1,024,494\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 1,442,725\n - Reserved memory size in B: 26,462,208\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nStorageUnion:\n - Number of threads: 1\n - Execution time in µs: 307,978\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: NULL\n - Reserved memory size in B: 8,385,604\n - Allocated memory size in B: NULL\n\nJoin:\n - Number of threads: 2\n - Execution time in µs: 1,024,494\n - Processed row count: NULL\n - Produced row count: 31,455\n - Clock time in µs: 1,442,725\n - Reserved memory size in B: 26,462,208\n - Allocated memory size in B: NULL\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\n\nExecute on: All Nodes", 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="#B74700"><FONT COLOR="#B74700">.</FONT></TD></TR></TABLE>>];\n\t4 [width=1.22, height=1.22, tooltip="Outer -> STORAGE ACCESS for ri [Cost: 649, Rows: 10K (NO STATISTICS)] (PATH ID: 4)\n * Execution time in µs: 5,941\n * Produced row count: 31,455\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 1\n - Execution time in µs: 5,941\n - Processed row count: 35,014\n - Produced row count: 31,455\n - Clock time in µs: 6,331\n - Reserved memory size in B: 135,392\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nScan:\n - Number of threads: 1\n - Execution time in µs: 5,941\n - Processed row count: 35,014\n - Produced row count: 31,455\n - Clock time in µs: 6,331\n - Reserved memory size in B: 135,392\n - Allocated memory size in B: NULL\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-10-31 10:42:24.287859-04\'::timestamptz)\n\nExecute on: All Nodes", 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="#788600" ><FONT COLOR="#788600">.</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="#B74700"><FONT COLOR="#B74700">.</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: 965, Rows: 10K (NO STATISTICS)] (PATH ID: 5)\n * Execution time in µs: 377,946\n * Produced row count: 694,456\n\nAggregated metrics:\n---------------------\n\n - Number of threads: 1\n - Execution time in µs: 377,946\n - Processed row count: 694,456\n - Produced row count: 694,456\n - Clock time in µs: 199,835\n - Reserved memory size in B: 4,737,028\n - Allocated memory size in B: NULL\n\nMetrics per operator\n---------------------\n\nStorageUnion:\n - Number of threads: 1\n - Execution time in µs: 377,946\n - Processed row count: NULL\n - Produced row count: 694,456\n - Clock time in µs: NULL\n - Reserved memory size in B: 4,737,028\n - Allocated memory size in B: NULL\n\nScan:\n - Number of threads: 1\n - Execution time in µs: 183,262\n - Processed row count: 694,456\n - Produced row count: 694,456\n - Clock time in µs: 199,835\n - Reserved memory size in B: 141,024\n - Allocated memory size in B: NULL\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)\n\nExecute on: All Nodes", 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="#E51900" ><FONT COLOR="#E51900">.</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
kindto “bar”.Query Events
We can easily look at the query events:
qprof.get_query_events()
📅event_timestampAbcnode_nameAbcevent_categoryAbcevent_typeAbcAbcoperator_name123path_idAbcAbcsuggested_action1 2024-10-31 11:42:24.515966-04:00 v_mldb_node0002 EXECUTION EE_COMPILE_MEMORY_ALLOC [null] [null] 2 2024-10-31 11:42:24.543806-04:00 v_mldb_node0002 EXECUTION RUNTIME_PREDICATE_EVAL_ORDER Scan 5 3 2024-10-31 11:42:24.519233-04:00 v_mldb_node0004 EXECUTION EE_COMPILE_MEMORY_ALLOC [null] [null] 4 2024-10-31 11:42:24.546973-04:00 v_mldb_node0004 EXECUTION RUNTIME_PREDICATE_EVAL_ORDER Scan 5 5 2024-10-31 11:42:24.521130-04:00 v_mldb_node0003 EXECUTION EE_COMPILE_MEMORY_ALLOC [null] [null] 6 2024-10-31 11:42:24.546278-04:00 v_mldb_node0003 EXECUTION RUNTIME_PREDICATE_EVAL_ORDER Scan 5 Rows: 1-6 | Columns: 9CPU 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
showoption toFalse.qprof.get_cpu_time(show=False)
Abcnode_nameAbcpath_id123counter_value1 v_mldb_node0004 -1 20 2 v_mldb_node0004 1 17 3 v_mldb_node0004 1 30 4 v_mldb_node0004 1 12 5 v_mldb_node0004 3 55 6 v_mldb_node0004 2 12 7 v_mldb_node0004 3 127 8 v_mldb_node0004 4 14 9 v_mldb_node0004 5 64 10 v_mldb_node0004 5 10 11 v_mldb_node0001 -1 95 12 v_mldb_node0001 -1 227 13 v_mldb_node0001 0 56 14 v_mldb_node0001 0 40 15 v_mldb_node0001 1 380 16 v_mldb_node0001 1 67 17 v_mldb_node0001 1 68538 18 v_mldb_node0001 1 112161 19 v_mldb_node0001 3 307978 20 v_mldb_node0001 2 220365 21 v_mldb_node0001 3 1024494 22 v_mldb_node0001 4 5941 23 v_mldb_node0001 5 377946 24 v_mldb_node0001 5 183262 25 v_mldb_node0002 -1 19 26 v_mldb_node0002 1 17 27 v_mldb_node0002 1 34 28 v_mldb_node0002 1 15 29 v_mldb_node0002 3 58 30 v_mldb_node0002 2 12 31 v_mldb_node0002 3 95 32 v_mldb_node0002 4 14 33 v_mldb_node0002 5 67 34 v_mldb_node0002 5 9 35 v_mldb_node0003 -1 44 36 v_mldb_node0003 1 40 37 v_mldb_node0003 1 76 38 v_mldb_node0003 1 31 39 v_mldb_node0003 3 140 40 v_mldb_node0003 2 31 41 v_mldb_node0003 3 236 42 v_mldb_node0003 4 31 43 v_mldb_node0003 5 154 44 v_mldb_node0003 5 23 Rows: 1-44 | Columns: 3Resource 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()
Abcnode_name123path_id123localplan_idAbcoperator_name123thread_count123exec_time_us123est_rows123proc_rows123prod_rows123rle_prod_rows123cstall_us123pstall_us123clock_time_us123mem_res_b123mem_all_b123bytes_spilled123blocks_filtered_sip123blocks_analyzed_sip123container_rows_filtered_sip123container_rows_filtered_pred123container_rows_pruned_sip123container_rows_pruned_pred123container_rows_pruned_valindex123hash_tables_spilled_sort123join_inner_clock_time_us123join_inner_exec_time_us123join_outer_clock_time_us123join_outer_exec_time_us123network_wait_us123producer_stall_us123producer_wait_us123request_wait_us123response_wait_us123recv_net_time_us123recv_wait_us123rows_filtered_sip123rows_pruned_valindex123rows_processed_sip123total_rows_read_join_sort123total_rows_read_sort1 v_mldb_node0001 -1 0 Root 1 95 [null] [null] 10 [null] [null] [null] 101 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 2 v_mldb_node0001 1 5 NetworkSend 1 67 9999 [null] 10 10 2941993 0 [null] 8397512 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 2942133 [null] [null] [null] [null] [null] [null] [null] [null] [null] 3 v_mldb_node0001 2 9 ExprEval 1 220365 9999 [null] 31455 31455 [null] [null] 221832 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 4 v_mldb_node0001 5 12 StorageUnion 1 377946 [null] [null] 694456 694456 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 311460 [null] [null] [null] [null] [null] [null] [null] 5 v_mldb_node0001 5 13 Scan 1 183262 9997 694456 694456 694456 [null] [null] 199835 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 6 v_mldb_node0002 1 3 ExprEval 1 15 [null] [null] 0 0 [null] [null] 15 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 7 v_mldb_node0002 3 4 StorageUnion 1 58 [null] [null] 0 0 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 301 [null] [null] [null] [null] [null] [null] [null] 8 v_mldb_node0002 4 7 Scan 1 14 9999 0 0 0 [null] [null] 15 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 9 v_mldb_node0002 5 9 Scan 1 9 9997 0 0 0 [null] [null] 10 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 10 v_mldb_node0003 1 2 TopK 1 76 9999 [null] 0 0 [null] [null] 77 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 11 v_mldb_node0003 3 4 StorageUnion 1 140 [null] [null] 0 0 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 708 [null] [null] [null] [null] [null] [null] [null] 12 v_mldb_node0003 3 6 Join 2 236 9999 [null] 0 0 [null] [null] 233 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 444 318 39 45 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 13 v_mldb_node0004 5 8 StorageUnion 1 64 [null] [null] 0 0 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 57 [null] [null] [null] [null] [null] [null] [null] 14 v_mldb_node0004 5 9 Scan 1 10 9997 0 0 0 [null] [null] 10 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 15 v_mldb_node0001 3 10 Join 2 1024494 9999 [null] 31455 31455 [null] [null] 1442725 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 1865804 1230538 1073209 682117 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 16 v_mldb_node0002 1 1 NetworkSend 1 17 9999 [null] 0 0 2069630 0 [null] 8789832 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 401 [null] [null] [null] [null] [null] [null] [null] [null] [null] 17 v_mldb_node0003 1 1 NetworkSend 1 40 9999 [null] 0 0 2069159 0 [null] 8789832 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 940 [null] [null] [null] [null] [null] [null] [null] [null] [null] 18 v_mldb_node0003 1 3 ExprEval 1 31 [null] [null] 0 0 [null] [null] 31 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 19 v_mldb_node0004 -1 0 NewEENode 1 20 [null] [null] 0 0 [null] [null] 18 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 20 v_mldb_node0004 1 1 NetworkSend 1 17 9999 [null] 0 0 2069647 0 [null] 8789832 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 418 [null] [null] [null] [null] [null] [null] [null] [null] [null] 21 v_mldb_node0004 3 6 Join 2 127 9999 [null] 0 0 [null] [null] 128 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 220 170 15 19 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 22 v_mldb_node0004 4 7 Scan 1 14 9999 0 0 0 [null] [null] 14 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 23 v_mldb_node0001 -1 1 NewEENode 1 227 [null] [null] 10 10 [null] [null] 227 393216 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 24 v_mldb_node0001 0 2 TopK 1 56 10 [null] 10 10 [null] [null] 59 128064 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 25 v_mldb_node0001 0 3 ExprEval 1 40 10 [null] 10 10 [null] [null] 39 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 26 v_mldb_node0001 1 6 TopK 2 68538 9999 [null] 10 10 [null] [null] 47911 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 27 v_mldb_node0001 1 7 ExprEval 1 112161 [null] [null] 31455 31455 [null] [null] 111958 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 28 v_mldb_node0002 -1 0 NewEENode 1 19 [null] [null] 0 0 [null] [null] 19 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 29 v_mldb_node0002 3 6 Join 2 95 9999 [null] 0 0 [null] [null] 98 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 206 142 18 20 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 30 v_mldb_node0002 5 8 StorageUnion 1 67 [null] [null] 0 0 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 67 [null] [null] [null] [null] [null] [null] [null] 31 v_mldb_node0003 -1 0 NewEENode 1 44 [null] [null] 0 0 [null] [null] 44 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 32 v_mldb_node0003 2 5 ExprEval 1 31 9999 [null] 0 0 [null] [null] 32 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 33 v_mldb_node0003 4 7 Scan 1 31 9999 0 0 0 [null] [null] 33 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 34 v_mldb_node0004 1 3 ExprEval 1 12 [null] [null] 0 0 [null] [null] 12 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 35 v_mldb_node0004 3 4 StorageUnion 1 55 [null] [null] 0 0 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 325 [null] [null] [null] [null] [null] [null] [null] 36 v_mldb_node0001 1 4 NetworkRecv 4 380 9999 [null] 10 10 [null] [null] [null] 21741048 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 737 [null] [null] [null] [null] 0 737 [null] [null] [null] [null] [null] 37 v_mldb_node0001 3 8 StorageUnion 1 307978 [null] [null] 31455 31455 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 2574626 [null] [null] [null] [null] [null] [null] [null] 38 v_mldb_node0001 4 11 Scan 1 5941 9999 35014 31455 31455 [null] [null] 6331 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 39 v_mldb_node0002 1 2 TopK 1 34 9999 [null] 0 0 [null] [null] 34 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 40 v_mldb_node0002 2 5 ExprEval 1 12 9999 [null] 0 0 [null] [null] 12 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 41 v_mldb_node0003 5 8 StorageUnion 1 154 [null] [null] 0 0 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 146 [null] [null] [null] [null] [null] [null] [null] 42 v_mldb_node0003 5 9 Scan 1 23 9997 0 0 0 [null] [null] 25 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 43 v_mldb_node0004 1 2 TopK 1 30 9999 [null] 0 0 [null] [null] 31 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 44 v_mldb_node0004 2 5 ExprEval 1 12 9999 [null] 0 0 [null] [null] 11 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] Rows: 1-44 | Columns: 40Query 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()
Abcnode_name123path_id123localplan_idAbcoperator_name123thread_count123exec_time_us123est_rows123proc_rows123prod_rows123rle_prod_rows123cstall_us123pstall_us123clock_time_us123mem_res_b123mem_all_b123bytes_spilled123blocks_filtered_sip123blocks_analyzed_sip123container_rows_filtered_sip123container_rows_filtered_pred123container_rows_pruned_sip123container_rows_pruned_pred123container_rows_pruned_valindex123hash_tables_spilled_sort123join_inner_clock_time_us123join_inner_exec_time_us123join_outer_clock_time_us123join_outer_exec_time_us123network_wait_us123producer_stall_us123producer_wait_us123request_wait_us123response_wait_us123recv_net_time_us123recv_wait_us123rows_filtered_sip123rows_pruned_valindex123rows_processed_sip123total_rows_read_join_sort123total_rows_read_sort1 v_mldb_node0001 3 10 Join 2 1024494 9999 [null] 31455 31455 [null] [null] 1442725 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 1865804 1230538 1073209 682117 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 2 v_mldb_node0002 1 1 NetworkSend 1 17 9999 [null] 0 0 2069630 0 [null] 8789832 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 401 [null] [null] [null] [null] [null] [null] [null] [null] [null] 3 v_mldb_node0003 1 1 NetworkSend 1 40 9999 [null] 0 0 2069159 0 [null] 8789832 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 940 [null] [null] [null] [null] [null] [null] [null] [null] [null] 4 v_mldb_node0003 1 3 ExprEval 1 31 [null] [null] 0 0 [null] [null] 31 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 5 v_mldb_node0004 -1 0 NewEENode 1 20 [null] [null] 0 0 [null] [null] 18 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 6 v_mldb_node0004 1 1 NetworkSend 1 17 9999 [null] 0 0 2069647 0 [null] 8789832 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 418 [null] [null] [null] [null] [null] [null] [null] [null] [null] 7 v_mldb_node0004 3 6 Join 2 127 9999 [null] 0 0 [null] [null] 128 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 220 170 15 19 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 8 v_mldb_node0004 4 7 Scan 1 14 9999 0 0 0 [null] [null] 14 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 9 v_mldb_node0001 -1 0 Root 1 95 [null] [null] 10 [null] [null] [null] 101 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 10 v_mldb_node0001 1 5 NetworkSend 1 67 9999 [null] 10 10 2941993 0 [null] 8397512 [null] 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 0 2942133 [null] [null] [null] [null] [null] [null] [null] [null] [null] 11 v_mldb_node0001 2 9 ExprEval 1 220365 9999 [null] 31455 31455 [null] [null] 221832 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 12 v_mldb_node0001 5 12 StorageUnion 1 377946 [null] [null] 694456 694456 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 311460 [null] [null] [null] [null] [null] [null] [null] 13 v_mldb_node0001 5 13 Scan 1 183262 9997 694456 694456 694456 [null] [null] 199835 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 14 v_mldb_node0002 1 3 ExprEval 1 15 [null] [null] 0 0 [null] [null] 15 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 15 v_mldb_node0002 3 4 StorageUnion 1 58 [null] [null] 0 0 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 301 [null] [null] [null] [null] [null] [null] [null] 16 v_mldb_node0002 4 7 Scan 1 14 9999 0 0 0 [null] [null] 15 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 17 v_mldb_node0002 5 9 Scan 1 9 9997 0 0 0 [null] [null] 10 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 18 v_mldb_node0003 1 2 TopK 1 76 9999 [null] 0 0 [null] [null] 77 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 19 v_mldb_node0003 3 4 StorageUnion 1 140 [null] [null] 0 0 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 708 [null] [null] [null] [null] [null] [null] [null] 20 v_mldb_node0003 3 6 Join 2 236 9999 [null] 0 0 [null] [null] 233 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 444 318 39 45 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 21 v_mldb_node0004 5 8 StorageUnion 1 64 [null] [null] 0 0 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 57 [null] [null] [null] [null] [null] [null] [null] 22 v_mldb_node0004 5 9 Scan 1 10 9997 0 0 0 [null] [null] 10 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 23 v_mldb_node0001 1 4 NetworkRecv 4 380 9999 [null] 10 10 [null] [null] [null] 21741048 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 737 [null] [null] [null] [null] 0 737 [null] [null] [null] [null] [null] 24 v_mldb_node0001 3 8 StorageUnion 1 307978 [null] [null] 31455 31455 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 2574626 [null] [null] [null] [null] [null] [null] [null] 25 v_mldb_node0001 4 11 Scan 1 5941 9999 35014 31455 31455 [null] [null] 6331 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 26 v_mldb_node0002 1 2 TopK 1 34 9999 [null] 0 0 [null] [null] 34 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 27 v_mldb_node0002 2 5 ExprEval 1 12 9999 [null] 0 0 [null] [null] 12 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 28 v_mldb_node0003 5 8 StorageUnion 1 154 [null] [null] 0 0 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 146 [null] [null] [null] [null] [null] [null] [null] 29 v_mldb_node0003 5 9 Scan 1 23 9997 0 0 0 [null] [null] 25 141024 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 30 v_mldb_node0004 1 2 TopK 1 30 9999 [null] 0 0 [null] [null] 31 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 31 v_mldb_node0004 2 5 ExprEval 1 12 9999 [null] 0 0 [null] [null] 11 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 32 v_mldb_node0001 -1 1 NewEENode 1 227 [null] [null] 10 10 [null] [null] 227 393216 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 33 v_mldb_node0001 0 2 TopK 1 56 10 [null] 10 10 [null] [null] 59 128064 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 34 v_mldb_node0001 0 3 ExprEval 1 40 10 [null] 10 10 [null] [null] 39 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 35 v_mldb_node0001 1 6 TopK 2 68538 9999 [null] 10 10 [null] [null] 47911 768392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 36 v_mldb_node0001 1 7 ExprEval 1 112161 [null] [null] 31455 31455 [null] [null] 111958 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 37 v_mldb_node0002 -1 0 NewEENode 1 19 [null] [null] 0 0 [null] [null] 19 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 38 v_mldb_node0002 3 6 Join 2 95 9999 [null] 0 0 [null] [null] 98 26462208 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 206 142 18 20 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 39 v_mldb_node0002 5 8 StorageUnion 1 67 [null] [null] 0 0 [null] [null] [null] 4737028 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 67 [null] [null] [null] [null] [null] [null] [null] 40 v_mldb_node0003 -1 0 NewEENode 1 44 [null] [null] 0 0 [null] [null] 44 0 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 41 v_mldb_node0003 2 5 ExprEval 1 31 9999 [null] 0 0 [null] [null] 32 2180041 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 42 v_mldb_node0003 4 7 Scan 1 31 9999 0 0 0 [null] [null] 33 135392 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 [null] [null] [null] 43 v_mldb_node0004 1 3 ExprEval 1 12 [null] [null] 0 0 [null] [null] 12 130932 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 44 v_mldb_node0004 3 4 StorageUnion 1 55 [null] [null] 0 0 [null] [null] [null] 8385604 [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] [null] 0 325 [null] [null] [null] [null] [null] [null] [null] Rows: 1-44 | Columns: 40Query 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()
Abcnode_name123path_idAbcactivityAbcabl_id123elaps_us123exec_usAbc123input_rows123input_mb123proc_rows123proc_mb1 v_mldb_node0002 1 Unused 1008,7,3 3 15 [null] [null] [null] [null] 2 v_mldb_node0002 1 TopK HEAP 1009,6,2 1 34 [null] [null] [null] [null] 3 v_mldb_node0002 1 Emit TopK 1010,6,2 [null] [null] [null] [null] [null] [null] 4 v_mldb_node0002 1 Send Data 1011,5,1 [null] [null] [null] [null] [null] [null] 5 v_mldb_node0002 2 Evaluate Expressions 1006,9,5 18 12 [null] [null] [null] [null] 6 v_mldb_node0002 3 Build Join Inner 1004,10,6 23 [null] [null] [null] [null] [null] 7 v_mldb_node0002 3 Join (hash) 1005,10,6 24 95 [null] [null] [null] [null] 8 v_mldb_node0002 3 Combine Storage Chunks 1007,8,4 [null] 58 0 [null] [null] [null] 9 v_mldb_node0002 3 reserveVTLoaderResources 1015,8,4 [null] [null] [null] [null] [null] [null] 10 v_mldb_node0002 4 Scan Projection 1003,11,7 25 14 [null] [null] 0 [null] 11 v_mldb_node0002 5 Scan Projection 1001,13,9 16 9 [null] [null] 0 [null] 12 v_mldb_node0002 5 Combine Storage Chunks 1002,12,8 [null] 67 0 [null] [null] [null] 13 v_mldb_node0002 5 reserveVTLoaderResources 1016,12,8 [null] [null] [null] [null] [null] [null] 14 v_mldb_node0004 1 Unused 1008,7,3 3 12 [null] [null] [null] [null] 15 v_mldb_node0004 1 TopK HEAP 1009,6,2 1 30 [null] [null] [null] [null] 16 v_mldb_node0004 1 Emit TopK 1010,6,2 [null] [null] [null] [null] [null] [null] 17 v_mldb_node0004 1 Send Data 1011,5,1 [null] [null] [null] [null] [null] [null] 18 v_mldb_node0004 2 Evaluate Expressions 1006,9,5 21 12 [null] [null] [null] [null] 19 v_mldb_node0004 3 Build Join Inner 1004,10,6 30 [null] [null] [null] [null] [null] 20 v_mldb_node0004 3 Join (hash) 1005,10,6 27 127 [null] [null] [null] [null] 21 v_mldb_node0004 3 Combine Storage Chunks 1007,8,4 [null] 55 0 [null] [null] [null] 22 v_mldb_node0004 3 reserveVTLoaderResources 1015,8,4 [null] [null] [null] [null] [null] [null] 23 v_mldb_node0004 4 Scan Projection 1003,11,7 27 14 [null] [null] 0 [null] 24 v_mldb_node0004 5 Scan Projection 1001,13,9 17 10 [null] [null] 0 [null] 25 v_mldb_node0004 5 Combine Storage Chunks 1002,12,8 [null] 64 0 [null] [null] [null] 26 v_mldb_node0004 5 reserveVTLoaderResources 1016,12,8 [null] [null] [null] [null] [null] [null] 27 v_mldb_node0001 -1 Send Data to Client 1000,0,0 496 95 [null] [null] [null] [null] 28 v_mldb_node0001 0 Unused 1013,3,3 477 40 [null] [null] [null] [null] 29 v_mldb_node0001 0 TopK 1014,2,2 475 56 [null] [null] [null] [null] 30 v_mldb_node0001 1 Unused 1008,7,7 1075785 112161 [null] [null] [null] [null] 31 v_mldb_node0001 1 TopK HEAP 1009,6,6 1075782 68538 [null] [null] [null] [null] 32 v_mldb_node0001 1 Emit TopK 1010,6,6 596 [null] [null] [null] [null] [null] 33 v_mldb_node0001 1 Send Data 1011,5,5 [null] [null] [null] [null] [null] [null] 34 v_mldb_node0001 1 Receive Data 1012,4,4 [null] [null] [null] [null] [null] [null] 35 v_mldb_node0001 2 Evaluate Expressions 1006,9,9 1075849 220365 [null] [null] [null] [null] 36 v_mldb_node0001 3 Build Join Inner 1004,10,10 1865488 [null] [null] [null] [null] [null] 37 v_mldb_node0001 3 Join (hash) 1005,10,10 1075989 1024494 [null] [null] [null] [null] 38 v_mldb_node0001 3 Combine Storage Chunks 1007,8,8 1075789 307978 0 [null] [null] [null] 39 v_mldb_node0001 3 reserveVTLoaderResources 1015,8,8 [null] [null] [null] [null] [null] [null] 40 v_mldb_node0001 4 Scan Projection 1003,11,11 1076032 5941 [null] [null] 35014 [null] 41 v_mldb_node0001 5 Scan Projection 1001,13,13 637519 183262 [null] [null] 694456 [null] 42 v_mldb_node0001 5 Combine Storage Chunks 1002,12,12 637452 377946 0 [null] [null] [null] 43 v_mldb_node0001 5 reserveVTLoaderResources 1016,12,12 [null] [null] [null] [null] [null] [null] 44 v_mldb_node0003 1 Unused 1008,7,3 6 31 [null] [null] [null] [null] 45 v_mldb_node0003 1 TopK HEAP 1009,6,2 3 76 [null] [null] [null] [null] 46 v_mldb_node0003 1 Emit TopK 1010,6,2 [null] [null] [null] [null] [null] [null] 47 v_mldb_node0003 1 Send Data 1011,5,1 [null] [null] [null] [null] [null] [null] 48 v_mldb_node0003 2 Evaluate Expressions 1006,9,5 45 31 [null] [null] [null] [null] 49 v_mldb_node0003 3 Build Join Inner 1004,10,6 53 [null] [null] [null] [null] [null] 50 v_mldb_node0003 3 Join (hash) 1005,10,6 59 236 [null] [null] [null] [null] 51 v_mldb_node0003 3 Combine Storage Chunks 1007,8,4 [null] 140 0 [null] [null] [null] 52 v_mldb_node0003 3 reserveVTLoaderResources 1015,8,4 [null] [null] [null] [null] [null] [null] 53 v_mldb_node0003 4 Scan Projection 1003,11,7 62 31 [null] [null] 0 [null] 54 v_mldb_node0003 5 Scan Projection 1001,13,9 40 23 [null] [null] 0 [null] 55 v_mldb_node0003 5 Combine Storage Chunks 1002,12,8 [null] 154 0 [null] [null] [null] 56 v_mldb_node0003 5 reserveVTLoaderResources 1016,12,8 [null] [null] [null] [null] [null] [null] Rows: 1-56 | Columns: 11Projection Data Distribution¶
To obtain the projection data distribution, utilize the following syntax:
qprof.get_proj_data_distrib()
Abctable_nameAbcprojection_nameAbcnode_name123row_count123used_bytes123ROS_count123del_rows123DV_countRows: 0 | Columns: 8Note
This report is really useful to check if the data are correctly segmented.
Node/Cluster Information¶
Nodes
To get node-wise performance information,
get_qexecutioncan 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()
Abchost_name123open_files_limit123threads_limit123core_file_limit_max_size_bytes123processor_count123processor_core_countAbc123opened_file_count123opened_socket_count123opened_nonfile_nonsocket_count123total_memory_bytes123total_memory_free_bytes123total_buffer_memory_bytes123total_memory_cache_bytes123total_swap_memory_bytes123total_swap_memory_free_bytes123disk_space_free_mb123disk_space_used_mb123disk_space_total_mb123system_open_files123system_max_files1 10.20.43.240 1048576 3094405 1612787712 2 72 6 13 18 811214127104 445606166528 3043733504 324146163712 2147479552 1375862784 2536002 1406052 3942054 1872 13051931 2 10.20.43.241 1048576 3094398 1611071488 2 72 6 13 22 811212410880 528219041792 3086884864 260933861376 2147479552 2089451520 2619974 1322080 3942054 1760 13051931 3 10.20.43.242 1048576 3094399 1611132928 2 72 6 11 18 811212472320 527806464000 3283681280 260932780032 2147479552 2120425472 2633335 1308719 3942054 1728 13051931 4 10.20.43.243 1048576 3094399 1611132928 2 72 6 11 18 811212472320 258251431936 3432087552 529823621120 2147479552 2112188416 2261459 1680595 3942054 1584 13051931 Rows: 1-4 | Columns: 21The Cluster Report can also be conveniently extracted:
qprof.get_rp_status()
Abcnode_name123pool_oidAbcpool_name010is_internal123memory_size_kb123memory_size_actual_kb123memory_inuse_kb123general_memory_borrowed_kb123queueing_threshold_kb123max_memory_size_kb123max_query_memory_size_kb123running_query_count123planned_concurrency123max_concurrency010is_standalone📅queue_timeout123queue_timeout_in_secondsAbcexecution_parallelismAbcthread_policy123priorityAbcruntime_priority123runtime_priority_threshold123runtimecap_in_secondsAbcsingle_initiator123query_budget_kbAbccpu_affinity_setAbccpu_affinity_maskAbccpu_affinity_mode1 v_mldb_node0001 45035996273705010 general ✅ 710505254 710505254 0 0 674980032 710505254 [null] 0 72 [null] ✅ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 9374723 ffffffffffffffffff ANY 2 v_mldb_node0001 45035996273705012 sysquery ✅ 1048576 1048576 78049 0 676211520 711801600 [null] 1 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 110 HIGH 0 [null] false 14563 ffffffffffffffffff ANY 3 v_mldb_node0001 45035996273705014 tm ✅ 38797312 38797312 0 0 712072832 749550336 [null] 0 7 7 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 105 MEDIUM 60 [null] true 5542473 ffffffffffffffffff ANY 4 v_mldb_node0001 45035996273705016 refresh ✅ 0 0 0 0 675215360 710753024 [null] 0 4 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM -10 MEDIUM 60 [null] true 168745008 ffffffffffffffffff ANY 5 v_mldb_node0001 45035996273705018 recovery ✅ 0 0 0 0 675215360 710753024 [null] 0 74 37 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 107 MEDIUM 60 [null] true 9121352 ffffffffffffffffff ANY 6 v_mldb_node0001 45035996273705020 dbd ✅ 0 0 0 0 675215360 710753024 [null] 0 4 [null] ❌ relativedelta() 0 AUTO SYSTEM 0 MEDIUM 0 [null] true 168745008 ffffffffffffffffff ANY 7 v_mldb_node0001 45035996273705098 jvm ✅ 0 0 0 0 1992294 2097152 [null] 0 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 27670 ffffffffffffffffff ANY 8 v_mldb_node0001 45035996273705108 blobdata ✅ 0 0 0 0 71306896 75059891 [null] 0 2 0 ❌ relativedelta() 0 AUTO SYSTEM 0 HIGH 0 [null] false [null] ffffffffffffffffff ANY 9 v_mldb_node0001 45035996273705110 metadata ✅ 247770 247770 0 0 675215360 710753024 [null] 0 1 0 ❌ relativedelta() 0 AUTO SYSTEM 108 HIGH 0 [null] false [null] ffffffffffffffffff ANY 10 v_mldb_node0002 45035996273705010 general ✅ 710100181 710100181 0 0 674595136 710100181 [null] 0 72 [null] ✅ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 9369377 ffffffffffffffffff ANY 11 v_mldb_node0002 45035996273705012 sysquery ✅ 1048576 1048576 56973 0 676209984 711800000 [null] 1 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 110 HIGH 0 [null] false 14563 ffffffffffffffffff ANY 12 v_mldb_node0002 45035996273705014 tm ✅ 38797312 38797312 0 0 712071296 749548736 [null] 0 7 7 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 105 MEDIUM 60 [null] true 5542473 ffffffffffffffffff ANY 13 v_mldb_node0002 45035996273705016 refresh ✅ 0 0 0 0 675213824 710751424 [null] 0 4 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM -10 MEDIUM 60 [null] true 168648784 ffffffffffffffffff ANY 14 v_mldb_node0002 45035996273705018 recovery ✅ 0 0 0 0 675213824 710751424 [null] 0 74 37 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 107 MEDIUM 60 [null] true 9116150 ffffffffffffffffff ANY 15 v_mldb_node0002 45035996273705020 dbd ✅ 0 0 0 0 675213824 710751424 [null] 0 4 [null] ❌ relativedelta() 0 AUTO SYSTEM 0 MEDIUM 0 [null] true 168648784 ffffffffffffffffff ANY 16 v_mldb_node0002 45035996273705098 jvm ✅ 0 0 0 0 1992294 2097152 [null] 0 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 27670 ffffffffffffffffff ANY 17 v_mldb_node0002 45035996273705108 blobdata ✅ 0 0 0 0 71306744 75059731 [null] 0 2 0 ❌ relativedelta() 0 AUTO SYSTEM 0 HIGH 0 [null] false [null] ffffffffffffffffff ANY 18 v_mldb_node0002 45035996273705110 metadata ✅ 651243 651243 0 0 675213824 710751424 [null] 0 1 0 ❌ relativedelta() 0 AUTO SYSTEM 108 HIGH 0 [null] false [null] ffffffffffffffffff ANY 19 v_mldb_node0004 45035996273705010 general ✅ 710101108 710101108 0 0 674596032 710101108 [null] 0 72 [null] ✅ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 9369389 ffffffffffffffffff ANY 20 v_mldb_node0004 45035996273705012 sysquery ✅ 1048576 1048576 56973 0 676210048 711800064 [null] 1 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 110 HIGH 0 [null] false 14563 ffffffffffffffffff ANY 21 v_mldb_node0004 45035996273705014 tm ✅ 38797312 38797312 0 0 712071360 749548800 [null] 0 7 7 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 105 MEDIUM 60 [null] true 5542473 ffffffffffffffffff ANY 22 v_mldb_node0004 45035996273705016 refresh ✅ 0 0 0 0 675213888 710751488 [null] 0 4 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM -10 MEDIUM 60 [null] true 168649008 ffffffffffffffffff ANY 23 v_mldb_node0004 45035996273705018 recovery ✅ 0 0 0 0 675213888 710751488 [null] 0 74 37 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 107 MEDIUM 60 [null] true 9116163 ffffffffffffffffff ANY 24 v_mldb_node0004 45035996273705020 dbd ✅ 0 0 0 0 675213888 710751488 [null] 0 4 [null] ❌ relativedelta() 0 AUTO SYSTEM 0 MEDIUM 0 [null] true 168649008 ffffffffffffffffff ANY 25 v_mldb_node0004 45035996273705098 jvm ✅ 0 0 0 0 1992294 2097152 [null] 0 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 27670 ffffffffffffffffff ANY 26 v_mldb_node0004 45035996273705108 blobdata ✅ 0 0 0 0 71306752 75059737 [null] 0 2 0 ❌ relativedelta() 0 AUTO SYSTEM 0 HIGH 0 [null] false [null] ffffffffffffffffff ANY 27 v_mldb_node0004 45035996273705110 metadata ✅ 650380 650380 0 0 675213888 710751488 [null] 0 1 0 ❌ relativedelta() 0 AUTO SYSTEM 108 HIGH 0 [null] false [null] ffffffffffffffffff ANY 28 v_mldb_node0003 45035996273705010 general ✅ 710100044 710100044 0 0 674595008 710100044 [null] 0 72 [null] ✅ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 9369375 ffffffffffffffffff ANY 29 v_mldb_node0003 45035996273705012 sysquery ✅ 1048576 1048576 56973 0 676210048 711800064 [null] 1 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 110 HIGH 0 [null] false 14563 ffffffffffffffffff ANY 30 v_mldb_node0003 45035996273705014 tm ✅ 38797312 38797312 0 0 712071360 749548800 [null] 0 7 7 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 105 MEDIUM 60 [null] true 5542473 ffffffffffffffffff ANY 31 v_mldb_node0003 45035996273705016 refresh ✅ 0 0 0 0 675213888 710751488 [null] 0 4 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM -10 MEDIUM 60 [null] true 168648752 ffffffffffffffffff ANY 32 v_mldb_node0003 45035996273705018 recovery ✅ 0 0 0 0 675213888 710751488 [null] 0 74 37 ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 107 MEDIUM 60 [null] true 9116149 ffffffffffffffffff ANY 33 v_mldb_node0003 45035996273705020 dbd ✅ 0 0 0 0 675213888 710751488 [null] 0 4 [null] ❌ relativedelta() 0 AUTO SYSTEM 0 MEDIUM 0 [null] true 168648752 ffffffffffffffffff ANY 34 v_mldb_node0003 45035996273705098 jvm ✅ 0 0 0 0 1992294 2097152 [null] 0 72 [null] ❌ relativedelta(minutes=+5) 300 AUTO SYSTEM 0 MEDIUM 2 [null] false 27670 ffffffffffffffffff ANY 35 v_mldb_node0003 45035996273705108 blobdata ✅ 0 0 0 0 71306752 75059737 [null] 0 2 0 ❌ relativedelta() 0 AUTO SYSTEM 0 HIGH 0 [null] false [null] ffffffffffffffffff ANY 36 v_mldb_node0003 45035996273705110 metadata ✅ 651444 651444 0 0 675213888 710751488 [null] 0 1 0 ❌ relativedelta() 0 AUTO SYSTEM 108 HIGH 0 [null] false [null] ffffffffffffffffff ANY Rows: 1-36 | Columns: 28Important
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, session_control: None | dict | list[dict] | str | list[str] = None, overwrite: bool = False, add_profile: bool = True, check_tables: bool = True, ignore_operators_check: bool = True, iterchecks: bool = False, print_info: bool = True, run_only_session: 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.Returns the a report including: Elapsed, exec_time and I/O by node, activity & path_id.
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([granularity, genSQL, ...])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
listof 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.
Returns all the queries and their respective information, of a
QueryProfilerobject.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.
Returns the RP status.
get_table([table_name])Returns the associated Vertica Table.
Returns the current Vertica version.
import_profile(target_schema, key_id, filename)The static method
import_profilecan be used to create newQueryProfilerobject 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
QueryProfilerstack.previous()A utility function to utilize the previous transaction from the
QueryProfilerstack.set_position(idx)A utility function to utilize a specific transaction from the
QueryProfilerstack.step(idx, *args, **kwargs)Function to return the QueryProfiler Step.
to_html([path])Creates an HTML report.