Profiling Long Queries

This feature allows you to track the query execution status without waiting for it to finish. Note that enabling profiling brings a certain amount of cost for all backend queries.

Preparation

To enable profiling, you must set query_profiling parameter in the settings (either in the configuration file, or dynamically using SET command).

To change the size of the internal plan cache queue, there is a parameter query_profiling_queue_size. The minimum size is 8MB.

Setting example:

query_profiling_queue_size = 32MB

Profiling also requires a functioning message queue, in particular, SELECT start_mq_broker(); call. Then, all interaction occurs with system queues that are already created.

Information analysis is very sensitive to static information supported by the DBMS, Therefore, it is recommended to use the ANALYZE command for the tables to be examined.

Information Analysis

The module provides 3 main functions for obtaining information about the query execution:

  • qhb_running_queries
  • qhb_dump_qep
  • qhb_wait_qep

qhb_running_queries returns a temporary table that contains the following information:

Column NameTypeDescriptionComment
query_idint8Unique query identifier
backend_pidint8PID of the postgres backend on which the query is executedThe same as the value from pg_stat_activity.pid
query_stringtextQuery text string
query_startedtimestamptzQuery start time
ETCtimestamptzEstimated completion timeMay be NULL, with complex queries the estimate may be far from reality
elapsed_counterfloat8Internal counter to track completed query work
estimated_total_counterfloat8Internal expected amount of query work

qhb_dump_qep takes the query ID from qhb_running_queries and displays the query plan tree. The second argument is a string with the formatting method:

  • 'full' displays complete counter values;
  • 'scaled' displays counter values resolved to the one order (displayed in the units field);
  • 'scientific' displays counter values;
  • 'percent' displays counter values ​​in percentage;
  • 'mixed_percent' displays counter values ​​in percentage and absolute expected value;

The function returns a plan tree that is the same as the one returned by EXPLAIN, as well as the execution status for each node. Note that nodes can have progress of 100%, but still be called again, increasing the cycle counter. Example of query analysis:

# select start_mq_broker();
 start_mq_broker
-----------------

(1 row)

# create table tb (x int);
CREATE TABLE
# insert into tb select generate_series(1, 50000);
INSERT 0 50000
# select a.x, count(*) from tb a, tb b group by a.x;

In another client:

# select * from qhb_running_queries();
 query_id | backend_pid |                    query_string                    |         query_started         |             ETC              | elapsed_counter  | estimated_total_counter
----------+-------------+----------------------------------------------------+-------------------------------+------------------------------+------------------+-------------------------
        6 |        2475 | select a.x, count(*) from tb a, tb b group by a.x; | 2025-07-09 18:48:23.397635+03 | 2025-07-18 15:26:46.20484+03 | 4846879.43268446 |       99537037.55000001
(1 row)

# select qhb_dump_qep(query_id, 'percent') from qhb_running_queries() where query_string like 'select a.x%';
                         qhb_dump_qep
---------------------------------------------------------------
 (0,"~  Agg",N/A," (progress: 0.00%, loops:  0)",0)
 (1,"~  Nest Loop",N/A," (progress: 5.28%, loops:  0)",0)
 (2,"~  Seq Scan"," on tb"," (progress: 5.98%, loops:  0)",1)
 (3,"-> Material",N/A," (progress: 88.32%, loops: 3384)",1)
 (4,"~  Seq Scan"," on tb"," (progress: 88.32%, loops:  0)",3)
(5 rows)

The qhb_wait_qep function has similar logic, but is designed to wait for a query that was executed earlier. The function takes the query ID obtained from the qhb_sql_plans_info view, plan cache. In addition, the second argument must specify the PID of the backend on which the query should start to be executed. The third argument, similar to qhb_dump_qep, is a string with the formatting method. The query timeout is limited to 30 seconds.

Example:

# select queryid, query from qhb_sql_plans_info;
       queryid        |                        query
----------------------+------------------------------------------------------
  5039774379276303448 | select a.x, count(*) from tb a, tb b group by a.x;


# select qhb_wait_qep(5039774379276303448, 1932, 'scaled');
                                qhb_wait_qep
----------------------------------------------------------------------------
 ("~  Agg",N/A," (progress: 0.00 / 56083831.48, loops:  0, units: 0)")
 ("-> Nest Loop",N/A," (progress: 2.62 / 400.60, loops:  0, units: 5)")
 ("~  Seq Scan"," on tb"," (progress: 5.85 / 788.10, loops:  0, units: 0)")
 ("~  Material",N/A," (progress: 3.96 / 4.50, loops: 419, units: 5)")
 ("~  Seq Scan"," on tb"," (progress: 6.96 / 7.88, loops:  0, units: 2)")

Limitations

The implementation in its current form has limitations:

  • The ETC value is predicted by interpolation from just two points in time, which means that this value can vary widely over time.
  • To analyze the query work, cost estimates of the plan tree nodes are used, received from the scheduler. These values ​​may not be accurate and are not adjusted during the query execution.

Copyright © KVANTOM LLC, 2019-2026