Adam - How I Stopped Writing Slow Queries - A Deep Dive into PostgreSQL Indexes

TL;DR

EXPLAIN ANALYZE shows you the actual plan and timing - read it before guessing.
A B-tree index turns an O(n) sequential scan into an O(log n) lookup.
Composite index column order matters - leftmost-prefix rule.
I took a dashboard query from 2.3s → 4ms with one composite index.

The wake-up call

TaskFlow's dashboard query had quietly gotten slow. With ~200k tasks in my seed data, loading a user's board took 2.3 seconds. In dev with 50 rows I never noticed. That's the trap: everything is fast until it isn't, and the difference is whether you tested at scale.

Step one: read the plan, don't guess

I used to optimize by vibes. Then I learned to ask Postgres what it was actually doing:

EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE project_id = 42 AND status = 'in_progress'
ORDER BY created_at DESC
LIMIT 20;

The output was the smoking gun:

Seq Scan on tasks  (cost=0.00..8214.00 rows=312 width=148)
  Filter: (project_id = 42 AND status = 'in_progress')
  Rows Removed by Filter: 199688
Execution Time: 2301.4 ms

Seq Scan + Rows Removed by Filter: 199688 means Postgres read every single row and threw almost all of them away. No index, no shortcut.

Why a B-tree fixes this

A B-tree index is a balanced, sorted tree. Instead of scanning 200k rows, Postgres walks down the tree in O(log n) to find matching rows directly. For 200k rows that's roughly 18 comparisons instead of 200,000.

But which index? The naive move is one index per column. The right move is a composite index that matches the query's access pattern:

CREATE INDEX idx_tasks_project_status_created
ON tasks (project_id, status, created_at DESC);

The leftmost-prefix rule (the part that took me a while)

Column order in a composite index isn't arbitrary. A B-tree on (project_id, status, created_at) can serve queries filtering on:

project_id
project_id + status
project_id + status + created_at

…but not status alone, because the index is sorted by project_id first. It's like a phone book sorted by last name then first name - useless for finding everyone named "Adam" regardless of surname.

So I put the equality filters first (project_id, status), then the range/sort column (created_at DESC) last so the ORDER BY ... LIMIT can read straight off the index without a separate sort step.

The result

Index Scan using idx_tasks_project_status_created on tasks
  (cost=0.42..16.88 rows=20 width=148)
Execution Time: 4.1 ms

Seq Scan → Index Scan. 2301ms → 4ms, a ~560x improvement, and the ORDER BY disappeared because the index was already sorted.

Other things I picked up

Covering indexes (INCLUDE): add non-filtered columns to the index so Postgres never touches the table heap - an "index-only scan."
Partial indexes: WHERE status = 'in_progress' in the index definition keeps it tiny if you only ever query active tasks.
The N+1 problem: my ORM was firing one query per task to load assignees. A single JOIN (or Prisma include) collapsed 200 queries into 1.
Indexes aren't free: every INSERT/UPDATE has to maintain them. Don't index columns you never filter on.

Takeaways

Measure at realistic scale. 50 rows hides every performance bug.
EXPLAIN ANALYZE first, optimize second. The plan tells you the truth.
Composite index order = equality columns, then range/sort columns.

My reads are fast now, but I'm still hitting the database for data that barely changes. Time to put a cache in front of it - learning Redis.