Working With Queries
With pgDash, you can examine how your SQL queries are performing, identify reasons for slow queries, track query stats over time and examine query execution plans.
pgDash relies on the popular extensions pg_stat_statements and auto_explain to collect query and plan information. These extensions are part of the core PostgreSQL distribution, and are typically available already in your deployment (including AWS RDS and Aurora). Documentation for the extensions can be found in the PostgreSQL docs here and here.
Tip: You can use the psql command \dx to list all the currently installed extensions in a database.
Follow these steps to install the pg_stat_statements 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 1and 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.
Follow these steps to install the auto_explain extension:
Update the configuration setting
shared_preload_libraries(docs) to include "auto_explain".Add auto_explain related settings to your PostgreSQL configuration. Refer to the auto_explain documentation for the full list. Here is a starting point:
auto_explain.log_min_duration = 2000auto_explain.log_analyze = 1auto_explain.log_timing = 0auto_explain.log_format = jsonWith the settings above, any SQL statement that executes for more than 2 seconds will have it's execution plan logged into the PostgreSQL log file in JSON format. Be sure to go through all available settings in the documentation and tweak them to suit your deployment.
For the changes to take effect, manually run
LOAD auto_explain;to load the extension andALTER SYSTEM SETto change the value of the configuration settings. (You can also reload PostgreSQL for the configuration changes to take effect).The auto_explain extension is independent of any database, and the above steps need to be done only once for the entire server.
The query information is collected by connecting to PostgreSQL and querying the pg_stat_statements view.
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=500By 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 settingpg_stat_statements.max(more details here).--statements-limit=100By 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}.
The plan information is collected from PostgreSQL log files. pgmetrics has to be run on the same system as the PostgreSQL server so that it can read and parse the PostgreSQL log files. Currently reading of AWS RDS and Aurora log files is not supported. Ensure that you have pgmetrics v1.8.0 or above and pgdash CLI v1.4.0 or above. Links for latest releases: pgmetrics and pgdash CLI.
When pgmetrics is run from the machine that hosts both the Postgres server and its log files, pgmetrics will attempt to automatically locate and read the last few minutes' worth of log files to collect auto_explain output. The following command-line arguments can help tweak the exact behavior:
--log-file=path/to/log/fileIf the default logic does not locate the correct log file, use this option to specify the exact location of the log file.--log-span=MINUTESBy default pgmetrics scans only the log file entries made during last 5 minutes of the log file. (Note that pgmetrics is meant to be run periodically, like every 5 minutes). You can change the default value of 5 using this option.
PostgreSQL can track the amount of time it spends doing disk I/O, but this 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.
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.