`pg_stat_statements`: installation and fundamentals

Capsule overview

You eliminated the bookstore's N+1 in the previous module because you knew which endpoint was wrong. In production you don't have that luxury. The question pg_stat_statements answers is: "of all the queries my database runs, which ones consume the most resources?" Without that answer, optimizing is charity: you add indexes at random, hope something improves, and rarely have measurable impact.

pg_stat_statements is an extension that has shipped with PostgreSQL for years. You don't install it like a Python library — you enable it in postgresql.conf and tell the database to record statistics for every query it runs. From that moment on, you can query a view (pg_stat_statements) that shows you, for each unique query, how many times it ran, how much total time it consumed, how much on average, how many disk blocks it read, and more.

This capsule teaches you:

• Enable pg_stat_statements from scratch in PostgreSQL 16, both in Docker and in a native installation.
• Verify that it's capturing data correctly.
• Understand the key concept of query normalization: why WHERE id = 1 and WHERE id = 2 count as the same query.
• Recognize the most important columns of the view and what each one tells you.
• Apply the reset discipline (pg_stat_statements_reset()) that separates a useful measurement from a meaningless number.

By the end, you'll have pg_stat_statements running against your local bookstore with real data you'll be able to query in the next capsule.

Mental model: the black box that counts

Think of pg_stat_statements as a supermarket cashier who takes note of everything that's bought:

• It doesn't care who bought (which user or app).
• It doesn't care when specifically (it doesn't store individual timestamps).
• It cares what product and how many times it was bought, summarized.

If 1,000 people come to buy apples, the cashier doesn't record 1,000 transactions — it records one entry that says "apples: 1,000 units, $5,000 total, $5 average each". If 200 people then come to buy pears, it records another entry for pears.

pg_stat_statements does the same with SQL queries:

• It doesn't store each individual execution (that would be an infinite log).
• It stores a summary per query shape (not per concrete value).
• Every time you see SELECT * FROM books WHERE id = 1, the extension groups it with SELECT * FROM books WHERE id = 2, WHERE id = 3, etc., under a single normalized entry: SELECT * FROM books WHERE id = $1.

That "query shape" is called a queryid. For each queryid, it keeps a metric accumulator: number of calls, accumulated total time, average time (calculated), blocks read, etc.

These queries come in over 1 minute:
  SELECT * FROM books WHERE id = 5;     (3ms)
  SELECT * FROM books WHERE id = 12;    (2ms)
  SELECT * FROM books WHERE id = 7;     (4ms)
  SELECT * FROM authors WHERE name = 'tolkien';   (15ms)

pg_stat_statements records:
  query: SELECT * FROM books WHERE id = $1
    calls: 3
    total_exec_time: 9ms
    mean_exec_time: 3ms

  query: SELECT * FROM authors WHERE name = $1
    calls: 1
    total_exec_time: 15ms
    mean_exec_time: 15ms

That's all the magic. The rest of the extension is plumbing around that accumulator.

Installation: step by step

pg_stat_statements ships included with PostgreSQL since version 8.4 (years ago). It's a contrib module. All you have to do is enable it.

There are two non-negotiable steps and one optional:

