“The app feels slow” is not a starting point. Postgres already knows exactly which query is costing you the most time — you just have to ask it, instead of guessing at indexes.

The debugging path, start to finish:

flowchart TD
    A["pg_stat_statements:\nfind worst query by total_exec_time"] --> B["EXPLAIN (ANALYZE, BUFFERS)"]
    B --> C{Seq Scan on large table?}
    C -->|Yes| D["CREATE INDEX CONCURRENTLY"]
    D --> E[Re-run EXPLAIN ANALYZE to confirm]
    C -->|No, plan looks fine| F["Check pg_stat_activity\nfor lock contention"]
  

Turn on pg_stat_statements

This extension tracks execution stats for every distinct query Postgres has run, aggregated. If it’s not already on:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Restart Postgres after that config change, then:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Find your worst offenders

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

total_exec_time — cumulative time across all calls — is usually the better sort key than mean_exec_time. A query that runs in 5ms but fires 500,000 times a day can cost more total database time than a query that runs in 2 seconds but only fires twice. Fix the total cost, not the scariest-looking single number.

Reset the stats after a deploy if you want to measure the impact of a fix cleanly:

SELECT pg_stat_statements_reset();

Once you have the query, get its actual plan

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';

ANALYZE actually runs the query and shows real timings, not estimates — use this in a read replica or off-peak if the query is expensive and you’re worried about load. BUFFERS shows how much of the work was cache hits vs. disk reads, which tells you whether you have a memory problem or an indexing problem.

Read the output bottom-up. The innermost operations run first. What you’re looking for:

Seq Scan on orders  (cost=0.00..48291.00 rows=1 width=120)
  (actual time=0.02..312.44 rows=1 loops=1)
  Filter: (customer_id = 4821 AND status = 'pending')
  Rows Removed by Filter: 998234

Seq Scan scanning a million rows to filter down to one is your answer, almost every time. Postgres read the entire table because nothing told it a faster path existed.

The fix, usually

CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

Always use CONCURRENTLY in production — a regular CREATE INDEX takes a lock that blocks writes to the table for the whole build, which on a large table can mean minutes of blocked inserts. CONCURRENTLY takes longer but doesn’t block writes. It does have one gotcha: if it fails partway through, it leaves behind an invalid index that silently isn’t being used. Check pg_indexes / \d tablename after a CONCURRENTLY build to confirm it actually completed successfully.

Re-run EXPLAIN ANALYZE after adding the index and confirm you now see Index Scan instead of Seq Scan, and that the actual time dropped. Don’t assume the index helped — verify it.

When the plan looks fine but it’s still slow

Sometimes EXPLAIN ANALYZE shows a reasonable plan but the query is still slow because of lock contention, not query structure. Check for blocking queries:

SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

A query waiting on a lock isn’t a query-planning problem — it’s a transaction somewhere else holding a lock too long. Find and fix that transaction instead of tuning the waiting query.

The takeaway

pg_stat_statements finds the expensive query by total cost, not vibes. EXPLAIN (ANALYZE, BUFFERS) shows exactly why it’s slow — almost always a sequential scan where an index should be. Add the index CONCURRENTLY, then verify the plan actually changed before you move on.