Getting query plan status for small queries

Real-time profiling counters, stored in system table EXECUTION_ENGINE_PROFILES, are available for all currently executing statements, including internal operations, such as a mergeout.

Profiling counters are available after query execution completes, if any one of the following conditions is true:

• The query was run via the PROFILE command

• Systemwide profiling is enabled by meta-function ENABLE_PROFILING.

• The query ran more than two seconds.

Profiling counters are saved in system table EXECUTION_ENGINE_PROFILES until the storage quota is exceeded.

For example:

1. Profile the query to get transaction_id and statement_id from from EXECUTION_ENGINE_PROFILES. For example:

   => PROFILE SELECT * FROM t1 JOIN t2 ON t1.x = t2.y;
   NOTICE 4788:  Statement is being profiled
   HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273955065 and statement_id=4;
   NOTICE 3557:  Initiator memory for query: [on pool general: 248544 KB, minimum: 248544 KB]
   NOTICE 5077:  Total memory required by query: [248544 KB]
    x | y |   z
   ---+---+-------
    3 | 3 | three
   (1 row)
   

2. Query system table QUERY_PLAN_PROFILES.

   Note

   For best results, sort on columns transaction_id, statement_id, path_id, and path_line_index.

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