---
title: "Index the queries you run · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/queries-and-indexes/index-the-queries-you-run
updated: 2026-10-20
licence: "All rights reserved"
---

# Index the queries you run

By Erik Bergstedt, Agent Wranglers. Updated 2026-10-20.

- Level: Required
- Chapter: [Queries and indexes](https://agentwranglers.se/playbook/data/queries-and-indexes)
- Rule: Add an index for each frequent query's filter, join and sort, and no index that no query uses.
- How: Index foreign key columns, because PostgreSQL and SQLite don't do it for you. MySQL's InnoDB does. Put equality columns first in a composite index and the range or sort column last. Each index slows every write. Drop one only after its usage statistics show zero scans over a full business cycle, such as a month. In PostgreSQL, build indexes on live tables with `CREATE INDEX CONCURRENTLY`, which cannot run inside a transaction.
- Check: Run the 20 most frequent queries with `EXPLAIN` on production-sized data. None does a full scan of a table with more than 10,000 rows, and the unused index query returns no rows.

Example:

```sql
-- Serves: WHERE customer_id = $1 ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY orders_customer_created ON orders (customer_id, created_at DESC);
-- PostgreSQL: indexes never scanned since the statistics were reset
SELECT indexrelid::regclass FROM pg_stat_user_indexes s JOIN pg_index i USING (indexrelid)
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary;
```

**For agents:** if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in `Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/queries-and-indexes/index-the-queries-you-run`.

- Previous: [Pass values as parameters](https://agentwranglers.se/playbook/data/queries-and-indexes/parameters)
- Next: [Read the query plan](https://agentwranglers.se/playbook/data/queries-and-indexes/query-plans)
- [Queries and indexes](https://agentwranglers.se/playbook/data/queries-and-indexes)