Working With Queries

With pgDash, you can examine how your SQL queries are performing, identify reasons for slow queries and track query stats over time.

Setting Up

pgDash relies on the popular extension pg_stat_statements to collect query information. This extension is part of the core PostgreSQL distribution, and is typically available already in your deployment (including AWS RDS and Aurora). Documentation for this extension can be found in the PostgreSQL docs here. Follow these steps to install the extension:

• Update the configuration setting shared_preload_libraries (docs) to include "pg_stat_statements".

• The PostgreSQL server needs to be restarted for this change to take affect. (Yes, this is required and cannot be avoided. This is the case for AWS RDS also.)

• Connect to the database and run CREATE EXTENSION pg_stat_statements; You'll need to connect as a superuser or at least the database owner.

• Verify that regular users are able to run SELECT * FROM pg_stat_statements LIMIT 1 and see the results without any errors. In particular, the "query" column should not say "<insufficient privilege>".

Note that even if you install the extension only in one database, it will collect and report queries for all the databases.

You can use the psql command \dx to list all the currently installed extensions in a database.

Using pgmetrics to collect query information

By default, pgmetrics will attempt to collect query information from pg_stat_statements without requiring any explicit actions. There are a few command-line parameters you can tweak to ensure it does what you want it to:

• --sql-limit=500 By default, pgmetrics collects only the first 500 characters of SQL queries, which is hopefully enough to identify the query which you can then locate in your code. If 500 is too few for you, you can increase the value by using this option, like so: pgmetrics --sql-limit=1000 {other.args}. Also note that pg_stat_statements itself has a similar limit (by default 5000). If you need to go above this limit, you'll need to change the setting pg_stat_statements.max (more details here).

• --statements-limit=100 By default, pgmetrics collects the 100 slowest queries (as ordered by total_time). You can change this number using this parameter, like so: pgmetrics --statements-limit=200 {other.args}.

Collecting Disk I/O Times

PostgreSQL can track the amount of time it spends doing disk I/O, but this is feature is turned off by default. The configuration setting "track_io_timing" (docs) controls this setting. It is off by default because the measurement may incur an overhead. However, it should be safe to turn it on in nearly any modern Linux system. You can check the actual overhead using the pg_test_timing tool.

When track_io_timing is enabled, pg_stat_statements can report the time spent by each query in performing disk reads and writes. In practice, this information can be quite useful in analyzing query performance.

Resetting The Statistics

pg_stat_statements reports aggregate values (average, standard deviation, sum) of the actual query execution statistics. These values are accumulated permanently (even across server restarts) until reset manually. Because of this, you may find that it takes time for the stats to reflect the current numbers. Resetting is especially helpful after an earnest round of query optimization or a significant change in workload patterns.

You can use the function pg_stat_statements_reset() to clear accumulated statistics. Use it like so: SELECT pg_stat_statements_reset(); You'll need superuser privileges to run this function.

Postgres 12 is more flexible and can discard only a subset of the accumulated statistics. For more information see the docs.