# Postgres performance and index health > Slow query patterns, missing and unused indexes, table bloat, and connection posture on a live Postgres database. Source: https://triagic.com/checkups/postgres-performance ## Scope [#scope] A read-only performance review of one Postgres database using the statistics collector and catalog views. `SELECT` and `EXPLAIN` only — never `EXPLAIN ANALYZE` (it executes the statement), never `CREATE INDEX`, `VACUUM`, `REINDEX`, or `pg_stat_reset()`. Every recommendation is a proposal for a human to schedule, because index builds and vacuums have production cost. ## Procedure [#procedure] 1. **Establish the baseline and the window.** `SELECT version()`, then `SELECT * FROM pg_stat_database WHERE datname = current_database()` for `stats_reset`, `xact_commit`/`xact_rollback`, `blks_hit`/`blks_read` (cache hit ratio: below \~0.99 on an OLTP database usually means `shared_buffers` is too small for the working set), `deadlocks`, and `temp_files`/`temp_bytes` (temp file volume means `work_mem` is too small for the sorts and hashes actually running). Every counter below is cumulative since `stats_reset`, so state that date in the report — a "top query" over three hours and over three months are different claims. Check whether `pg_stat_statements` is installed: `SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements'`. 2. **Query patterns.** With `pg_stat_statements`: rank by `total_exec_time DESC` for aggregate load, and separately by `mean_exec_time DESC` (filtered to `calls` above a floor such as 100, so a single slow migration does not top the list) for per-call pain. Those two columns are named `total_time` and `mean_time` before Postgres 13, so use the `version()` read in step 1 — or the column list of `pg_stat_statements` itself — and fall back to the old names on 12 and earlier rather than letting the query fail on a missing column. For the top patterns read `calls`, `rows`, `shared_blks_hit` vs `shared_blks_read`, and `temp_blks_written`. A statement with enormous `calls` and a tiny `mean_exec_time` is usually an N+1 in the application, and it can outweigh every genuinely slow query on the list — check for that shape explicitly. Without the extension, fall back to `pg_stat_activity` sampling plus `pg_stat_user_tables.seq_scan` and `seq_tup_read`, and say in the report that query-level attribution was unavailable. 3. **Explain the worst offenders.** For the top few statements, run plain `EXPLAIN` (never `ANALYZE`) on the parameterized form with representative literals. Look for sequential scans on large tables, nested loops over large estimated row counts, and estimates that are wildly off the table's real size — the last usually means stale statistics, which is a `last_analyze` question, not an index question. 4. **Indexes, both directions.** From `pg_stat_user_indexes` joined to `pg_index`: any index with `idx_scan = 0` since `stats_reset` is dead weight — it is written on every insert and update and read by nobody. Exclude unique and primary-key indexes and any index backing a constraint or a foreign key, which exist for correctness rather than reads; call that exclusion out in the report so nobody drops a constraint index on this checkup's advice. Use `pg_relation_size()` to size each candidate so the report can rank them. In the missing direction, use `pg_stat_user_tables`: high `seq_scan` with high `seq_tup_read` on a large table, and `idx_scan` near zero, is a table being scanned for want of an index. Also detect exact-duplicate and left-prefix-redundant indexes (an index on `(a)` is redundant when `(a, b)` exists) by comparing `indkey` column lists per table. Check for unused foreign-key columns with no supporting index, which makes cascading deletes and joins scan. 5. **Bloat and autovacuum.** From `pg_stat_user_tables`: `n_dead_tup` against `n_live_tup` per table. A ratio above \~20% on a large, actively-read table is real bloat — it inflates every scan and the buffer cache. Read `last_vacuum`, `last_autovacuum`, `last_analyze`, `last_autoanalyze`, and `autovacuum_count`: a table with a large `n_dead_tup` and a `last_autovacuum` of null or weeks old means autovacuum is not keeping up, usually because of per-table `autovacuum_vacuum_scale_factor` defaults on a very large table. Check for long-lived transactions in `pg_stat_activity` holding back the vacuum horizon (see the next step) — they are frequently the actual cause of bloat, and recommending a manual `VACUUM` without finding them fixes nothing. 6. **Connection posture.** `SELECT count(*), state FROM pg_stat_activity GROUP BY state` against `SHOW max_connections`. Flag: total connections above \~70% of `max_connections` (no headroom for a failover or a deploy); any session in `idle in transaction` for more than a few minutes (it pins the vacuum horizon and can hold locks); `idle in transaction (aborted)` sessions at all. Read `state_change`, `xact_start`, `wait_event_type` and `wait_event` for the worst offenders, and `backend_type`/`application_name` to attribute them to a service. Check whether a pooler is in front: hundreds of mostly-idle application connections is a pooling finding, not a `max_connections` finding. 7. **Locks, only if the evidence points there.** If step 6 shows waiting sessions, read `pg_locks` joined to `pg_stat_activity` to identify the blocking chain. Report the pattern (which statement blocks which), not a one-off snapshot. 8. **Attribute everything.** Name the view behind every number, and give the `stats_reset` window once at the top of the report. Where the connected role lacks visibility (`pg_stat_statements` not installed, other users' query text hidden without `pg_read_all_stats`), say what that leaves unverified. ## Finding keys [#finding-keys] The `key` names the *underlying issue* so the ledger can track it across runs: the same unused index must produce the same key every month, so a run where it is absent means it was actually dropped. Use `::`, and never embed a timing, a row count, or a date — those change every run and belong in `detail` and `evidence`. * `index:public.users_email_idx:unused` * `table:public.orders:bloat` * `table:public.events:missing-index` (or `table:public.events:seq-scan-heavy`) * `query:3f8a91c4:slow` — the `pg_stat_statements.queryid`, not the text * `connection:api-service:idle-in-transaction` * `config:max_connections:no-headroom` ## Severity rubric [#severity-rubric] * **critical** — the database is at or near a hard limit right now: connections above 90% of `max_connections`, transaction-ID wraparound risk visible in `age(datfrozenxid)`, or a blocking chain stalling user-facing writes. * **high** — one query pattern responsible for more than a quarter of total execution time; a large hot table with autovacuum demonstrably not keeping up; an `idle in transaction` session hours old; cache hit ratio well below 0.99 on an OLTP workload. * **medium** — a large unused index worth dropping; a missing index on a table taking heavy sequential scans; 20–40% dead-tuple ratio; measurable temp-file volume pointing at `work_mem`. * **low** — small unused or duplicate indexes; stale `ANALYZE` on a low-traffic table; cosmetic schema issues with no measured cost. * **info** — healthy readings worth recording as a baseline for the next run, or a new large table to watch. ## Output guidance [#output-guidance] Open with a one-paragraph executive summary: the statistics window (`stats_reset` date), overall health in plain words, and the one change with the largest expected effect. Then sections for Query patterns, Indexes, Bloat and autovacuum, and Connections and locks, each naming the view behind its numbers. Close with a recommendations table (Recommendation | Object | Expected effect | Production risk | When to run it) — index builds and vacuums carry real cost, so every row must state its risk and whether it needs a maintenance window. Never present a proposed index as free. State outright when the database is healthy.