Downloads

Blog

Improve PostgreSQL Query Performance Insights with pg_stat_monitor

July 30, 2021

Author

Share this Post:

Understanding query performance patterns is essentially the foundation for query performance tuning. It, in many ways, dictates how a database cluster evolves. And then there are obviously direct and indirect cost connotations as well.

PostgreSQL provides very detailed statistics through a number of catalog views and extensions that can be easily added to provide more detailed query statistics. With each view focused on a particular aspect, the picture almost always needs to be stitched together by combining different datasets. That requires effort and still, the whole picture might not be complete.

The pg_stat_monitor extension attempts to provide a more holistic picture by providing much-needed query performance insights in a single view. The extension has been evolving over the past year and is now nearing the GA release.

Some Useful Extensions

Currently, you may be relying on a number of extensions to understand how a query behaves, the time taken in planning and execution phases, min/max/meantime values, index hits, query plan, and client application details. Here are some extensions that you might already be very familiar with.

pg_stat_activity

This view is available by default with PostgreSQL. It provides one row per server process along with current activity and query text.

In case you’d like to learn more about it, hop over to the official PostgreSQL documentation here.

pg_stat_statements

This extension is part of the contrib packages provided with the PostgreSQL server. However, you’d have to create the extension manually. It’s a query-wise aggregation of statistical data with min/max/mean/standard deviation for execution and planning times and various useful information and query text.

You can read more about pg_stat_statements at the official PostgreSQL documentation site.

auto_explain

Another useful extension is provided by the PostgreSQL server. It dumps query plans in the server log for any query exceeding a time threshold specified by a GUC

(Grand Unified Configuration).

You can find more about auto_explain here.

pg_stat_monitor

Whilst all previously mentioned views/extensions are great in their own right, one needs to manually combine client/connection information from pg_stat_activity, statistical data from pg_stat_statements, and query plan from auto_analyze to complete the dataset to understand query performance patterns

And that’s precisely the pain that pg_stat_monitor alleviates.

The feature set has been growing over the past year, with it providing, in a single view, all performance-related information that you may need to debug a low performant query. For more information about the extension see our GitHub repository, or for user-specific documentation, see our user guide.

Ensure your databases are performing their best — today and tomorrow — with proactive database optimization and query tuning. Book a database assessment

Feature Set

Some features that were part of earlier releases are already discussed in this blog, however, for completeness, I’m going to discuss those here as well.

Time Interval Grouping: Instead of supplying one set of ever-increasing counts, pg_stat_monitor computes stats for a configured number of time intervals; time buckets. This allows for much better data accuracy, especially in the case of high resolution or unreliable networks.

Multi-Dimensional Grouping: While pg_stat_statements groups counters by (userid, dbid, queryid), pg_stat_monitor uses a more detailed group for higher precision:

- Bucket ID (bucket),

- User ID (userid),

- Database ID (dbid),

- Query ID (queryid),

- Client IP Address (client_ip),

- Plan ID (planid),

- Application Name (application_name).

This allows you to drill down into the performance of queries coming from particular client addresses and applications, which we at Percona have found to be very valuable in a number of cases.

Capture Actual Parameters in the Queries: pg_stat_monitor allows you to choose if you want to see queries with placeholders for parameters or actual query examples.

Query Plan: Each SQL is now accompanied by its actual plan that was constructed for its execution. Also, we found having query parameter values is very helpful, as you can run EXPLAIN on it, or easily play with modifying the query to make it run better, as well as making communication about the query clearer when discussing with other DBAs and application developers.

Tables Access Statistics for a Statement: This allows us to easily identify all queries that accessed a given table. This set is at par with the information provided by the pg_stat_statements.

Histogram: Visual representation is very helpful when it can help identify issues. With the help of the histogram function, you can now view a timing/calling data histogram in response to a SQL query. And yes, it even works in psql.

SELECT * FROM histogram(0, 'F44CD1B4B33A47AF') AS a(range TEXT, freq INT, bar TEXT);<br>       range        | freq |              bar<br>--------------------+------+--------------------------------<br>  (0 - 3)}          |    2 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■<br>  (3 - 10)}         |    0 |<br>  (10 - 31)}        |    1 | ■■■■■■■■■■■■■■■<br>  (31 - 100)}       |    0 |<br>  (100 - 316)}      |    0 |<br>  (316 - 1000)}     |    0 |<br>  (1000 - 3162)}    |    0 |<br>  (3162 - 10000)}   |    0 |<br>  (10000 - 31622)}  |    0 |<br>  (31622 - 100000)} |    0 |<br>(10 rows)

SELECT * FROM histogram(0, 'F44CD1B4B33A47AF') AS a(range TEXT, freq INT, bar TEXT); range | freq | bar --------------------+------+-------------------------------- (0 - 3)} | 2 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ (3 - 10)} | 0 | (10 - 31)} | 1 | ■■■■■■■■■■■■■■■ (31 - 100)} | 0 | (100 - 316)} | 0 | (316 - 1000)} | 0 | (1000 - 3162)} | 0 | (3162 - 10000)} | 0 | (10000 - 31622)} | 0 | (31622 - 100000)} | 0 | (10 rows)

