Database Indexing: Why Queries Slow Down and How to Fix Them

Most query performance problems are indexing problems. Either the index is missing, the index exists but the query can't use it, or there are too many indexes slowing down writes. Understanding how indexes work — not just that they exist — is what lets you fix these problems without guessing.

How B-Tree Indexes Work

PostgreSQL's default index type is a B-tree (balanced tree). It stores index values in sorted order, which makes range queries, equality checks, and ORDER BY efficient.

When you query WHERE email = 'user@example.com', PostgreSQL traverses the B-tree from root to leaf in O(log n) steps, rather than scanning every row. For a table with 10 million rows, that's about 23 comparisons instead of 10 million.

B-trees support: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%' (prefix matching only — LIKE '%suffix' can't use a B-tree).

Hash Indexes

Hash indexes are faster for equality lookups (= only) but don't support range queries. In PostgreSQL, B-tree indexes are usually fast enough for equality that hash indexes are rarely worth the trade-off. The main use case for hash indexes in Postgres is when you know the column will only ever be queried for exact equality and the key is very long (like a UUID or SHA-256 hash), where the hash index stores a fixed-size hash rather than the full value.

The Column Order Problem in Composite Indexes

A composite index on (userId, createdAt) can satisfy:

• WHERE userId = ?
• WHERE userId = ? AND createdAt > ?
• ORDER BY userId, createdAt

But it cannot efficiently satisfy WHERE createdAt > ? alone — the index is sorted by userId first. The leftmost column must appear in the query for the index to be used.

This means the order of columns in a composite index matters enormously:

-- For queries that filter by userId and sort by createdAt:
CREATE INDEX idx_posts_user_date ON posts (userId, createdAt DESC);
 
-- For queries that filter by status and also filter by userId:
CREATE INDEX idx_posts_status_user ON posts (status, userId);

Think about the selectivity (how many rows are filtered out) of each column. Put the higher-selectivity column first if you're doing range scans; put the equality-filter column first if you're filtering on it constantly.

Partial Indexes

A partial index covers only rows matching a condition. If 95% of your orders table has status = 'completed' and you almost never query completed orders, an index on all orders wastes space and slows writes.

-- Index only active/pending orders
CREATE INDEX idx_orders_active ON orders (createdAt DESC)
WHERE status IN ('pending', 'processing');

This index is smaller (only the active rows), faster for write operations (fewer index entries to maintain), and just as fast for queries that filter on the same condition.

In NodWatch's schema, watch_progress has millions of rows, but completed = false rows are what "Continue Watching" queries. A partial index on completed = false would be much smaller than a full index.

Covering Indexes

A covering index includes all columns the query needs, so PostgreSQL can return results from the index alone without touching the table rows. This is called an "index-only scan."

-- Query: SELECT title, posterPath FROM watchlist WHERE userId = ?
-- Covering index:
CREATE INDEX idx_watchlist_cover ON watchlist (userId) INCLUDE (title, posterPath);

The INCLUDE clause adds the columns to the leaf nodes of the index without using them for sorting. An index-only scan is faster than a regular index scan because it avoids a second lookup per row.

When Indexes Hurt

Write performance. Every index must be updated on every INSERT, UPDATE, or DELETE. A table with 15 indexes has 15 tree updates per write. For write-heavy tables (event logs, audit trails), fewer indexes is often better.

Dead code indexes. Indexes that no query uses still slow down writes and consume space. Use pg_stat_user_indexes to find unused indexes:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND idx_scan = 0;

Too many choices. If a table has many indexes and PostgreSQL's query planner chooses the wrong one, EXPLAIN ANALYZE will show it. You can hint with SET enable_seqscan = off during debugging or use /*+ IndexScan(table idx_name) */ hints in some configurations.

Drizzle Index Definition

In Drizzle ORM, indexes are defined in the table definition:

export const watchProgress = pgTable('watch_progress', {
  userId: text('userId').notNull(),
  tmdbId: integer('tmdbId').notNull(),
  completed: boolean('completed').default(false),
  updatedAt: timestamp('updatedAt').defaultNow(),
}, (table) => ({
  userIdx: index('wp_user_idx').on(table.userId),
  activeIdx: index('wp_active_idx').on(table.userId, table.updatedAt.desc())
    .where(sql`completed = false`),
}));

Run drizzle-kit generate to produce the migration SQL, then drizzle-kit migrate to apply it.