nlqdb

Solve · Backend engineers

How do I find missing indexes in Postgres?

Rank pg_stat_statements by total time and EXPLAIN the top queries: a Seq Scan whose Filter keeps few rows of a big table is a missing index. So is a foreign key no index leads with. Or run nlq attach: nlqdb makes both checks, proposes each CREATE INDEX CONCURRENTLY, and measures it after.

Postgres never says an index is missing. pg_stat_user_tables counts sequential scans but not which query ran them, pg_stat_statements ranks queries but keeps no plans, and every 'missing index' script you find is a heuristic you rerun by hand. Then comes the part nobody automates: build the index without blocking writes, and check days later that the slow query actually got faster.

The command that solves it.

What nlqdb does for this

  • nlq attach reads the catalog and proposes an index for every foreign key no index leads with — seconds after attach, no extension needed.
  • With pg_stat_statements on Postgres 16+, it diffs a 24-hour window and plans each hot query with EXPLAIN (GENERIC_PLAN), never executing it.
  • A selective sequential-scanned filter becomes one CREATE INDEX CONCURRENTLY proposal with its DROP INDEX inverse; nlq apply <id> builds it without blocking writes.
  • 72 hours later it re-reads that query's counters; a p95 estimate over 10 % worse runs the inverse, with no action from you.

Run in your terminal

curl -fsSL https://nlqdb.com/install | sh
export NLQDB_API_KEY=sk_live_... # from nlqdb.com/app/keys
echo "$DATABASE_URL" | nlq attach

Attach reads the catalog, so unindexed foreign keys come back as proposals in seconds; the workload's missing indexes follow after one day of traffic.

Or attach in the browser →

What this replaces

What nlqdb doesn't try to do here

  • No exact p95 — pg_stat_statements keeps no percentiles, so nlqdb estimates p95 as mean + 1.65 σ over the window and labels it an estimate everywhere.
  • No index for a partitioned parent, a LIKE or full-text filter, or an expression — the proposals cover btree equality filters plus at most one range column today.
  • No anonymous attach — your connection string is a credential, so attach needs a signed-in account; it is sealed server-side and never printed back.

Questions buyers ask

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.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Attach your database

Sign in, hand nlqdb a Postgres connection string or a D1's account id, database id and API token, and see the first proposals. Nothing runs on your database until you apply one.

Attach a database →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.