| F.44. pg_query_state | ||||
|---|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules and Extensions Shipped inpostgrespro-std-15-contrib | Home | Next |
F.44. pg_query_state
Thepg_query_state module provides facility to know the current state of query execution on working backend.
F.44.1. Overview
Each non-utility query statement (SELECT/INSERT/UPDATE/DELETE) after optimization/planning stage is translated into plan tree, which is kind of imperative representation of declarative SQL query. EXPLAIN ANALYZE request allows to demonstrate execution statistics gathered from each node of plan tree (full time of execution, number of rows emitted to upper nodes, etc). But this statistics is collected after execution of query. This module allows to show actual statistics of query running on external backend. At that, format of resulting output is almost identical to ordinal EXPLAIN ANALYZE. Thus users are able to track of query execution in progress. In fact, this module is able to explore external backend and determine its actual state. Particularly it's helpful when backend executes a heavy query or gets stuck.
F.44.2. Use cases
Using this module there can help in the following things:
detect a long query (along with other monitoring tools);
supervise query execution.
F.44.3. Installation
To installpg_query_state, complete the following steps:
Add the library name to the
shared_preload_librariesparameter in thepostgresql.conffile as follows:shared_preload_libraries = 'pg_query_state'
Reload the database server for the changes to take effect.
Create thepg_query_state extension using the following query:
CREATE EXTENSION pg_query_state;
If you need to upgrade thepg_query_state extension, use
ALTER EXTENSION UPDATE.
F.44.4. Functions
F.44.4.1. pg_query_state
pg_query_state (integer pid, verbose boolean DEFAULT FALSE, costs boolean DEFAULT FALSE, timing boolean DEFAULT FALSE, buffers boolean DEFAULT FALSE, triggers boolean DEFAULT FALSE, format text DEFAULT 'text') returns TABLE (pid integer, frame_number integer, query_text text, plan text, leader_pid integer)
Extract current query state from backend with specifiedpid. Since parallel query can spawn multiple workers and function call causes nested subqueries so that state of execution may be viewed as stack of running queries, return value of pg_query_state has typeTABLE (pid integer, frame_number integer, query_text text, plan text, leader_pid integer). It represents tree structure consisting of leader process and its spawned workers identified bypid. Each worker refers to leader throughleader_pid column. For leader process the value of this column isnull. The state of each process is represented as stack of function calls. Each frame of that stack is specified as correspondence betweenframe_number starting from zero,query_text andplan with online statistics columns.
Thus, user can see the states of main query and queries generated from function calls for leader process and all workers spawned from it.
In process of execution some nodes of plan tree can take loops of full execution. Therefore statistics for each node consists of two parts: average statistics for previous loops just like in EXPLAIN ANALYZE output and statistics for current loop if node have not finished.
Optional arguments:
verbose - use EXPLAIN VERBOSE for plan printing;
costs - costs for each node;
timing - print timing data for each node, if collecting of timing statistics is turned off on called side resulting output will contain WARNING message
timing statistics disabled;buffers - print buffers usage, if collecting of buffers statistics is turned off on called side resulting output will contain WARNING message
buffers statistics disabled;triggers - include triggers statistics in result plan trees;
format - EXPLAIN format to be used for plans printing, possible values: text, xml, json, yaml.
If callable backend is not executing any query the function prints INFO message about backend's state taken frompg_stat_activity view if it exists there.
Calling role have to be superuser or member of the role whose backend is being called. Otherwise function prints ERROR messagepermission denied.
F.44.4.2. pg_progress_bar
pg_progress_bar (integer pid) returns FLOAT
Returns the current progress of query execution. This function extracts the state of the current query from the backend process with the specifiedpid. Then, it collects the actual and expected row counts from all plan nodes, and computes the overall progress ratio for the entire execution tree. As a result, the function returns a numeric value from0 to1 that means the query execution progress.
This function can be used in thePostgres Pro GUI to display the graphical progress bar indicator.
If there is no information about the current query state or it is impossible to calculate the query execution progress, the corresponding message is displayed.
This function can be called only by members of the role that owns the backend process or superusers.
F.44.4.3. pg_progress_bar_visual
pg_progress_bar_visual (integer pid, integer delay) returns VOID
Cyclically calculates the current progress of query execution and prints it in the text form. The function extracts the state of the current query from the backend process with the specifiedpid with the interval defined in thedelay parameter (in seconds).
This function is a cyclic version of thepg_progress_bar function but returnsvoid. Unlikepg_progress_bar, thepg_progress_bar_visual function can be used to track the query execution progress without using the GUI.
If there is no information about the current query state or it is impossible to calculate the query execution progress, the corresponding message is displayed.
This function can be called only by members of the role that owns the backend process or superusers.
F.44.5. Configuration settings
There are several user-accessible GUC variables designed to toggle the whole module and the collecting of specific statistic parameters while query is running:
pg_query_state.enable - disable (or enable) pg_query_state completely, default value is
truepg_query_state.enable_timing - collect timing data for each node, default value is
falsepg_query_state.enable_buffers - collect buffers usage, default value is
false
These parameters are set on called side before running any queries whose states are attempted to extract.WARNING: if pg_query_state.enable_timing is turned off the calling side cannot get time statistics, similarly for pg_query_state.enable_buffers parameter.
F.44.6. Usage Examples
Set maximum number of parallel workers ongather node equals 2:
postgres=# set max_parallel_workers_per_gather = 2;
Assume one backend withpid = 49265 performs a simple query:
postgres=# select pg_backend_pid();pg_backend_pid---------------- 49265(1 row)postgres=# select count(*) from foo join bar on foo.c1=bar.c1;
Other backend can extract intermediate state of execution that query:
postgres=# \xpostgres=# select * from pg_query_state(49265);-[ RECORD 1 ]+-------------------------------------------------------------------------------------------------------------------------pid | 49265frame_number | 0query_text | select count(*) from foo join bar on foo.c1=bar.c1;plan | Finalize Aggregate (Current loop: actual rows=0, loop number=1) + | -> Gather (Current loop: actual rows=0, loop number=1) + | Workers Planned: 2 + | Workers Launched: 2 + | -> Partial Aggregate (Current loop: actual rows=0, loop number=1) + | -> Nested Loop (Current loop: actual rows=12, loop number=1) + | Join Filter: (foo.c1 = bar.c1) + | Rows Removed by Join Filter: 5673232 + | -> Parallel Seq Scan on foo (Current loop: actual rows=12, loop number=1) + | -> Seq Scan on bar (actual rows=500000 loops=11) (Current loop: actual rows=173244, loop number=12)leader_pid | (null)-[ RECORD 2 ]+-------------------------------------------------------------------------------------------------------------------------pid | 49324frame_number | 0query_text | <parallel query>plan | Partial Aggregate (Current loop: actual rows=0, loop number=1) + | -> Nested Loop (Current loop: actual rows=10, loop number=1) + | Join Filter: (foo.c1 = bar.c1) + | Rows Removed by Join Filter: 4896779 + | -> Parallel Seq Scan on foo (Current loop: actual rows=10, loop number=1) + | -> Seq Scan on bar (actual rows=500000 loops=9) (Current loop: actual rows=396789, loop number=10)leader_pid | 49265-[ RECORD 3 ]+-------------------------------------------------------------------------------------------------------------------------pid | 49323frame_number | 0query_text | <parallel query>plan | Partial Aggregate (Current loop: actual rows=0, loop number=1) + | -> Nested Loop (Current loop: actual rows=11, loop number=1) + | Join Filter: (foo.c1 = bar.c1) + | Rows Removed by Join Filter: 5268783 + | -> Parallel Seq Scan on foo (Current loop: actual rows=11, loop number=1) + | -> Seq Scan on bar (actual rows=500000 loops=10) (Current loop: actual rows=268794, loop number=11)leader_pid | 49265
In example above working backend spawns two parallel workers with pids 49324 and 49323. Theirleader_pid column's values clarify that these workers belong to the main backend.Seq Scan node has statistics on passed loops (average number of rows delivered toNested Loop and number of passed loops are shown) and statistics on current loop. Other nodes has statistics only for current loop as this loop is first (loop number = 1).
Assume first backend executes some function:
postgres=# select n_join_foo_bar();
Other backend can get the follow output:
postgres=# select * from pg_query_state(49265);-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------pid | 49265frame_number | 0query_text | select n_join_foo_bar();plan | Result (Current loop: actual rows=0, loop number=1)leader_pid | (null)-[ RECORD 2 ]+------------------------------------------------------------------------------------------------------------------pid | 49265frame_number | 1query_text | SELECT (select count(*) from foo join bar on foo.c1=bar.c1)plan | Result (Current loop: actual rows=0, loop number=1) + | InitPlan 1 (returns $0) + | -> Aggregate (Current loop: actual rows=0, loop number=1) + | -> Nested Loop (Current loop: actual rows=51, loop number=1) + | Join Filter: (foo.c1 = bar.c1) + | Rows Removed by Join Filter: 51636304 + | -> Seq Scan on bar (Current loop: actual rows=52, loop number=1) + | -> Materialize (actual rows=1000000 loops=51) (Current loop: actual rows=636355, loop number=52)+ | -> Seq Scan on foo (Current loop: actual rows=1000000, loop number=1)leader_pid | (null)
First row corresponds to function call, second - to query which is in the body of that function.
We can get result plans in different format (e.g. json):
postgres=# select * from pg_query_state(pid := 49265, format := 'json');-[ RECORD 1 ]+------------------------------------------------------------pid | 49265frame_number | 0query_text | select * from n_join_foo_bar();plan | { + | "Plan": { + | "Node Type": "Function Scan", + | "Parallel Aware": false, + | "Function Name": "n_join_foo_bar", + | "Alias": "n_join_foo_bar", + | "Current loop": { + | "Actual Loop Number": 1, + | "Actual Rows": 0 + | } + | } + | }leader_pid | (null)-[ RECORD 2 ]+------------------------------------------------------------pid | 49265frame_number | 1query_text | SELECT (select count(*) from foo join bar on foo.c1=bar.c1)plan | { + | "Plan": { + | "Node Type": "Result", + | "Parallel Aware": false, + | "Current loop": { + | "Actual Loop Number": 1, + | "Actual Rows": 0 + | }, + | "Plans": [ + | { + | "Node Type": "Aggregate", + | "Strategy": "Plain", + | "Partial Mode": "Simple", + | "Parent Relationship": "InitPlan", + | "Subplan Name": "InitPlan 1 (returns $0)", + | "Parallel Aware": false, + | "Current loop": { + | "Actual Loop Number": 1, + | "Actual Rows": 0 + | }, + | "Plans": [ + | { + | "Node Type": "Nested Loop", + | "Parent Relationship": "Outer", + | "Parallel Aware": false, + | "Join Type": "Inner", + | "Current loop": { + | "Actual Loop Number": 1, + | "Actual Rows": 610 + | }, + | "Join Filter": "(foo.c1 = bar.c1)", + | "Rows Removed by Join Filter": 610072944, + | "Plans": [ + | { + | "Node Type": "Seq Scan", + | "Parent Relationship": "Outer", + | "Parallel Aware": false, + | "Relation Name": "bar", + | "Alias": "bar", + | "Current loop": { + | "Actual Loop Number": 1, + | "Actual Rows": 611 + | } + | }, + | { + | "Node Type": "Materialize", + | "Parent Relationship": "Inner", + | "Parallel Aware": false, + | "Actual Rows": 1000000, + | "Actual Loops": 610, + | "Current loop": { + | "Actual Loop Number": 611, + | "Actual Rows": 73554 + | }, + | "Plans": [ + | { + | "Node Type": "Seq Scan", + | "Parent Relationship": "Outer", + | "Parallel Aware": false, + | "Relation Name": "foo", + | "Alias": "foo", + | "Current loop": { + | "Actual Loop Number": 1, + | "Actual Rows": 1000000 + | } + | } + | ] + | } + | ] + | } + | ] + | } + | ] + | } + | }leader_pid | (null)F.44.7. Progress Bar Usage Examples
The first backend process executes the following sample query:
postgres=# insert into table_name select generate_series(1,10000000);
Another backend process can get the execution process of the query above as follows:
postgres=# SELECT pid FROM pg_stat_activity where query like 'insert%'; pid------- 23877(1 row)postgres=# SELECT pg_progress_bar(23877); pg_progress_bar----------------- 0.6087927(1 row)
To display a visual representation of the query execution progress, run the following query:
postgres=# SELECT pg_progress_bar_visual(23877, 1);Progress = 0.043510Progress = 0.085242Progress = 0.124921Progress = 0.168168Progress = 0.213803Progress = 0.250362Progress = 0.292632Progress = 0.331454Progress = 0.367509Progress = 0.407450Progress = 0.448646Progress = 0.488171Progress = 0.530559Progress = 0.565558Progress = 0.608039Progress = 0.645778Progress = 0.654842Progress = 0.699006Progress = 0.735760Progress = 0.787641Progress = 0.832160Progress = 0.871077Progress = 0.911858Progress = 0.956362Progress = 0.995097Progress = 1.000000 pg_progress_bar_visual------------------------ 1(1 row)