nlqdb

Solve · Backend engineers

Which index does my slow Postgres query need?

Run EXPLAIN (ANALYZE, BUFFERS) on it. A Seq Scan whose Rows Removed by Filter dwarfs the rows it keeps needs a btree on the filter's columns: the = columns first, one range column last. Build it with CREATE INDEX CONCURRENTLY. Or run nlq attach: nlqdb plans your hot queries, proposes that index and measures each after.

EXPLAIN shows the Seq Scan but not the index that would end it. A composite index in the wrong column order is one the planner skips, an index on every column slows every write, and an unselective filter is faster scanned anyway. Then the index still has to be built without locking the table, and checked days later against the query it was for.

The command that solves it.

What nlqdb does for this

  • 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 sequential scan whose filter keeps at most 5 % of the table's rows becomes a candidate; an unselective filter is left to its scan.
  • It orders the key for you: = and = ANY columns first, one range column last; a filter an index already leads with is skipped.
  • nlq apply <id> runs CREATE INDEX CONCURRENTLY; 72 hours later a p95 estimate over 10 % worse runs its DROP INDEX inverse.

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 plans your hot queries the way you would by hand, with EXPLAIN and without running them, and turns each selective scan into one index proposal after a day of traffic.

Or attach in the browser →

What this replaces

What nlqdb doesn't try to do here

  • No EXPLAIN ANALYZE — nlqdb never runs your query, so it judges selectivity from the generic plan and, for a range bound by a parameter, the rows each call returned.
  • No index for an OR, a LIKE or full-text filter, an expression, two range columns or a partitioned parent — btree equality filters plus one range column only.
  • No covering (INCLUDE), partial or sort-order index — a proposal keys on the filter's columns, never on ORDER BY or the selected ones.

Questions buyers ask

Which index does my slow Postgres query need?
Run EXPLAIN (ANALYZE, BUFFERS). A Seq Scan whose Rows Removed by Filter is most of the table wants a btree on the filter's columns. Put the columns compared with = first and at most one range column (<, >, BETWEEN) last: equality on the leading keys plus a range on the next one is a single contiguous slice of the index.
Does column order matter in a Postgres composite index?
Yes. A btree on (account_id, created_at) serves WHERE account_id = $1 AND created_at > $2 as one index range, but a query that filters only created_at can't use its leading key and reads far more of it. Lead with the columns your queries filter by equality; end with the one they filter by range.
Why is Postgres not using my index?
Most often the filter isn't selective: when a query returns a large share of the table, a sequential scan is cheaper and the planner is right to choose it. Otherwise look for a function or cast on the column (lower(email) needs an expression index), a leading index column the query never filters, or stale statistics — run ANALYZE.
What does an applied index look like on a real database?
On the first real app Postgres nlqdb attached, a range lookup scanned a ~10,200-row table to return 0.01 % of it per call. nlqdb proposed an index on the range column; after 72 hours the query's mean fell from 7.7 ms to 0.3 ms and its p95 estimate from 65.2 ms to 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.