Jun 7, 2020

Enable Slow Queries Analysis on Postgres RDS (pg_stat_statements)

Analyzing slow queries on Postgres can be done by enabling slow queries, but it is even more fun with the builtin extension: pg_stat_statements

1. Enable tracking by changing parameters group values (if you did not create a custom parameter group follow these instrcutions and don't forget to reboot the instance to take effect):
pg_stat_statements.track ALL

2. Connect to Postgres
psql -U ADMIN_USER -h RDS_PATH.rds.amazonaws.com -d postgres

3. Create the extension
CREATE EXTENSION pg_stat_statements;

4. Verify the extension was created:
SELECT * 
FROM pg_available_extensions 
WHERE 
    name = 'pg_stat_statements' and 

    installed_version is not null;

5. Analyze the queries usage:
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;

Keep Performing,
Moshe Kaplan

ShareThis

Intense Debate Comments

Ratings and Recommendations