pg_stat_statements are your friends.

Let me tell you a story of the day our users face slowness in our application. As we investigate further, we found out that our RDS instance CPU use is at constant 100%.

One of the things we do is to increase our instance size, and that is not helpful. Which lead to one of my go-to tools in PostgreSQL for this kind of issue, pg_stat_statements.

In a rails application, there's many queries to the database. Tracing individual queries can be painful. There is where pg_stat_statements come in. When enabled, this logs all database queries to the database. This also collects various statistics in the query.

This is a module that AWS have compiled in their PostgreSQL RDS instance. It is not something that is enabled by default. But it is easy to enable, AWS provides documentation for this.

To use this, do a SQL query, this is a view. The following is my usual query to get the statistics that I need. This will get the total run time in minutes, and the mean execution time, number of calls, and actual query. This is ordered by the total_min field.

SELECT
  (total_exec_time / 1000 / 60) as total_min,
  mean_exec_time as avg_ms,
  calls,
  query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 5; 

This is an example of how you use this. First create extensions, only for the first time. Then set extended view \x to make view easier. Then actual query.

db=> CREATE EXTENSION pg_stat_statements; 
db=> \x
db=> SELECT
  (total_exec_time / 1000 / 60) as total_min,
  mean_exec_time as avg_ms,
  calls,
  query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 5;

You should see the following. I edited the query; this is query post optimization. But you do not usually get the parameter, it will be set as $1 for first parameter, $2 for second etc.

total_min | 49.976002760516685
avg_ms    | 38.661167684772884
calls     | 77560
query     | SELECT field FROM "tables" WHERE "table"."field" = $1;

What you should do with this information is find out the function and from the function, infer potential parameter for running the query. Then run EXPLAIN query to find out issue with said database query.

Now back to the story of the slowness in our database. From running query on pg_stat_statements . I with help of EXPLAIN I found out some of our column should be indexed added. That helped speed up our query.