Postgres performance and index health

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

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

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 <object-type>:<schema-qualified-object>:<issue-slug>, 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

• 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

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.