How can you find and analyze slow queries in PostgreSQL?

There are several main methods:

1. Enabling slow query logging in PostgreSQL configuration.

   In the postgresql.conf file, you can configure the following parameters:

   • log_min_duration_statement: Logs queries that run longer than the specified time (in milliseconds).
   • log_statement: Determines which queries to log ('none', 'ddl', 'mod', 'all'). For analyzing slow queries, it is usually used in conjunction with log_min_duration_statement.

   After changing postgresql.conf, you need to reload the configuration or restart the PostgreSQL server itself. Logs will be located in the PostgreSQL data directory, which can be found from the log_directory parameter.

   Logs can be analyzed manually or with tools like pgBadger, which parses logs and generates readable reports.

2. Using the EXPLAIN and EXPLAIN ANALYZE commands.

   • EXPLAIN: Shows the query execution plan without actually executing it. This helps understand how the database plans to retrieve data, which indexes will be used, and what operations will be performed (table scans, joins, etc.).
   • EXPLAIN ANALYZE: Executes the query and shows the actual execution plan, including time spent on each step, number of rows processed by each plan node, and memory usage. This provides a more accurate view of query performance.

   When analyzing the output of EXPLAIN ANALYZE, pay attention to:

   • Types of scans (Seq Scan often indicates missing indexes).
   • Join operations (Nested Loop, Hash Join, Merge Join) and their efficiency.
   • Time spent on each plan node.
   • Use of Shared Buffers.
   • Use of temporary files.

3. Using the pg_stat_statements extension.

   This extension, included with PostgreSQL, collects statistics on executed queries, including total execution time, number of calls, average execution time, etc.

   To use pg_stat_statements, it must be enabled in the shared_preload_libraries parameter in postgresql.conf and the server reloaded, then run CREATE EXTENSION pg_stat_statements; in each database where statistics are to be collected.

   The statistics are available through the pg_stat_statements view. The easiest way to get a list of the slowest queries:

   -- Select the 10 slowest queries by average execution time
   SELECT query, mean_exec_time, calls, total_exec_time
   FROM pg_stat_statements
   ORDER BY mean_exec_time DESC
   LIMIT 10;
   

   -- Select the 10 queries with the highest total execution time
   SELECT query, total_exec_time, calls, mean_exec_time
   FROM pg_stat_statements
   ORDER BY total_exec_time DESC
   LIMIT 10;
   

   pg_stat_statements helps identify queries that most frequently or most heavily load the system. It is important to consider both average execution time and total execution time (which depends on the number of calls).

Combining these methods provides a comprehensive view of query performance in PostgreSQL and helps identify bottlenecks.