PostgreSQL

What is it?

pg_stat_statements is the most important extension for analysing query performance in PostgreSQL. It records execution statistics for all queries: total time, call count, blocks read, and more.

When to use it?

• To identify the slowest queries in the system
• To analyse resource consumption per query
• As input for the DBA Copilot Query Advisor

Enable the extension:

-- Add to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- Restart PostgreSQL, then:
CREATE EXTENSION pg_stat_statements;

Export data for DBA Copilot:

-- Full export script
COPY (
  SELECT query, calls, total_exec_time, mean_exec_time,
         rows, shared_blks_hit, shared_blks_read,
         shared_blks_written, temp_blks_read, temp_blks_written
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC
  LIMIT 100
) TO '/tmp/pg_stat_statements.csv' WITH CSV HEADER;

PostgreSQL Logs

What is it?

PostgreSQL logs record errors, slow queries, connections and system events. They are the first source of evidence when an incident occurs.

Configure slow query logging:

-- In postgresql.conf:
log_min_duration_statement = 1000  -- queries > 1 second
log_line_prefix = '%t [%p]: [%l-1] '
logging_collector = on
log_directory = 'pg_log'

Log file location:

# Debian/Ubuntu
/var/log/postgresql/

# RedHat/CentOS
/var/lib/pgsql/data/pg_log/

# Query from psql
SHOW log_directory;

EXPLAIN ANALYZE

The detailed execution plan for a query is the most precise evidence for diagnosing individual performance issues. It includes real timing, plan nodes and buffer usage.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ... -- your query here
;