0Pricing
SQL Academy · Lesson

pg_stat_statements: Top Queries

Enable pg_stat_statements, find the heaviest queries by total time, and target them for optimisation.

pg_stat_statements: Top Queries is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What pg_stat_statements Gives You

An accumulating record of every query, normalised by shape — the single most useful DBA tool in PostgreSQL.

Enable It

It's a contrib extension. Add to shared_preload_libraries and restart:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- After restart:
CREATE EXTENSION pg_stat_statements;

Top Queries by Total Time

The "where is my CPU going" query:

SELECT calls,
       total_exec_time,
       mean_exec_time,
       rows,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Slow Per-Call Queries

Queries that are slow on every call:

SELECT mean_exec_time, calls, query
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

Frequent Cheap Queries

A million 1ms queries is still a problem:

SELECT calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

Normalisation

pg_stat_statements groups queries by normalised shape — literals replaced with $N. So "SELECT * FROM users WHERE id = 1" and "= 2" become the same row.

Resetting Stats

Start fresh after a deployment to measure new behaviour:

SELECT pg_stat_statements_reset();

I/O vs CPU Time

Newer PG versions split execution time. shared_blks_hit / shared_blks_read hint at cache vs disk:

SELECT query, shared_blks_hit, shared_blks_read,
       shared_blks_read::FLOAT / NULLIF(shared_blks_hit + shared_blks_read, 0) AS read_ratio
FROM pg_stat_statements
ORDER BY shared_blks_read DESC LIMIT 20;

JIT and Planning Time

PG 13+ exposes total_plan_time. Some queries spend more time PLANNING than running — usually a sign to use prepared statements.

Stats Per Database / Per User

pg_stat_statements is cluster-wide. Filter by dbid and userid columns for per-database / per-user views.

Combining With EXPLAIN ANALYZE

pg_stat_statements tells you WHICH queries are slow. EXPLAIN ANALYZE tells you WHY.

Caveats

  • Per-database, per-user accumulation can hide issues — look at the total
  • Doesn't track per-row time, just per-call total
  • Resets on restart or reset call

Recap

pg_stat_statements is mandatory in production.

  • Top by total time → biggest wins
  • Top by mean → consistent slowness
  • Top by calls → frequency problem
  • Pair with EXPLAIN for fixes

Quick Check

You enabled pg_stat_statements. Which query gives the biggest perf wins to investigate?

Frequently asked questions

Is the “pg_stat_statements: Top Queries” lesson free?

Yes — the full text of “pg_stat_statements: Top Queries” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “pg_stat_statements: Top Queries”?

Enable pg_stat_statements, find the heaviest queries by total time, and target them for optimisation. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “pg_stat_statements: Top Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. pg_stat_statements: Top Queries
  2. pgBadger for Log Analysis
  3. Connection Pooling: PgBouncer
  4. Capacity Planning and Bloat Audits
← Back to SQL Academy