Getting Query Plan Status for Small Queries

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

Profiling counters are available after query execution has completed if any of the following conditions are true:

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

Here's an example:

  1. Profile the query to get the 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 the QUERY_PLAN_PROFILES system table.

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

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