1. Add pg_stat_statements to shared_preload_libraries in postgresql.conf.
2. Restart PostgreSQL (not pg_reload_conf() — a reload isn't enough, it needs a full restart because it loads the library at startup).
3. Run CREATE EXTENSION pg_stat_statements; in the database where you want to use it.

Option A: Docker (recommended for this guide)

If you're following the module 1 bookstore, the fastest way is to start PostgreSQL with a custom postgresql.conf mounted.

1. Create the postgresql.conf file in your project folder:

mkdir -p ~/projects/bookstore-baseline/pg-config
cd ~/projects/bookstore-baseline

Create the file pg-config/postgresql.conf with this minimal content:

# pg-config/postgresql.conf

# Critical defaults inherited from the official image's entrypoint
listen_addresses = '*'
max_connections = 100

# Extension we enable in this module
shared_preload_libraries = 'pg_stat_statements'

# pg_stat_statements-specific configuration
pg_stat_statements.track = 'all'        # capture top-level + nested statements (inside functions)
pg_stat_statements.max = 10000          # how many unique queryids to keep (default is usually 5000)
pg_stat_statements.save = on            # persist across restarts

2. Start the container mounting the file:

# If you have a previous container, stop it and remove it:
docker stop bookstore-pg && docker rm bookstore-pg

docker run -d --name bookstore-pg \
    -e POSTGRES_USER=bookstore \
    -e POSTGRES_PASSWORD=bookstore \
    -e POSTGRES_DB=bookstore \
    -p 5432:5432 \
    -v ~/projects/bookstore-baseline/pg-config/postgresql.conf:/etc/postgresql/postgresql.conf \
    postgres:16 \
    -c config_file=/etc/postgresql/postgresql.conf

The -c config_file=... tells PostgreSQL to use your file instead of the one the image generates by default.

3. Verify the library loaded:

docker logs bookstore-pg | grep -i pg_stat_statements

Expected output (not an error, it's info):

LOG:  registering background worker "pg_stat_statements"

If you don't see that line, shared_preload_libraries didn't take. Check that the file mounted correctly with docker exec bookstore-pg cat /etc/postgresql/postgresql.conf.

4. Connect and create the extension:

docker exec -it bookstore-pg psql -U bookstore -d bookstore

Inside psql:

CREATE EXTENSION pg_stat_statements;

If it says "already exists", perfect, someone already created it. If it says "could not open extension control file", the library isn't loaded — go back to the previous step.

5. Verify it works:

-- This query should return at least one row (the CREATE EXTENSION query itself):
SELECT calls, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

If you see results, done. You now have pg_stat_statements capturing everything.

Option B: native PostgreSQL (Linux/Mac without Docker)

If you have PostgreSQL installed directly on your system:

1. Locate your postgresql.conf:

psql -U postgres -c 'SHOW config_file;'

Example output:

                config_file
--------------------------------------------
 /etc/postgresql/16/main/postgresql.conf

2. Edit that file with privileges:

sudo vim /etc/postgresql/16/main/postgresql.conf

Find the shared_preload_libraries line (probably commented out with #) and change it to:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000
pg_stat_statements.save = on

3. Restart PostgreSQL:

# Linux with systemd:
sudo systemctl restart postgresql

# Mac with Homebrew:
brew services restart postgresql@16

4. Connect to the bookstore DB and create the extension:

psql -U bookstore -d bookstore -c 'CREATE EXTENSION pg_stat_statements;'

5. Verify the same as in option A.

What does each configuration parameter do?

shared_preload_libraries = 'pg_stat_statements'

Tells PostgreSQL to load this library at startup. Without this, the extension captures nothing (even if it's created with CREATE EXTENSION).

pg_stat_statements.track = 'all'

Three possible values:

• 'top': captures only top-level statements (what arrives directly from your app). It does not capture statements run inside PL/pgSQL functions or triggers.
• 'all': captures top-level + nested. More complete. Recommended for serious profiling.
• 'none': disables capture without uninstalling the extension. Useful for temporary auditing.

For the bootcamp and for real production, 'all' is the correct option.

pg_stat_statements.max = 10000

How many unique queryids it can keep simultaneously. If your app generates more unique queries than this number, the least-used ones are discarded. For typical apps, 5000-10000 is enough. For apps with poorly normalized dynamic queries, you may need more.

pg_stat_statements.save = on

If it's on, the statistics are saved to disk (pg_stat_tmp/pgss_query_texts.stat) and persist across restarts. If it's off, they're lost on each restart. For production, always on.

pg_stat_statements.track_utility = on  # (default)

If it's on, it also captures utility statements (CREATE, ALTER, ANALYZE, VACUUM). Useful for detecting slow administrative commands. A reasonable default.

The key concept: normalization

The part of pg_stat_statements that confuses people most is seeing a query and not finding the value you expect.

If your app ran:

SELECT * FROM books WHERE id = 42;

and you go to pg_stat_statements expecting to see that exact line, you will NOT find it. You'll see:

SELECT * FROM books WHERE id = $1;

The 42 disappeared, replaced by $1. This is called normalization, and it's a feature, not a bug. Without normalization, a query SELECT * FROM books WHERE id = 1 and WHERE id = 2 and WHERE id = 3 (a thousand different ids) would generate a thousand different entries. Useless for profiling — you want to know the aggregate cost of "that query shape", not of each individual execution.

How it works internally

PostgreSQL parses each query, identifies the literals (numbers, strings, etc.) and replaces them with positional placeholders ($1, $2, ...). The result is hashed and that hash is the queryid.

-- These three queries generate the SAME queryid:
SELECT * FROM books WHERE id = 1;
SELECT * FROM books WHERE id = 999;
SELECT * FROM books WHERE id = 12345;

-- Stored as:
SELECT * FROM books WHERE id = $1;

-- This query generates a DIFFERENT queryid (another shape):
SELECT * FROM books WHERE title = 'The Hobbit';

-- Stored as:
SELECT * FROM books WHERE title = $1;

-- And this other one (it changes the WHERE column):
SELECT * FROM books WHERE author_id = 5;

-- Stored as:
SELECT * FROM books WHERE author_id = $1;

Practical consequences

1. Queries with literals break normalization.

If your app builds SQL with direct string interpolation (bad practice, plus SQL injection):

# BAD
session.execute(text(f"SELECT * FROM books WHERE id = {book_id}"))

Each different book_id generates a different entry in pg_stat_statements. You'll have massive noise.

If you use the SQLAlchemy ORM or text() with parameters (the correct way):

# GOOD
session.execute(text("SELECT * FROM books WHERE id = :id"), {"id": book_id})
session.scalar(select(Book).where(Book.id == book_id))

PostgreSQL always receives the query with $1 and pg_stat_statements normalizes it correctly.

2. IN (...) lists of variable size generate different queryids.

This is a famous trap:

-- queryid A:
SELECT * FROM books WHERE id IN ($1, $2, $3);

-- queryid B (different number of elements):
SELECT * FROM books WHERE id IN ($1, $2, $3, $4, $5);

-- queryid C:
SELECT * FROM books WHERE id IN ($1, $2);

For pg_stat_statements, each IN cardinality is a different query. If your endpoint fires WHERE id IN (...) with a variable count, you'll see many near-identical entries.

PostgreSQL 17+ improved this with a better query_id for IN-lists, but in 16 it still applies. Common workaround: use WHERE id = ANY($1::int[]) with an array, which normalizes to a single shape.

3. Changes in column order or whitespace can generate different queryids.

SELECT id, name FROM books;    -- queryid X
SELECT name, id FROM books;    -- queryid Y (different)
SELECT  id,  name  FROM books; -- queryid X (whitespace is normalized)

PostgreSQL normalizes whitespace but not column order. Inconsistency between your code and your joins can multiply entries.

The columns that matter

The pg_stat_statements view has many columns. For daily profiling, these are the ones you'll always look at:

The columns with the _plan_time suffix apply only if you enable planning time tracking separately (we don't cover it here; the default doesn't include it).

Basic query example

After running the bookstore for a few minutes:

SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Example output:

 calls | total_ms | mean_ms | rows |                          query
-------+----------+---------+------+-----------------------------------------------------------
  1820 | 12450.30 |    6.84 | 9100 | SELECT b.id, b.title, b.author_id FROM books b WHERE b.author_id = $1
   240 |  3200.50 |   13.34 | 2400 | SELECT * FROM books WHERE title ILIKE $1
    12 |  1450.20 |  120.85 |  240 | SELECT count(*) FROM orders WHERE created_at > $1
   ...

This is the information you'll learn to read in four different dimensions in the next capsule. For now, it's enough that you see it's working.

The reset discipline

The statistics in pg_stat_statements are cumulative since the last reset (or since the last PostgreSQL restart if it was never reset). If your database has gone 6 months without restarting and you never ran pg_stat_statements_reset(), what you see is a 6-month average that mixes old loads, obsolete deploys, queries that no longer exist, and today's activity.

For useful profiling, the discipline is:

1. Before measuring a change, reset:

   SELECT pg_stat_statements_reset();

2. Generate representative load (run the app, launch wrk against the endpoints, whatever).

3. Query the view to have a snapshot of "how the DB behaved during this interval".

4. Apply the change you want to test.

5. Reset again and go back to step 2 to have the post-change snapshot.

Without reset, you can't compare before/after honestly. The data gets contaminated.

Reset variants

-- Reset ALL statistics (all queries):
SELECT pg_stat_statements_reset();

-- Reset only a specific user's queries (PG 13+):
SELECT pg_stat_statements_reset(user_oid, db_oid, queryid);

-- Reset a specific query by queryid:
SELECT pg_stat_statements_reset(0, 0, 1234567890);

For 99% of cases, the global reset is what you'll use.

Who can run the reset?

By default, only superusers. If you want a monitoring user (non-superuser) to be able to reset, you have to give them the pg_read_all_stats role and run the specific GRANT:

GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO monitoring_user;

On your local machine with the bookstore user (which you already created as the database owner) you probably have permissions. If not, connect as postgres (the superuser) to reset.

Why this matters in real work

1. It's the first query any DBA runs when diagnosing a slowdown.

When someone says "the database is slow", the first SQL that gets run on any serious team is the pg_stat_statements top ordered by total_exec_time. If you don't know this, you depend on someone else doing it for you.

2. It lets you prioritize refactors with data.

Without pg_stat_statements, when your PM asks you "which endpoint do I optimize first?", the answer is intuition. With pg_stat_statements, the answer is "this endpoint fires the database's query #1 which represents 35% of the total time — attacking here has the highest ROI".

3. It gives you the correct language to talk with DBAs and SREs.

"This query has high shared_blks_read" or "very high calls, low mean_time, looks like N+1" are phrases that open doors. Without this capsule, that vocabulary is opaque.

4. It's portable: it works the same on RDS, Supabase, Neon, Cloud SQL, on-prem.

pg_stat_statements is PostgreSQL standard. The extension is enabled by default (or trivially enableable) in all managed services. The skill you learn here you apply identically in any deployment.

Traps and common mistakes

Mistake 1 (configuration): forgetting the restart after editing postgresql.conf

Symptom: "I edited postgresql.conf, ran pg_reload_conf(), but CREATE EXTENSION pg_stat_statements tells me 'shared_preload_libraries does not contain the library'."

Why it happens: shared_preload_libraries is loaded at PostgreSQL startup. A pg_reload_conf() reapplies most parameters, but NOT the ones that require a restart (like this one). You have to do a full restart.

How to distinguish: if after editing the conf and reloading it still fails, that parameter belongs to the "requires restart" group. pg_settings tells you:

SELECT name, context FROM pg_settings WHERE name = 'shared_preload_libraries';
-- context = 'postmaster' means "requires restart"

How to fix it:

# Docker:
docker restart bookstore-pg

# Linux:
sudo systemctl restart postgresql

# Mac:
brew services restart postgresql@16

Mistake 2 (conceptual): expecting to see literal values in the view

Symptom: "My app ran WHERE id = 42 a thousand times. In pg_stat_statements I don't see 42 anywhere, only $1. Is it broken?"

Why it happens: normalization. pg_stat_statements doesn't store literal values — it replaces them with positional placeholders to group queries by shape.

How to distinguish: if you see entries with $1, $2, etc., the system is working correctly. If you need to see the concrete value of a slow query, the correct tool is auto_explain (next capsule), which captures the full plan with real values for queries that exceed a threshold.

How to fix it: it's not something to "fix" — it's understanding the model. Accept normalization as an advantage, not a limitation.

Mistake 3 (interpretation): comparing total_exec_time of queries with very different calls without thinking about mean

Symptom: "Query #1 has a huge total_exec_time. I'm going to optimize it."

Why it happens: you look only at total_exec_time without checking calls or mean_exec_time. Query #1 can have total_exec_time = 1000s for two very different reasons:

• Case A: 1 call of 1000s (a monster query, must be optimized).
• Case B: 1,000,000 calls of 1ms each (probably N+1, the query isn't the problem but rather whoever fires it that many times).

How to distinguish: always look at the three together. Capsule 03 teaches you to read them in combination.

How to fix it: the discipline of reading calls, mean_exec_time, and total_exec_time together. If mean_exec_time is low and calls is very high, don't optimize the query — find who calls it so much.

Mistake 4 (operational): never resetting and reading months of data

Symptom: "I made a fix, waited a day, queried pg_stat_statements. The slow query still shows up just as slow."

Why it happens: the statistics are cumulative. If the query ran badly for 6 months and now runs well for 1 day, the average is still "bad" because the historical weight dominates.

How to distinguish: look at whether mean_exec_time changed relative to the baseline. If it didn't change but max_exec_time did (dropped), your fix worked but the accumulated average hides it.

How to fix it: the discipline of pg_stat_statements_reset() before and after each change you want to measure.

Mistake 5 (security): exposing queries with sensitive literals

Symptom: "I saw a query in pg_stat_statements that included a user's email in plain text."

Why it happens: if the query was built with string interpolation (without parameters), the literal stays in the queryid and isn't normalized. Anyone with permissions on the view can see it.

How to distinguish: check whether your view has queries with concrete strings instead of $1. Those are badly parameterized queries in your app.

How to fix it:

1. Audit the code: any badly done text(f"...{var}...") or f"SELECT ... {value}".
2. Switch to parameters: text("... :var") with a parameter dict, or use the ORM.
3. Restrict access to the view: REVOKE SELECT ON pg_stat_statements FROM PUBLIC; and grant it only to monitoring users.

Exercises

Exercise 1: install and verify pg_stat_statements in the bookstore

Bring up the bookstore (from module 1) with pg_stat_statements enabled using Docker. Verify that the extension is active and capturing data.

mkdir -p ~/projects/bookstore-baseline/pg-config
cat > ~/projects/bookstore-baseline/pg-config/postgresql.conf <<'EOF'
listen_addresses = '*'
max_connections = 100

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000
pg_stat_statements.save = on
EOF

docker stop bookstore-pg 2>/dev/null && docker rm bookstore-pg 2>/dev/null

docker run -d --name bookstore-pg \
    -e POSTGRES_USER=bookstore \
    -e POSTGRES_PASSWORD=bookstore \
    -e POSTGRES_DB=bookstore \
    -p 5432:5432 \
    -v ~/projects/bookstore-baseline/pg-config/postgresql.conf:/etc/postgresql/postgresql.conf \
    postgres:16 \
    -c config_file=/etc/postgresql/postgresql.conf

docker logs bookstore-pg 2>&1 | grep pg_stat_statements
# Expected: LOG:  registering background worker "pg_stat_statements"

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "CREATE EXTENSION pg_stat_statements;"

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "SELECT count(*) FROM pg_stat_statements;"

Exercise 2: generate traffic and read the top

With the bookstore running, run the app and make 50-100 requests to the /books-with-author?author_name=tolkien endpoint. Then query pg_stat_statements and show the top 5 queries by total_exec_time.

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c \
    "SELECT pg_stat_statements_reset();"

cd ~/projects/bookstore-baseline
source venv/bin/activate
uvicorn main:app --reload

for i in {1..100}; do
    curl -s "http://localhost:8000/books-with-author?author_name=tolkien" > /dev/null
done

docker exec -it bookstore-pg psql -U bookstore -d bookstore -c "
SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 5;
"

 calls | total_ms | mean_ms |                  query
-------+----------+---------+-------------------------------------------
   500 |  4250.30 |    8.50 | SELECT books.id, books.title FROM books WHERE books.author_id = $1
   100 |   850.20 |    8.50 | SELECT authors.id, authors.name FROM authors WHERE authors.name = $1
   ...

Exercise 3: normalization experiment

Connect to the database with psql and manually run these three queries. Then query pg_stat_statements and observe how they appear.

SELECT * FROM books WHERE id = 1;
SELECT * FROM books WHERE id = 999;
SELECT * FROM books WHERE id = 42;

How many entries appear in the view for these three queries? Justify it.

docker exec -it bookstore-pg psql -U bookstore -d bookstore

-- Previous reset:
SELECT pg_stat_statements_reset();

-- Run the three queries:
SELECT * FROM books WHERE id = 1;
SELECT * FROM books WHERE id = 999;
SELECT * FROM books WHERE id = 42;

-- Query:
SELECT calls, query
FROM pg_stat_statements
WHERE query LIKE '%books WHERE id%';

 calls |               query
-------+-------------------------------------
     3 | SELECT * FROM books WHERE id = $1

SELECT pg_stat_statements_reset();

-- These DO generate different queryids (different IN cardinality):
SELECT * FROM books WHERE id IN (1);
SELECT * FROM books WHERE id IN (1, 2);
SELECT * FROM books WHERE id IN (1, 2, 3);

SELECT calls, query
FROM pg_stat_statements
WHERE query LIKE '%books WHERE id IN%';

Exercise 4: measure the impact of a reset

Run a query 10 times. Reset. Run it 1 more time. Query pg_stat_statements. How many calls appear?

-- Initial reset:
SELECT pg_stat_statements_reset();

-- 10 executions:
SELECT * FROM books WHERE id = 1;
SELECT * FROM books WHERE id = 2;
SELECT * FROM books WHERE id = 3;
SELECT * FROM books WHERE id = 4;
SELECT * FROM books WHERE id = 5;
SELECT * FROM books WHERE id = 6;
SELECT * FROM books WHERE id = 7;
SELECT * FROM books WHERE id = 8;
SELECT * FROM books WHERE id = 9;
SELECT * FROM books WHERE id = 10;

-- Verify:
SELECT calls FROM pg_stat_statements
WHERE query = 'SELECT * FROM books WHERE id = $1';
-- Expected: 10

-- Reset:
SELECT pg_stat_statements_reset();

-- 1 more execution:
SELECT * FROM books WHERE id = 11;

-- Verify:
SELECT calls FROM pg_stat_statements
WHERE query = 'SELECT * FROM books WHERE id = $1';
-- Expected: 1

Exercise 5: identify a query with unsafe interpolation

Imagine you see this entry in pg_stat_statements:

 calls |                                  query
-------+--------------------------------------------------------------------
   847 | SELECT * FROM users WHERE email = 'alice@example.com'
   523 | SELECT * FROM users WHERE email = 'bob@example.com'
   401 | SELECT * FROM users WHERE email = 'carol@example.com'
   ...

What problem do you diagnose? What change would you make in the app code?

# BAD
def get_user(email: str):
    return session.execute(text(f"SELECT * FROM users WHERE email = '{email}'"))

# GOOD — option 1: ORM
from sqlalchemy import select
from app.models import User

def get_user(email: str):
    stmt = select(User).where(User.email == email)
    return session.scalar(stmt)

# GOOD — option 2: text with parameters
from sqlalchemy import text

def get_user(email: str):
    stmt = text("SELECT * FROM users WHERE email = :email")
    return session.execute(stmt, {"email": email})

 calls |               query
-------+-----------------------------------------
  1771 | SELECT * FROM users WHERE email = $1

Summary and next step

In this capsule you:

• Enabled pg_stat_statements in PostgreSQL 16 with Docker (or native): shared_preload_libraries, restart, CREATE EXTENSION.
• Understood that the extension normalizes queries by replacing literals with $1, $2, etc., to group by shape and be able to summarize cost.
• Learned the columns that matter: calls, total_exec_time, mean_exec_time, rows, shared_blks_*.
• Internalized that the statistics are cumulative and that pg_stat_statements_reset() is a mandatory discipline before measuring changes.
• Recognized the anti-patterns that break normalization (queries with interpolated literals) and why fixing them matters.

Before moving on, you should be able to:

• Enable pg_stat_statements on any PostgreSQL you have access to.
• Explain normalization with an example (what happens with WHERE id = 1 vs WHERE id = 2).
• List at least 5 useful columns of the view and what each one is for.
• Justify why reset is necessary before measuring a change.

Next capsule — Reading pg_stat_statements: top queries. You already have the tool running. Now you learn to read it seriously. There are four different useful orderings (by total_exec_time, by mean_exec_time, by calls, by shared_blks_read) and each one reveals a different kind of problem. Capsule 03 teaches you the 4 canonical SQL queries you'll keep as snippets for the rest of your career, and how to interpret the results of each one to prioritize fixes.

Resources

1. PostgreSQL 16 — pg_stat_statements — official documentation: installation, columns, configuration.
2. PostgreSQL — shared_preload_libraries — the parameter's reference and why it requires a restart.
3. Lukas Fittl — "Effective query analysis with pg_stat_statements" — a practical overview and when to apply it.
4. Hubert "depesz" Lubaczewski — "Why is my query slow?" — a classic on query analysis.
5. PostgreSQL wiki — Query Normalization — the normalization concept in detail.
6. Crunchy Data — "PostgreSQL pg_stat_statements" — tuning the module itself.

Module 5 — Database Performance & Query Tuning Guide