Finding the Most Resource-Consuming SQL Statements (Top SQL)

What Is Top SQL?

Top SQL refers to SQL statements that consume the most system resources (CPU, I/O, or memory) or have the highest execution frequency within a given period. Quickly and accurately identifying top SQL statements is a key step in database performance tuning.

Prerequisites

Top SQL analysis typically relies on the pg_stat_statements extension, which collects execution statistics for SQL statements.

For details about how to install this extension, see Using the pg_stat_statements Extension.

Best Time to Analyze Top SQL

When you are troubleshooting performance issues, the time you choose to analyze the database determines whether the collected metrics are useful.

Query Templates for Identifying Top SQL Statements

The following query templates target different resource dimensions. These queries are based on the historical view in pg_stat_statements, with column names adjusted for compatibility with PostgreSQL 14 and later versions.

• Find SQL statements with the highest total execution time (CPU spikes).

  These SQL statements are typically prime candidates for performance tuning because optimizing queries with high cumulative execution times yields the greatest overall performance gains.

  -- PostgreSQL 13 and earlier
  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; 
  -- PostgreSQL 14 and later
  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

• Find SQL statements with the highest I/O consumption (disk bottlenecks).

  These statements involve large numbers of data block reads and writes, often indicating missing indexes or full table scans.

  -- PostgreSQL 13 and earlier
  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time + blk_write_time) DESC LIMIT 5; 
  -- PostgreSQL 14 and later
  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blk_read_time + local_blk_read_time + temp_blk_read_time + shared_blk_write_time + local_blk_write_time + temp_blk_write_time) DESC LIMIT 5;

• Find the slowest SQL statements per execution (long-tail queries).

  Some SQL statements have low total execution time (total_exec_time) but are slow on each execution. This may cause frontend API timeouts.

  -- PostgreSQL 13 and earlier
  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5; 
  -- PostgreSQL 14 and later
  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

• Find the most frequently executed SQL statements (high-frequency queries).

  Some SQL statements take only 0.1 ms per execution but run thousands of times per second, consuming significant CPUs and other resources.

  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

• Check the real-time status (pg_stat_activity).

  When the system responds slowly, data in pg_stat_statements may lag. In such cases, you should directly check currently running SQL statements.

  SELECT pid, usename, datname, state, wait_event_type, wait_event, query, age(clock_timestamp(), query_start) AS duration FROM pg_stat_activity WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%' ORDER BY query_start ASC; 

  wait_event: If IO:DataFileRead is displayed, it indicates disk reads (missing indexes). If Lock:transactionid is displayed, there is a lock conflict.

Resetting Statistics

pg_stat_statements records cumulative data. You can periodically reset counters to collect fresh statistics:

SELECT pg_stat_statements_reset();