Database Indexing Strategies in Depth

Database slowness is caused by indexing problems 90% of the time; get the indexes right, and 90% of slow queries disappear. But "adding an index" involves far more than writing CREATE INDEX idx_x ON t(col). Anyone can write that statement. A senior engineer can explain why one index is ten times faster than another, when to use GIN, and when not to add any index at all.

1. Indexes Are the Primary Performance Lever, but Must Be Designed Around Query Patterns

Start with two fundamental principles.

First, an index trades write performance for read performance. Every index you add means maintaining another B-tree on every INSERT, UPDATE, and DELETE. More indexes are not necessarily better; more precise indexes are. Precision means matching actual query patterns exactly, with neither too much nor too little.

Second, indexes are designed around queries, not tables. Do not begin by indexing every column. The correct workflow runs in the opposite direction: identify a slow query, analyze its WHERE, ORDER BY, and JOIN clauses, and then design an index specifically for it. One index tailored to a query can outperform ten single-column indexes added by guesswork.

The only way to determine whether an index is being used is EXPLAIN. Tuning without inspecting the execution plan is like driving blindfolded. Here is a typical example of an index eliminating a sequential scan:

-- An orders table with 10 million rows
EXPLAIN SELECT * FROM orders WHERE user_id = 88123;

-- Without an index:
-- Seq Scan on orders  (cost=0.00..234511.00 rows=1)
-- Full-table scan with enormous disk I/O

CREATE INDEX idx_orders_user_id ON orders(user_id);

EXPLAIN SELECT * FROM orders WHERE user_id = 88123;
-- Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=12)
-- The cost drops from 230,000 to 8. That is the power of the primary performance lever.

2. Composite Indexes and the Leftmost-Prefix Rule

Complex queries rarely filter on only one column, which is where composite, or multicolumn, indexes come in. The leftmost-prefix rule is the key to using them correctly:

A composite index on (a, b, c) can serve these queries:

• WHERE a = ? ✅
• WHERE a = ? AND b = ? ✅
• WHERE a = ? AND b = ? AND c = ? ✅
• WHERE b = ? ❌ (it skips a, so the index cannot be used)
• WHERE b = ? AND c = ? ❌
• WHERE a = ? AND c = ? ⚠️ (only the a portion can be used)

Why? Because the B-tree is organized first by a, then by b among rows with the same a, and then by c. If you skip a and search directly by b, b is not ordered across the tree, so the index cannot help.

How should you order the columns? Remember one golden rule: equality → range → ordering.

-- Typical query: a user's orders from the last 30 days, newest first
SELECT * FROM orders
WHERE user_id = 88123           -- Equality
  AND created_at > '2026-06-17' -- Range
ORDER BY created_at DESC;       -- Ordering

-- Correct index: equality column first, range column second;
-- the ordering comes along for free
CREATE INDEX idx_orders_user_time ON orders(user_id, created_at);

-- Index Scan Backward using idx_orders_user_time on orders
-- One index handles both filtering and ordering, eliminating the Sort operator

What happens if created_at comes first? WHERE created_at > X matches every user's data across a large time range, giving the index poor selectivity, while the equality filter on user_id cannot benefit from the index ordering. Equality columns come first because they quickly narrow the index to a small contiguous range, allowing subsequent columns to continue narrowing within that range. Once a range column opens the floodgates, columns after it can only be scanned row by row within the result set and provide limited help with ordering. That is why the range column should come last.

A common mistake is to create an index for every possible column ordering: (a,b,c), (a,c,b), (b,a,c), and so on. This is disastrous. Use pg_stat_user_indexes in PostgreSQL or sys.schema_unused_indexes in MySQL to see which indexes are actually used, and remove the unused ones decisively.

3. Covering Indexes: Avoiding Heap Fetches

A regular secondary index stores the "indexed columns + row pointer (primary key)." After locating a row through the index, if the query needs other columns, the database must use the primary key to look it up again in the primary-key index or heap. This is called a heap fetch. Heap fetches are random I/O: they are expensive even on SSDs, devastating on HDDs, and can amplify lock contention under high concurrency.

The idea behind a covering index is to put every column needed by the query into the index. The index scan then returns all required data without visiting the table.

PostgreSQL implements this cleanly and conservatively with INCLUDE:

-- Query: retrieve only the ID, email address, and nickname from the user list
SELECT id, email, nickname FROM users WHERE email = '[email protected]';

-- Regular index: still requires a heap fetch for nickname after a match
CREATE INDEX idx_users_email ON users(email);

-- Covering index: attach nickname to the index to avoid the heap fetch
CREATE INDEX idx_users_email_cover ON users(email) INCLUDE (nickname);