Functions: It may come as a surprise, but we do understand that functions may internally execute statements!!! To help ease the tracking and analysis, pg_stat_monitor now provides a column that specifically helps keep track of the top query for a statement so that you can backtrack to the originating function.

Relation Names: Relations used in a query are available in the “relations” column in the pg_stat_monitor view. This reduces work at your and makes analysis simpler and quicker.

Query Types: With query classification as SELECT, INSERT, UPDATE or DELETE, analysis becomes simpler. It’s another effort reduced at your end, and another simplification by pg_stat_monitor.

SELECT bucket, substr(query,0, 50) AS query, cmd_type FROM pg_stat_monitor WHERE elevel = 0;<br> bucket |                       query                       | cmd_type <br>--------+---------------------------------------------------+----------<br>      4 | END                                               | <br>      4 | SELECT abalance FROM pgbench_accounts WHERE aid = | SELECT<br>      4 | vacuum pgbench_branches                           | <br>      4 | select count(*) from pgbench_branches             | SELECT<br>      4 | UPDATE pgbench_accounts SET abalance = abalance + | UPDATE<br>      4 | truncate pgbench_history                          | <br>      4 | INSERT INTO pgbench_history (tid, bid, aid, delta | INSERT

Query Metadata: Google’s Sqlcommenter is a useful tool that in a way bridges that gap between ORM libraries and understanding database performance. And we support it. So, you can now put any key value data in the comments in /* … */ syntax (see Sqlcommenter documentation for details) in your SQL statements, and the information will be parsed by pg_stat_monitor and made available in the comments column in pg_stat_monitor view.

CREATE EXTENSION hstore;<br>CREATE FUNCTION text_to_hstore(s text) RETURNS hstore AS $$<br>BEGIN<br>    RETURN hstore(s::text[]);<br>EXCEPTION WHEN OTHERS THEN<br>    RETURN NULL;<br>END; $$ LANGUAGE plpgsql STRICT;<br><br><br>SELECT 1 AS num /* { "application", java_app, "real_ip", 192.168.1.1} */;<br> num <br>-----<br>   1<br>(1 row)

CREATE EXTENSION hstore; CREATE FUNCTION text_to_hstore(s text) RETURNS hstore AS $$ BEGIN RETURN hstore(s::text[]); EXCEPTION WHEN OTHERS THEN RETURN NULL; END; $$ LANGUAGE plpgsql STRICT; SELECT 1 AS num /* { "application", java_app, "real_ip", 192.168.1.1} */; num ----- 1 (1 row)

SELECT query, text_to_hstore(comments)->'real_ip' AS real_ip from pg_stat_monitor;<br>query                                                                       |  real_ip <br>----------------------------------------------------------------------------+-------------<br> SELECT $1 AS num /* { "application", psql_app, "real_ip", 192.168.1.3) */  | 192.168.1.1

1	SELECT query, text_to_hstore(comments)->'real_ip' AS real_ip from pg_stat_monitor;<br>query \| real_ip <br>----------------------------------------------------------------------------+-------------<br> SELECT $1 AS num /* { "application", psql_app, "real_ip", 192.168.1.3) */ \| 192.168.1.1

Logging Error and Warning: As seen in different monitoring/statics collector tools, most of the tools/extensions only monitor the successful queries. But in many cases, monitoring ERROR, WARNING, and LOG give meaningful information to debug the issue. pg_stat_monitor not only monitors the ERROR/WARNINGS/LOG but also collects the statistics about these queries. In PostgreSQL queries with ERROR/WARNING there is an error level (elevel), SQL Code (sqlcode), and an error message is attached. Pg_stat_monitor collects all this information along with its aggregates.

SELECT substr(query,0,50) AS query, decode_error_level(elevel) AS elevel,sqlcode, calls, substr(message,0,50) message <br>FROM pg_stat_monitor;<br>                       query                       | elevel | sqlcode | calls |                      message                      <br>---------------------------------------------------+--------+---------+-------+---------------------------------------------------<br> select substr(query,$1,$2) as query, decode_error |        |       0 |     1 | <br> select bucket,substr(query,$1,$2),decode_error_le |        |       0 |     3 | <br> select 1/0;                                       | ERROR  |     130 |     1 | division by zero

We’ve Come a Long Way

What started as a concept is now nearing its final approach. The pg_stat_monitor extension has evolved and has become very feature-rich. We have no doubt about its usefulness for DBAs, performance engineers, application developers, and anyone who needs to look at query performance. We believe it can help save many hours and help identify unexpected query behaviors.

pg_stat_monitor is available on Github. We are releasing it to get feedback from the community on what we’re doing right and what we should do differently before we release pg_stat_monitor as a generally available version to be supported for years to come. Please check it out, drop us a note, file an issue, or make a pull request!

Our PostgreSQL Performance Tuning eBook condenses years of database expertise into a practical guide for optimizing your PostgreSQL databases. Inside, you’ll discover our most effective PostgreSQL performance strategies derived from real-world experience.

Download now: Elevate your PostgreSQL Performance