Database Indexing Strategies That Improve Query Performance 

A team running a Postgres-backed SaaS product once watched a dashboard query that had run in under 50 milliseconds for two years suddenly take eleven seconds, without a single line of application code changing. The cause was not a bug, the underlying table had simply grown past a size where the query planner’s existing execution plan, built around an index that no longer matched the query’s actual filtering pattern, stopped being efficient, and a full table scan quietly became the plan of choice.

Indexing is one of the few areas of software engineering where a single well-placed change, or a missing one, can move a query’s performance by orders of magnitude. 

How B-Tree Indexes Organize Data for Fast Lookups 

The B-tree, short for balanced tree, is the default index structure in nearly every relational database, Postgres, MySQL’s InnoDB engine, SQL Server, because its performance characteristics fit the overwhelming majority of query patterns well. A B-tree organizes indexed values into a balanced, sorted tree structure where every leaf node sits at the same depth, guaranteeing that a lookup, insert, or delete takes a predictable, logarithmic number of steps relative to the table’s size, regardless of which value is being searched for. 

When a query filters on an indexed column with an equality or range condition, the database engine walks down the tree from the root, comparing the search value at each node and following the appropriate branch, until it reaches the leaf nodes pointing to the matching rows. Because the tree is balanced and sorted, this walk touches only a small fraction of the total index, which is why an indexed lookup on a table with ten million rows can complete in a few milliseconds, while the same lookup without an index scans every row sequentially.

B-trees excel at range queries and sorted output, beyond exact-match lookups, because the leaf nodes are linked in sorted order, a query like WHERE createdat BETWEEN ‘2026-01-01’ AND ‘2026-01-31’ can locate the start of the range with a tree walk and simply follow the linked leaf nodes forward until the range ends, rather than repeating a full tree walk for every matching row. This same property lets a B-tree index satisfy an ORDER BY clause without a separate sort step, since the index already stores values in the required order. 

The trade-off inherent to any index, B-tree included, is that it must be maintained on every write. An insert, update, or delete on an indexed column requires the database to also update the index structure, which is why adding indexes is not a free performance improvement, each one adds write overhead and storage, a cost that becomes material on tables with heavy write traffic and many indexes. 

Composite Indexes and Column Order 

A composite index spans multiple columns, and the order those columns are listed in the index definition determines which query patterns the index can serve efficiently, this is one of the most consistently misunderstood aspects of indexing among engineers who have not studied it directly. A composite index on (customer_id, created_at) is truly useful for a query filtering on customer_id alone, or on both customer_id and created_at together, because the index’s sort order groups all rows for a given customer together, and within each customer’s group, sorts by date. 

That same index provides little to no benefit for a query filtering on created_at alone without also filtering on customer_id, because the index’s leading column is customer_id, and the database cannot range-scan the second column without first narrowing the first. This is the “leftmost prefix” rule: a composite index can serve queries using a prefix of its columns, in order, but not queries that skip the leading column. 

Deciding column order should generally start with the column used most often for equality filtering, followed by columns used for range filtering or sorting. A common pattern in multi-tenant applications is leading every composite index with the tenant identifier, since nearly every query filters by tenant first, making that column the natural leftmost choice. 

CREATE INDEX idx_orders_customer_created 

ON orders (customer_id, created_at DESC);

Over-indexing with too many narrow, overlapping composite indexes is a real cost, not just a theoretical one, a table with a dozen overlapping composite indexes, several of which are redundant subsets of a broader index, pays that maintenance cost repeatedly for indexes that could often be consolidated into fewer, more thoughtfully designed ones. 

Contrasting Index Types: B-Tree, Hash, GIN, and BRIN

Hash indexes store a hashed representation of the indexed value, making equality lookups extremely fast but entirely unsuitable for range queries or sorting, since a hash function deliberately scrambles the relationship between input values and storage location. Postgres’s hash indexes were historically avoided due to reliability issues before version 10, but modern versions are crash-safe and replicated, so they see occasional use for very large tables where equality lookups dominate. 

GIN, or Generalized Inverted Index, handles a different problem: indexing values containing multiple component values within a single column, such as array elements, full-text search vectors, or JSONB document keys. A GIN index maps each component value to the list of rows containing it, which is what makes searching whether a JSONB column contains a key, or a text column contains a word, efficient without scanning every row’s full content. Full-text search in Postgres relies on GIN indexes over `tsvector` columns almost universally. 

BRIN, or Block Range Index, takes the opposite approach from a B-tree’s fine-grained precision, storing only summary information, like the minimum and maximum value, for each physical block of storage rather than an entry per row. This makes BRIN indexes dramatically smaller than an equivalent B-tree, at the cost of being useful only when the indexed column correlates strongly with physical storage order, a timestamp column on an append-only, time-ordered table is the canonical use case, letting BRIN quickly eliminate large ranges of blocks that cannot contain a match. 