EXPLAIN SELECT id, email, nickname FROM users WHERE email = '[email protected]';
-- Index Only Scan using idx_users_email_cover on users
-- Note the Index **Only** Scan: no heap fetch

INCLUDE columns exist only in leaf nodes. They do not participate in ordering or consume space in internal nodes, so adding them barely increases index depth while potentially eliminating heap fetches entirely. This is one of PostgreSQL 11+'s most underrated features.

MySQL InnoDB secondary indexes already include the primary key, making covering indexes especially cost-effective. MySQL has no INCLUDE, so columns must be added directly to the index key list, as in INDEX(email, nickname). The tradeoff is that those columns participate in ordering and may affect selectivity. The tradeoffs differ between the two databases, but the objective is the same: let the index answer the query by itself.

4. Partial Indexes: Small and Fast

Often, only a small subset of a table matters. For example, perhaps only users with status = 'active' are queried frequently, while inactive users make up 90% of the table but are almost never queried. An index on email across the entire table would waste 90% of its entries while continuously charging you for their writes.

A partial index uses a WHERE clause to restrict the index to a meaningful subset:

-- Index only active users
CREATE INDEX idx_users_active_email ON users(email)
WHERE status = 'active';

-- Index only the last 30 days of a large table
CREATE INDEX idx_orders_recent ON orders(created_at)
WHERE created_at > '2026-06-17';

-- Index only unshipped orders, which are the business hotspot
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';

The benefits are twofold: the index is smaller, saving storage and improving cache hit rates, and writes are cheaper because rows that do not satisfy the WHERE clause never enter the index. For the common business pattern of "a small hot subset plus a large amount of cold data"—which describes most applications—partial indexes offer exceptional value.

Important: the query's WHERE clause must logically imply the index's WHERE clause before the optimizer can use it. WHERE status = 'active' AND email = ? can use the index above; WHERE email = ?, without the status condition, cannot. The index must align strictly with real queries.

5. Expression and Function Indexes

Here is a classic pitfall: you create an index on name, but the query uses WHERE LOWER(name) = 'alice'. The index is completely unusable. The B-tree stores the original value of name, while the query compares LOWER(name), which is a different expression and is not stored in the tree.

The solution is to index the expression itself.

-- Case-insensitive lookup
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
SELECT * FROM users WHERE LOWER(name) = 'alice';  -- ✅ Uses the index

-- Case-insensitive email lookup
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- JSON extraction
CREATE INDEX idx_events_type ON events((metadata->>'type'));

The core rule is simple: the WHERE clause must use exactly what the index stores. As long as the expressions in the index definition and query match exactly, the optimizer can recognize them.

MySQL was historically weaker in this area, but MySQL 8.0 added function indexes such as CREATE INDEX idx ON t ((LOWER(name))). It also supports the workaround of indexing a generated column. PostgreSQL has always been flexible and can index any immutable expression. However, the function must be IMMUTABLE: LOWER is, while NOW() is not, because its result changes over time and cannot produce a stable index.

6. Specialized Indexes: GIN, GiST, and BRIN

B-tree is not the only option. For nonscalar data, specialized index types can improve performance by several orders of magnitude.

GIN (Generalized Inverted Index) is an inverted index optimized for queries where "one value contains multiple elements":

-- jsonb containment query
CREATE INDEX idx_events_meta ON events USING GIN (metadata);
SELECT * FROM events WHERE metadata @> '{"type":"signup"}';

-- Array intersection
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags && ARRAY['rust','wasm'];

-- Full-text search
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('english', body));

GIN sacrifices write speed for extremely fast queries, making it suitable for read-heavy workloads and element lookups. Building the index is slow and updates are expensive, but queries can drop from full-table scans to millisecond execution times.

GiST (Generalized Search Tree) excels at ranges, geometry, nearest-neighbor searches, and other operations that are not purely ordered:

-- Time-range overlap for detecting meeting-room booking conflicts
CREATE INDEX idx_bookings_range ON bookings
  USING GIST (tsrange(start_at, end_at));
SELECT * FROM bookings
WHERE tsrange(start_at, end_at) && tsrange('2026-07-17 14:00','2026-07-17 15:00');

-- Nearby locations with PostGIS
CREATE INDEX idx_shops_geo ON shops USING GIST (location);
SELECT * FROM shops ORDER BY location <-> 'POINT(121.47 31.23)' LIMIT 10;

BRIN (Block Range Index) is remarkably effective for enormous, physically ordered tables. Instead of storing an entry for every row, it stores min/max values for each range of physical blocks. Its size may be only one-thousandth that of a B-tree, at the cost of lower precision and a focus on range scans:

-- An append-only log table that is naturally ordered by time
CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (ts);
-- On a table with 1 billion rows, a B-tree index might occupy 20GB,
-- while BRIN needs only tens of MB

