QUERY_PLAN_PROFILES

Provides detailed execution status for queries that are currently running in the system. Output from the table shows the real-time flow of data and the time and resources consumed for each path in each query plan.

Column Name Data Type Description 
TRANSACTION_ID

INTEGER

An identifier for the transaction within the session if any; otherwise NULL.

 
STATEMENT_ID

INTEGER

Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.

 
PATH_ID

INTEGER

Unique identifier that Vertica assigns to a query operation or path in a query plan. Textual representation for this path is output in the PATH_LINE column. 

PATH_LINE_INDEX

INTEGER

Each plan path in QUERY_PLAN_PROFILES could be represented with multiple rows. PATH_LINE_INDEX returns the relative line order. You should include the PATH_LINE_INDEX column in the QUERY_PLAN_PROFILES ... ORDER BY clause so rows in the result set appear as they do in EXPLAIN-generated query plans. 

PATH_IS_EXECUTING

BOOLEAN

Status of a path in the query plan. True (t) if the path has started running, otherwise false.

 
PATH_IS_COMPLETE

BOOLEAN

Status of a path in the query plan. True (t) if the path has finished running, otherwise false.

 
IS_EXECUTING

BOOLEAN

Status of a running query. True if the query is currently active (t), otherwise false (f).

 
RUNNING_TIME

INTERVAL

The amount of elapsed time the query path took to execute.

 
MEMORY_ALLOCATED_BYTES

INTEGER

The amount of memory the path used, in bytes.

 
READ_FROM_DISK_BYTES

INTEGER

The number of bytes the path read from disk (or the disk cache).

 
RECEIVED_BYTES

INTEGER

The number of bytes received over the network.

 
SENT_BYTES

INTEGER

Size of data sent over the network by the path.

 
PATH_LINE

VARCHAR

The query plan text string for the path, associated with the PATH ID and PATH_LINE_INDEX columns.

Privileges

Non-superusers see only the records of tables they have permissions to view.

Best Practices

Table results can be very wide. For best results when you query QUERY_PLAN_PROFILES, sort on these columns:

  • TRANSACTION_ID
  • STATEMENT_ID
  • PATH_ID
  • PATH_LINE_INDEX

For example:

=> SELECT ... FROM query_plan_profiles
     WHERE ...
    ORDER BY transaction_id, statement_id, path_id, path_line_index;

Example

See Profiling Query Plans in the Administrator's Guide

See Also