Choosing among these types requires matching both the query pattern and the data’s own physical characteristics, not just picking B-tree by default, a GIN index on a JSONB column that a B-tree cannot reasonably support, or a BRIN index that shrinks a multi-gigabyte B-tree down to a few megabytes on a well-ordered append-only table, represent real wins a one-size-fits-all approach misses entirely. 

Balancing Read Speed Against Write Overhead 

Every index accelerates some read pattern while imposing a cost on every write to the indexed columns, and the right number and design of indexes for a given table depends on the actual ratio of reads to writes that table experiences in production, not a generic best practice applied uniformly. A table that is read constantly and written to rarely, a product catalog, a configuration table, can generally afford a generous set of indexes covering many query patterns, since the write cost is paid infrequently relative to how often the read benefit is realized. 

A table under heavy write load, an events table capturing high-volume telemetry, an orders table processing thousands of transactions per minute, pays the index maintenance cost on every write, and an over-indexed version of that table can slow down the write path the business depends on most, sometimes outweighing the read-side benefit, especially for indexes supporting infrequent analytical queries better served by a read replica instead. 

Index bloat compounds this trade-off over time in databases like Postgres that use multi-version concurrency control: updates and deletes leave behind dead tuples that indexes must still account for until vacuum reclaims the space, and a table with frequent updates and poorly tuned autovacuum settings can accumulate bloat that degrades both read and write performance well beyond the inherent cost of the index design itself. 

The practical response is treating indexing as an ongoing, query-pattern-driven decision rather than a one-time setup task: monitoring which indexes are used (Postgres’s pg_stat_user_indexes view reports scan counts directly), removing ones unused for a meaningful period, and periodically reviewing whether new common query patterns are served efficiently by the current index set. 

Query Planning and the Role of the Optimizer 

A database’s query optimizer decides, for any query, which available indexes to use and in what order to join tables, based on statistics it maintains about the data’s distribution, distinct values in a column, approximate row counts matching a condition, and correlation between logical and physical ordering. These statistics are estimates, refreshed periodically, and when they drift out of date, the optimizer can make truly poor decisions, choosing a full table scan when an index would have been faster, or vice versa. 

EXPLAIN ANALYZE is the primary tool for seeing what a database did to execute a query, showing the chosen plan alongside real timing and row-count data gathered by running the query, essential for diagnosing the gap between what an engineer expects an index to do and what the optimizer did. A query that “should” use an index but shows a sequential scan in EXPLAIN output is one of the most common and fixable performance issues, and the root cause is nearly always one of: stale statistics, a function applied to the indexed column, a data type mismatch, or a query pattern the existing index does not support. 

EXPLAIN ANALYZE 

SELECT * FROM orders 

WHERE customer_id = 42 

AND created_at > NOW() - INTERVAL '30 days';

A subtlety worth noting is that the optimizer’s choice is about cost estimation under the specific statistics available, not which plan is theoretically fastest in isolation, and it can reasonably choose a sequential scan over an index scan when a query matches a large fraction of the table’s rows, since reading an index and then randomly accessing table rows can, past a certain selectivity threshold, cost more than simply reading the table sequentially. 

Typical Indexing Mistakes That Hurt Performance 

The most common mistake is indexing every column that ever appears in a `WHERE` clause without considering column order in composite indexes or the actual selectivity of the column being indexed. Indexing a boolean column or a status column with only three possible values, on its own, rarely helps performance much, since the index cannot narrow the search space enough to beat a sequential scan when a third or more of the table matches the condition, selectivity, not just presence in a query, determines whether an index is worth having.

