Getting query plan status for large queries

Real-time profiling is designed to monitor large (long-running) queries.

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

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

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

Run the query:

=> SELECT * FROM t1 JOIN t2 ON x=y JOIN ext on y=z;

Query system table QUERY_PLAN_PROFILES, and sort on the transaction_id, statement_id, path_id, and path_line_index columns.

=> SELECT ... FROM query_plan_profiles WHERE 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 queries system table QUERY_PLAN_PROFILES:

Create table longq:

=> 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 Vertica continues to save query plan information to the file you specified. Alternatively, leave it on and examine the file after you query system table QUERY_PLAN_PROFILES.
```
=> \o
```

Query system table QUERY_PLAN_PROFILES:

=> 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;