Getting Query Plan Status for Large Queries

Real-time profiling is designed to monitor large (long-running) queries. Take the following steps to monitor plans for large queries:

  1. Get the statement and transaction IDs for the query plan you want to profile by querying the CURRENT_SESSION system table:

    => SELECT transaction_id, statement_id from current_session;
      transaction_id   | statement_id
    -------------------+--------------
     45035996273955001 |            4
    (1 row)
    
  2. Run the query:

    => SELECT * FROM t1 JOIN t2 ON x=y JOIN ext on y=z;
  3. Query the QUERY_PLAN_PROFILES system table, and sort on the transaction_id, statement_id, path_id, and path_line_index columns.

    => SELECT ... FROM query_plan_profilesWHERE transaction_id=45035996273955001 and statement_id=4
       ORDER BY transaction_id, statement_id, path_id, path_line_index;
    

You can also use the Linux watch command to monitor long-running queries (see Real-Time Profiling).

Example

The following series of commands creates a table for a long-running query and then runs the QUERY_PLAN_PROFILES system table:

=> CREATE TABLE longq(x int);
CREATE TABLE
=> COPY longq FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>> 10
>> \.
=> INSERT INTO longq SELECT f1.x+f2.x+f3.x+f4.x+f5.x+f6.x+f7.x 
      FROM longq f1 
      CROSS JOIN longq f2 
      CROSS JOIN longq f3 
      CROSS JOIN longq f4 
      CROSS JOIN longq f5 
      CROSS JOIN longq f6
      CROSS JOIN longq f7;
  OUTPUT  
----------
 10000000
(1 row)
=> COMMIT;
COMMIT

Suppress query output on the terminal window by using the vsql \o command:

=> \o /home/dbadmin/longQprof

Query the new table:

=> SELECT * FROM longq;

Get the transaction and statement IDs:

=> SELECT transaction_id, statement_id from current_session;
  transaction_id   | statement_id
-------------------+--------------
 45035996273955021 |            4
(1 row)

Turn off the \o command so that Vertica continues to save query plan information to the file you specified. Alternatively, leave it on and examine the file after you query the QUERY_PLAN_PROFILES system table.

=> \o

Query the QUERY_PLAN_PROFILES system table;

=> SELECT
     transaction_id,
     statement_id,
     path_id,
     path_line_index,
     is_executing,
     running_time,
     path_line 
   FROM query_plan_profiles 
   WHERE transaction_id=45035996273955021 AND statement_id=4 
   ORDER BY transaction_id, statement_id, path_id, path_line_index;