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.\dx
to list all the currently installed extensions in a database.CREATE EXTENSION pg_stat_statements;
You'll need to connect as a superuser or at least the database owner.SELECT * FROM pg_stat_statements LIMIT 1
and see the results without any errors. In particular, the "query" column should not say "<insufficient privilege>".auto_explain.log_min_duration = 2000
auto_explain.log_analyze = 1
auto_explain.log_timing = 0
auto_explain.log_format = json
LOAD auto_explain;
to load the extension and ALTER SYSTEM SET
to change the value of the configuration settings. (You can also reload PostgreSQL for the configuration changes to take effect).--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}
.--log-file=path/to/log/file
If the default logic does not locate the correct log file, use this option to specify the exact location of the log file.
--log-span=MINUTES
By 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.
--aws-rds-dbid
option, as well credentials to access the AWS APIs must be set so that pgmetrics can fetch the Postgres logs via CloudWatch. See this page for more info.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.