Step 6: Fetching Data from Stats Tables:

The pg_stat_statements extension in PostgreSQL is a contrib module that provides a way to track and analyze SQL statement execution statistics. It collects information about executed SQL statements, including their execution time, number of calls, number of rows processed, and more.

When the pg_stat_statements extension is enabled, it records statistics for all SQL statements executed by the database server. These statistics can be used to gain insights into the performance of individual queries and identify areas for optimization.

To retrieve query statistics from the pg_stat_statements table, use the appropriate query based on the PostgreSQL version:

For versions <= 12.x:

SELECT
  total_time AS "Total (ms)",
  to_char(total_time/1000/60/60, '999,999,999,990.99') AS "Total (hours)",
  to_char((total_time/calls), '999,999,990.990') AS "Average (ms)",
  to_char(calls, '999,999,999') AS "Calls",
  to_char(rows, '999,999,999,999') AS "Rows",
  to_char(rows/calls, '999,999,990') AS "Average Rows",
  to_char(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read, 0), '999.999') AS "Hit Percent",
  query AS "Query"
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 100;

For versions >= 13:

SELECT
  (SELECT datname FROM pg_database WHERE oid=dbid) AS db_name,
  total_exec_time AS "Total (ms)",
  to_char(total_exec_time/1000/60/60, '999,999,999,990.99') AS "Total (hours)",
  to_char((total_exec_time/calls), '999,999,990.990') AS "Average (ms)",
  to_char(calls, '999,999,999') AS "Calls",
  to_char(rows, '999,999,999,999') AS "Rows",
  to_char(rows/calls, '999,999,990') AS "Average Rows",
  to_char(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read, 0), '999.999') AS "Hit Percent",
  query AS "Query"
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 100;

Identify the queries consuming the most CPU resources using the above query.