TIL — Postgres slow queries with pg_stat_statements

Using the pg_stat_statements extension, we can quickly identify problematic queries.

SELECT
  userid::regrole AS "user",
  "query",
  -- Number of times the query has been called
  calls AS "num_calls",
  -- Total time spent executing the query
  (ROUND(total_exec_time::numeric / 1000, 2) || 's') AS "total_time",
  (ROUND(mean_exec_time::numeric / 1000, 2) || 's') AS "mean_time",
  (ROUND(stddev_exec_time::numeric / 1000, 2) || 's') AS "stddev_time",
  ROUND((100 * total_exec_time / SUM(total_exec_time::numeric) OVER())::numeric, 2) AS "percentage_overall"

FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 25;

Bonus: Queries with high memory usage!

SELECT 
  userid::regrole AS "user",
  "query"
FROM pg_stat_statements
ORDER by (shared_blks_hit + shared_blks_dirtied) DESC 
LIMIT 25;