Find slow Postgres queries with pg_stat_statements and EXPLAIN ANALYZE.

pg_stat_statements ships with Postgres but is off until you load it. It needs shared memory, so it has to be in shared_preload_libraries, which takes a server restart. After that, CREATE EXTENSION in each database where you want to read the view. It records every statement with its constants replaced by $1, $2, so WHERE id = 7 and WHERE id = 8 count as one query.

# postgresql.conf (needs a restart)
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on

-- then, in each database where you want the view
CREATE EXTENSION pg_stat_statements;

On a server where you can read the view yourself, this is the one to start with. Sort by total time first. A 2 ms query called 400,000 times a day costs more than a 3-second report run twice.

-- Where the time goes: total time, with calls and the average
SELECT round(total_exec_time) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows / nullif(calls, 0) AS rows_per_call,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 15;

-- Start a fresh measurement after a fix
SELECT pg_stat_statements_reset();

pg_stat_statements shows $1 where the values were. Put real values back: the plan for a customer with 40 orders can differ from one with 400,000. Then ask Postgres how it ran the query.

-- Postgres 18 adds buffer counts to ANALYZE by itself; on 17, ask for them
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 20;

-- ANALYZE really runs the statement. For writes, roll it back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'paid' WHERE id = 991;
ROLLBACK;

A plan is a tree. Each line is a step, indented under the step that consumes its rows; the deepest lines run first. With ANALYZE each step shows two sets of numbers: the planner’s guess (cost, rows) and what happened (actual time, rows, loops).

-- Before (Postgres 17 output, trimmed)
Limit  (actual time=88.420..88.426 rows=20 loops=1)
  Buffers: shared hit=2113 read=6221
  ->  Sort  (actual time=88.418..88.421 rows=20 loops=1)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..20834.00 rows=51 width=97)
                                (actual time=0.381..88.301 rows=48 loops=1)
              Filter: (customer_id = 4821)
              Rows Removed by Filter: 999952
Execution Time: 88.471 ms

-- The fix, as its own migration (CONCURRENTLY cannot run in a transaction)
CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

-- After
Limit  (actual time=0.032..0.061 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_created_idx on orders
        (actual time=0.031..0.057 rows=20 loops=1)
        Index Cond: (customer_id = 4821)
Execution Time: 0.083 ms

Build indexes with CREATE INDEX CONCURRENTLY on a live table so writes keep going, in a migration of its own outside a transaction. Where migrations should run covers how Rails and Django handle that.

• Missing index on a filter or join column. Foreign keys are the usual gap: Postgres indexes the primary key, not the column that points to it. On Canner, canner db check lists unindexed foreign keys and duplicate indexes on every plan, from the catalog only.
• N+1 queries. One query for the list, then one per row. The fix is in the ORM: includes or preload in Rails, select_related/prefetch_related in Django, include in Prisma, or a join you write yourself.
• Deep `OFFSET` pagination. OFFSET 50000 reads and throws away 50,000 rows. Paginate by key instead: WHERE id < $last_seen ORDER BY id DESC LIMIT 20.
• A function on the column. WHERE lower(email) = $1 can’t use an index on email. Index the expression: CREATE INDEX ON users (lower(email)).
• Too much data per row. SELECT * on a table with big JSON or text columns moves all of it. Select the columns the page uses.
• Statistics out of date after a bulk load. Run ANALYZE table_name and explain again.

On Canner the counters run from when you turned monitoring on or last reset them, and the log keeps seven days. There is no long-term query history and no tracing from a slow request to the query behind it. If you need that, an APM tool in your app does it better.

On a database without the dedicated instance, pg_stat_statements is not available. EXPLAIN ANALYZE works anywhere you can run SQL, for example psql through external access, and canner db check still finds missing foreign-key indexes.