PSQL Slowest Average Queries

psql_slowest_average_queries.sql

# See the 10 slowest queries with over a 1000 calls
SELECT query, calls, (total_time/calls)::integer AS avg_time_ms 
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY avg_time_ms DESC
LIMIT 10;

#  query     |  calls  | avg_time_ms
#  ----------+---------+-------------
#  INSERT .. |   52323 |          12
#  SELECT .. |  116948 |          10
#  INSERT .. |  116948 |           4
#  SELECT .. |  116948 |           4
#  INSERT .. |  116948 |           3
#  SELECT .. |  116948 |           1
#  UPDATE .. |  116949 |           1
#  SELECT .. |  116947 |           1
#  DELETE .. |  116946 |           1
#  SELECT .. |    1465 |           1
#  (10 rows)


# See the 10 slowest SELECT queries with over a 1000 calls
SELECT query, calls, (total_time/calls)::integer AS avg_time_ms 
FROM pg_stat_statements
WHERE calls > 1000
AND query iLIKE '%SELECT%'
ORDER BY avg_time_ms DESC
LIMIT 10;

#  query     |  calls  | avg_time_ms
#  ----------+---------+-------------
#  SELECT .. |   52323 |          12
#  SELECT .. |  116948 |          10
#  SELECT .. |  116948 |           4
#  SELECT .. |  116948 |           4
#  SELECT .. |  116948 |           3
#  SELECT .. |  116948 |           1
#  SELECT .. |  116949 |           1
#  SELECT .. |  116947 |           1
#  SELECT .. |  116946 |           1
#  SELECT .. |    1465 |           1
#  (10 rows)