- How do I find missing indexes in Postgres?
- Start from
pg_stat_statements, ordered by total_exec_time. EXPLAIN each top query: a Seq Scan on a large table whose Filter removes most rows is the classic missing index. Then check every foreign key for an index whose leading columns match it — joins and cascading deletes on an unindexed key scan the whole referencing table.
- Is a high seq_scan count in pg_stat_user_tables a missing index?
- Not by itself. Small tables are cheaper to scan than to index, and an unselective filter scans either way. A missing index is a scan of many rows that a filter then throws away — which is why nlqdb judges each query by its rows per call, not the table's scan count.
- Does nlqdb run my queries or change my database to find indexes?
- No. It reads the catalog and
pg_stat_statements, and plans queries with EXPLAIN (GENERIC_PLAN) without executing them. Nothing changes until you apply a proposal, and every proposal carries the DROP INDEX that undoes it.
- What does an applied index look like on a real database?
- On the first real app Postgres nlqdb attached, its one workload proposal indexed a range lookup that scanned its whole table to return 0.01 % of it per call. Before: mean 7.7 ms, p95 estimate 65.2 ms. After 72 hours: mean 0.3 ms, p95 estimate 1.7 ms — so verify kept it.