Choose based on the shape of both the data and the query: scalar equality/range → B-tree; jsonb/arrays/full text → GIN; ranges/geometry/nearest neighbor → GiST; enormous ordered append-only tables → BRIN.

7. Indexes Are Not Free: Write Costs and Maintenance

After covering the benefits, we must be equally clear about the costs. Understanding them is one of the biggest distinctions between junior and experienced engineers.

Write amplification: every index is an independent B-tree. If a table has five indexes, one INSERT actually writes to six places: the heap plus five trees. If an UPDATE changes indexed columns, every relevant index must also be updated. Adding indexes indiscriminately to a write-heavy table can make TPS collapse.

Storage cost: indexes consume real disk space. A 100GB table with five indexes may require more than 200GB in total. The same applies to memory: indexes must remain in the buffer cache to be effective. The more indexes you have, the less cache each receives, potentially reducing the overall hit rate.

Maintenance and fragmentation: repeated insertions and deletions leave gaps, or bloat, in a B-tree, slowing queries and wasting space. You need periodic VACUUM / REINDEX in PostgreSQL or OPTIMIZE TABLE in MySQL. PostgreSQL 12+'s REINDEX CONCURRENTLY can rebuild an index online without locking the table, making it essential in production.

Practical recommendations:

• Keep the number of indexes on a table to five or six at most. Anything beyond that deserves scrutiny.
• Always remove redundant indexes. If (a,b,c) already exists, (a,b) and (a) are redundant because the leftmost prefix already covers them.
• Inspect idx_scan in pg_stat_user_indexes. Remove indexes that remain at zero for a long time: they are written but never read, making them pure overhead.
• On write-heavy, read-light tables, fewer indexes are better. On read-heavy, write-light reporting or search tables, you can be more aggressive.

8. Design Workflow: From a Slow Query to an Index

Now combine everything above into a reusable workflow. This is what you should actually do in production:

1. Find slow queries. Enable pg_stat_statements in PostgreSQL or the slow query log in MySQL, sort by average execution time, and select the top N. Do not guess based on intuition; use data.

2. Read the SQL and break down its access pattern. Examine three things: which columns appear in WHERE? Which use equality and which use ranges? Which columns appear in ORDER BY or GROUP BY? Finally, which columns does the query SELECT, so they can be considered for a covering index?

3. Design one index that covers the query. Order columns according to "equality → range → ordering," then use INCLUDE to attach columns required by SELECT and turn it into a covering index. If the data has a clear hot subset, add WHERE to make it a partial index.

4. Validate with EXPLAIN. Run EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL or EXPLAIN ANALYZE in MySQL both before and after creating the index. Confirm that the plan uses Index Scan or Index Only Scan instead of Seq Scan; that cost and actual execution time fall significantly; and that there is no unexpected Sort or Bitmap Heap Scan involving many heap fetches.

5. Keep observing. One week after deployment, inspect pg_stat_user_indexes again to confirm that the new index is being used. Roll it back if it is not. An index is not insurance simply because it exists; it only counts after validation.

Here is a complete example. The slow query is:

SELECT id, amount_cents, status FROM orders
WHERE user_id = 88123 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

Breakdown: equality on user_id, equality on status, ordering by created_at, and amount_cents required by SELECT. The design is:

CREATE INDEX idx_orders_user_pending_time
ON orders(user_id, status, created_at DESC)
INCLUDE (amount_cents)
WHERE status = 'pending';  -- pending is a small hot subset, so use a partial index

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Limit  (cost=0.42..2.30 rows=20 ...)
--   -> Index Scan Backward using idx_orders_user_pending_time on orders
--        Index Cond: (user_id = 88123)
--        Buffers: shared hit=4   -- 4 pages, no heap fetch and no sorting

One index handles filtering, ordering, heap-fetch elimination, and hotspot narrowing at the same time. That is what it means to understand indexing.

Summary

1. Design indexes around queries, not tables. Start with a slow query, then design the index. A precise index for each query beats accumulating indexes indiscriminately.
2. The leftmost-prefix rule is the heart of composite indexes, and columns should follow "equality → range → ordering." This rule determines whether 80% of your indexes can be used.
3. Covering indexes with INCLUDE and partial indexes with WHERE are two powerful tools. One eliminates heap fetches; the other removes cold data. Both offer exceptional value.
4. Index exactly what the WHERE clause uses. A query on LOWER(name) needs an index on LOWER(name). The expressions must match exactly before the optimizer will recognize them.
5. Indexes impose three costs: write amplification, storage, and maintenance. Unused indexes are pure liabilities, so remove them regularly with the help of pg_stat_user_indexes.

More indexes are not necessarily better; more precise indexes are. One index tailored to a query is better than ten single-column indexes added by guesswork.