Applying a function to an indexed column inside a query, WHERE LOWER(email) = ‘user@example.com’ against a plain index on email, silently prevents the database from using that index at all, since the stored index values are not transformed by the function, and the database cannot know the function’s output without computing it for every row. The fix, a functional index built on the transformed expression itself (CREATE INDEX ON users (LOWER(email))`), is straightforward once the problem is recognized, but the symptom, a query mysteriously ignoring an index that clearly exists, confuses many engineers encountering it for the first time. 

Ignoring index maintenance over the life of an application is another recurring issue: schemas evolve, query patterns change, and indexes created for a feature later removed or rewritten often persist indefinitely, silently costing write performance without benefiting any current query. Periodic audits using the database’s own usage statistics catch this waste, but few teams schedule them as routine maintenance. 

Finally, teams frequently over-rely on ORMs’ automatic indexing conventions, indexing only foreign keys by default, without examining actual query patterns, which often need composite indexes, partial indexes for filtered subsets of data, or covering indexes, none of which a generic ORM convention generates without explicit configuration. 

Real-World Indexing Examples in Postgres and MySQL 

A common real-world pattern in Postgres is the partial index, which indexes only rows matching a specific condition, an index like CREATE INDEX ON orders (created_at) WHERE status = ‘pending’ is far smaller than a full index, and directly serves the common query pattern of looking up only pending orders, while completed and cancelled orders, most of the table, are excluded entirely. 

Covering indexes, which include additional non-key columns purely to satisfy a query from the index without touching the underlying table (an “index-only scan” in Postgres terms), can reduce I/O for read-heavy queries selecting only a small, predictable set of columns. Adding a frequently selected column to a composite index’s INCLUDE clause lets the database answer the query from the index alone, avoiding extra disk access to fetch the row from heap storage. 

MySQL’s InnoDB engine has its own notable characteristic: the primary key is a clustered index, meaning row data is physically stored in primary key order, and every secondary index stores the primary key value rather than a direct row pointer, so secondary lookups always involve an extra step to fetch the row. This makes primary key choice unusually consequential in MySQL, a UUID primary key, with its random insertion order, causes far more page splits than an auto-incrementing integer would, which has led many MySQL systems to prefer sequential primary keys even when UUIDs are used elsewhere. 

Both engines also support extended statistics objects for cases where the optimizer’s default assumption of column independence produces poor estimates, Postgres’s CREATE STATISTICS command helps it recognize correlated columns like city and postal code, improving plan quality for queries filtering on both.

Maintaining and Auditing Indexes Over Time 

A sustainable indexing strategy treats indexes as a living part of the schema needing periodic review, not decisions made once during initial development. Postgres’s pg_stat_user_indexes and MySQL’s sys.schema_unused_indexes view both provide direct visibility into which indexes are used in production, and reviewing this data on a recurring cadence, quarterly is a reasonable default, surfaces indexes accumulating write overhead without any corresponding read benefit. 

Index bloat monitoring deserves its own attention in Postgres, since MVCC means indexes accumulate dead entries between vacuum cycles, and a table under sustained high-update load with poorly tuned autovacuum settings can develop bloat that a REINDEX (ideally the concurrent variant, to avoid locking the table) resolves. 

Before adding a new index to a production table, testing its impact on a realistic copy of the data, rather than a small development dataset, catches surprises that only appear at scale, an index harmless at ten thousand rows can take unexpectedly long and lock the table at ten million, which is why concurrent index builds (CREATE INDEX CONCURRENTLY in Postgres) are standard for live production tables. 

Finally, documenting the intended purpose of non-obvious indexes, a partial index built for a specific dashboard query, a covering index added to eliminate a slow query found in an incident review, saves the next engineer from mistakenly removing an index that serves a non-obvious purpose, or leaving one in place whose original justification no longer applies.

 Final Thoughts 

Indexing is not a one-time configuration decision but an ongoing discipline that requires knowing both how a database’s index structures work and how application query patterns evolve over time. The right index for a given table depends on its read-to-write ratio, the selectivity of its columns, and the specific queries run against it, not a generic set of best practices applied without measurement.

Teams that treat EXPLAIN ANALYZE output as a routine input to schema decisions, rather than a tool reached for only during an incident, tend to avoid the sudden, unexplained slowdown that indexing problems so often produce.

Frequently Asked Questions 

1. How many indexes are too many on a single table? 

There is no fixed number; it depends on the table’s write volume and the diversity of query patterns against it. A read-heavy, rarely-written table can reasonably support a dozen or more indexes, while a high-write table should be indexed conservatively, guided by actual query patterns and periodic review of index usage statistics rather than an arbitrary limit. 

2. Why would a database ignore an index that clearly exists on a column? 

Common causes include stale statistics, a function or type cast applied to the column in the query that prevents index use, or the optimizer correctly determining that a sequential scan is cheaper because the query matches too large a fraction of the table for the index to provide a meaningful advantage over reading the table directly. 

3. What is the difference between a clustered and non-clustered index? 

A clustered index determines the physical storage order of the table’s rows, and a table can have only one, since data can only be physically sorted one way. A non-clustered, or secondary, index is a separate structure pointing back to the table’s rows, and a table can have many of these serving different query patterns.

4. Do composite indexes need to match a query’s column order exactly? 

Not exactly, but the leftmost columns of the index must match the query’s filter conditions for the index to be used effectively, following the leftmost-prefix rule. A composite index can still serve a query that uses only its first one or two columns, but not one that skips the leading column entirely. 

5. How often should a team review its indexing strategy? 

A quarterly review of index usage statistics is a reasonable cadence for most production systems, though teams experiencing rapid schema or query pattern changes may benefit from more frequent review. Any significant performance incident traced to a missing or misused index is also a natural trigger for a focused review. 

6. Can adding too many indexes ever cause the database to choose a worse execution plan? 

Yes, indirectly. A large number of overlapping indexes increases the search space the optimizer must evaluate, and in rare cases can lead to plan choices that are marginally worse than a simpler, more focused index set would produce, though the more common and significant cost of excessive indexing is write overhead rather than degraded plan selection.