Indexing Like a Neo: Dodging Slow Queries in The Matrix

The Quest Begins (The "Why")

Honestly, I was staring at a dashboard that looked like a Neo‑bullet‑time freeze frame—everything was moving in slow motion while my users screamed for speed. We had a simple rate‑limiter service built on top of PostgreSQL, and every request hit a SELECT COUNT(*) FROM requests WHERE user_id = $1 AND created_at > now() - interval '1 minute'. The table had grown to a few million rows, and that query was taking seconds instead of milliseconds. I felt like I was stuck in a loop, trying to outrun Agents that kept getting smarter.

I knew I needed to rewire the way the database found those rows, but I didn’t want to just slap on an index and hope for the best. I wanted to understand why a particular index would turn that sluggish scan into a lightning‑fast lookup. So I embarked on a mini‑adventure: dissect the query, examine the data, and pick the right index like Neo choosing the red pill.

The Revelation (The Insight)

Here’s the thing: indexes aren’t magic blankets that make everything faster; they’re precise tools that turn a full table scan into a narrow range scan. The critical insight for my rate‑limiter was that the query’s WHERE clause filtered on two columns: user_id (equality) and created_at (range). PostgreSQL can only use an index efficiently if the leading columns match equality predicates, followed by at most one range column.

So the optimal index isn’t just (user_id) or (created_at). It’s a compound index where the equality column comes first: (user_id, created_at). With that layout, PostgreSQL can:

1. Jump directly to the slice of the index for a given user_id (thanks to the equality).
2. Then walk forward through the created_at values until it hits the time boundary (the range).

If I reversed the order—(created_at, user_id)—the engine would have to scan all rows in the last minute across all users before it could filter by user_id, which defeats the purpose. And if I added extra columns after the range column, they’d be useless for this query because the scan would already have stopped at the range bound.

That realization felt like discovering the hidden doorway in the Matrix—once you see it, the world changes.

ASCII diagram of the index layout

(user_id, created_at) index (simplified)

+----------+---------------------+
| user_id  | created_at          |
+----------+---------------------+
| 1001     | 2025-09-24 12:00:01 |
| 1001     | 2025-09-24 12:00:03 |
| 1001     | 2025-09-24 12:00:07 |
| 1002     | 2025-09-24 12:00:02 |
| 1002     | 2025-09-24 12:00:05 |
| ...      | ...                 |
+----------+---------------------+

For a given user_id = 1001, PostgreSQL jumps to the first row with that ID and then scans only the subsequent rows until created_at exceeds the one‑minute window.

Wielding the Power (Code & Examples)

The Struggle – No Proper Index

-- Table definition (simplified)
CREATE TABLE requests (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    endpoint    TEXT
);

-- The slow query
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM requests
WHERE user_id = 42
  AND created_at > now() - interval '1 minute';

Output (before index):

Aggregate  (cost=... rows=1 width=8) (actual time=... rows=1 loops=1)
  ->  Seq Scan on requests  (cost=0.00...... rows=... width=0)
        Filter: ((user_id = 42) AND (created_at > ...))
        Rows Removed by Filter: 3,200,000
Planning Time: 0.12 ms
Execution Time: 1450.3 ms

A full sequential scan over three million rows—ouch.

The Victory – Adding the Compound Index

-- The index that matches our query pattern
CREATE INDEX idx_requests_user_created
    ON requests (user_id, created_at);

Output (after index):

Aggregate  (cost=... rows=1 width=8) (actual time=... rows=1 loops=1)
  ->  Index Scan using idx_requests_user_created on requests
        (cost=0.42..8.44 rows=10 width=0) (actual time=0.020..0.025 rows=10 loops=1)
        Index Cond: ((user_id = 42) AND (created_at > ...))
Planning Time: 0.09 ms
Execution Time: 0.04 ms

Boom! Execution time dropped from 1.5 seconds to 0.04 ms—a ~37,000× speedup. The index scan touches only the rows that actually belong to the user and fall inside the time window.

Common Traps to Avoid

Trap	What it looks like	Why it hurts
Index on only created_at	CREATE INDEX ON requests (created_at);	The engine still scans all rows in the last minute for every user, then filters by user_id.
Reverse column order	CREATE INDEX ON requests (created_at, user_id);	The leading column is a range, so PostgreSQL can’t use the equality on user_id to jump directly; it ends up scanning a large range anyway.
Over‑indexing	Adding indexes on every column hoping “something will help”	Each index costs write overhead and storage; unused indexes just slow down inserts/updates.

Why This New Power Matters

With that single compound index in place, our rate‑limiter went from a bottleneck that threatened to melt under load to a nimble gatekeeper that could handle thousands of requests per second without breaking a sweat. The same principle applies anywhere you have equality plus range filters: think equality first, range second.

Beyond rate limiters, this insight unlocked faster leaderboards, real‑time analytics dashboards, and even our internal audit logs. It’s a small change in schema design that yields massive returns in latency and throughput—exactly the kind of leverage a developer loves to wield.

A Quick Challenge

Grab a table in your own project that runs a query like:

SELECT *
FROM orders
WHERE customer_id = ?
  AND order_date BETWEEN ? AND ?;

Run EXPLAIN ANALYZE on it, then try adding an index (customer_id, order_date). Compare the before/after times. Share your results in the comments—I’d love to see how deep the rabbit hole goes for you!

Now go forth, index wisely, and may your queries always be as swift as Neo dodging bullets. 🚀