Data indexing is the design of additional data structures that help a database locate useful records without examining every stored row in the same way. Query optimisation is the process of choosing among possible execution strategies—table scans, index scans, join orders, join algorithms, sorts, aggregations, parallel work and other operators—to answer a declarative query with an acceptable amount of work.

The central idea is simple: a database query states what result is wanted, while the optimiser decides how to obtain it. The same SQL statement can have many valid physical plans. A good plan reduces expensive work such as unnecessary page reads, repeated comparisons, avoidable sorts and oversized intermediate results. A bad plan can be logically correct and operationally disastrous.

An index is not a promise that a query will be fast. It is another possible route through the data, and the optimiser still has to decide whether that route is worth taking.

This article therefore treats indexing and optimisation as one connected system. An index affects which access paths exist. Statistics affect how the optimiser estimates those paths. Query structure affects which paths are eligible. Data distribution affects whether estimates are realistic. Memory, caching, concurrency and storage affect observed cost. Measurement finally tells us whether the chosen plan solved the receiver’s actual problem.

ARTICLE ID: DATA.MANAGEMENT.062
Canonical function: index design, access-path selection, cardinality/selectivity reasoning, cost-based planning and evidence-led query tuning
Owner boundary: Database Management and Transactional Integrity owns correctness, persistence, transactions and concurrency; Data Partitioning and Sharding owns distribution and locality across partitions/nodes; Data Caching and Materialisation owns precomputed and cached state. This article owns the path-selection problem: how the database finds and combines the data efficiently.

Start here: read One Query, Many Plans for the core model, B-Trees from First Principles for index mechanics, Selectivity and Cardinality for optimiser reasoning, or the Plan Laboratory for worked examples.

The engine examples below are intentionally labelled. PostgreSQL, MySQL and SQLite expose different details and support different index families. The principles transfer, but syntax, cost models and plan operators do not always transfer unchanged. Current primary documentation is linked where a concrete engine behaviour is discussed.

1. One query can have many correct plans

Consider a table named orders containing one hundred million rows. A user asks for all orders for customer 48291 placed in the last thirty days:

SELECT order_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 48291
  AND ordered_at >= CURRENT_DATE - INTERVAL '30 days';

At least several broad strategies are imaginable. The database can scan the entire table and test every row. It can use an index on customer_id, retrieve all rows for that customer and then filter by date. It can use an index on ordered_at, retrieve all recent orders and then filter by customer. It can use a composite index beginning with customer_id and continuing with ordered_at. If the required output columns are available from the index in a form the engine can exploit, it may avoid some base-table lookups as well.

All of these plans can return the same logical answer. Their cost depends on how many rows each step touches, how data is laid out, how selective the predicates are, whether pages are already in memory, how wide rows are, whether parallelism is available, whether the index order helps satisfy ORDER BY, and many other factors.

This is why SQL performance cannot be reduced to “indexes are faster than scans”. If a predicate matches 80% of a table, reading an index plus following millions of row pointers may cost more than scanning the table sequentially. If a table is tiny, a scan can be cheaper than consulting an index. If the optimiser believes a predicate will return one row but it actually returns ten million, it may choose a join algorithm that performs badly at runtime.

Optimisation is therefore a comparison problem. The engine enumerates or constructs candidate plans, estimates their cost and selects one according to its planning model. Different engines search the plan space differently, but the general idea is shared: several legal ways exist to satisfy the query; the optimiser needs enough information to prefer a good one.

2. Separate logical correctness from physical efficiency

A relational query describes a logical result. Operations such as filter, join, projection, aggregation and ordering describe what relationship the user wants from the stored data. Physical operators describe how the system performs those logical operations.

A logical join can be implemented as a nested-loop join, hash join, merge join or another engine-specific strategy. A logical filter can be applied during a table scan, during an index lookup, after a join or through predicate pushdown into a storage layer. A logical ordering can require an explicit sort or emerge naturally from an access path that already yields the required order.

Keeping the two levels separate makes query tuning more disciplined. Rewriting SQL merely to imitate a desired physical algorithm can make the query harder to understand and more brittle when the optimiser improves. Conversely, pretending physical design does not matter can leave a correct query doing avoidable work.

The best tuning question is usually not “How can I force plan X?” It is “Why does the optimiser prefer this plan under the information and costs it currently sees, and what evidence shows another plan is safer or cheaper for the real workload?”

3. A table scan is not automatically a failure

A full table scan reads the table’s relevant storage and evaluates the predicate against rows or batches of rows. On a large table with a very selective predicate, that can be expensive. Yet scans have strengths that indexes do not.

• They access storage in a predictable sequential pattern.
• They avoid the overhead of traversing a separate index.
• They can be efficient when a large fraction of rows is required.
• They can benefit from vectorised or parallel execution in analytical systems.
• They do not pay the maintenance cost of another persistent structure.

Suppose a one-million-row table fits comfortably in memory and a query needs 700,000 rows. An index that points to those 700,000 rows may simply add indirection. The database might sensibly scan once rather than perform hundreds of thousands of row lookups through a secondary structure.

A tuning review should therefore ask whether the scan is proportionate to the result and workload. A scan returning one row from a billion-row table deserves investigation. A scan returning 90% of a small table may be exactly right.

4. Think of an index as a map with maintenance cost

An index stores selected key values in an organisation that helps locate rows or data pages. The database pays to build and maintain that organisation because future reads may become cheaper.

If an index contains customer_id, it allows the engine to navigate by customer rather than reading every order to discover which rows belong to that customer. If the index is ordered, it can also make ranges and sorting useful. If the index contains enough output columns, the engine may answer a query using the index alone or mostly from the index, depending on engine rules and visibility requirements.

The trade-off is real. Every additional index consumes storage. Inserts may need a new index entry. Updates to indexed values may need deletion and insertion work. Deletes must remove or invalidate entries. Vacuuming, page splits, compaction or background maintenance can become more expensive. The optimiser must consider more candidate paths. Backups and replication may carry more data.

MySQL’s current documentation makes this trade-off explicit: indexes can greatly improve reads, but unnecessary indexes consume space and add work to inserts, updates and deletes. MySQL 8.4 — Optimization and Indexes.

The mature goal is therefore not “maximum number of indexes”. It is a small, justified portfolio of structures that supports important access patterns at acceptable write, storage and operational cost.

5. B-Trees from first principles

The B-tree family is the default general-purpose indexing idea in many relational systems because it keeps keys in sorted order while maintaining a shallow tree. Internal nodes guide the search toward a leaf page; leaf-level entries identify the matching row or stored value according to the engine’s implementation.

The important practical property is fan-out. A disk or memory page can contain many key-pointer pairs. Instead of a binary tree with two branches at each node, a database B-tree node can have a large number of children. That means even an index covering millions or billions of keys can remain only a handful of levels deep.

Imagine, purely for intuition, a tree whose internal pages effectively direct to 200 children. One level can distinguish about 200 ranges, two levels about 40,000, three levels about eight million and four levels about 1.6 billion. Real fan-out depends on page size, key width, pointers, compression, fill factors and engine internals; the example shows why balanced database trees can remain shallow.

Sorted order enables more than equality lookup. A B-tree can often support ranges such as price BETWEEN 20 AND 40, inequalities, ordered traversal and prefix-constrained multi-column searches. PostgreSQL’s documentation describes B-tree indexes as suitable for equality and range comparisons and identifies them as the default index type for common situations. PostgreSQL — Indexes.

Do not equate a theoretical logarithmic search with end-to-end query time. A query may find the first matching leaf quickly and then scan millions of neighbouring entries. It may perform random heap lookups for each match. It may block behind I/O or locks. Complexity notation explains part of the search structure, not the entire physical workload.

6. Equality lookup and range scan are different workloads

For an equality predicate on a highly selective key, a B-tree can navigate directly toward the relevant key range. If the key is unique, the engine may need only a tiny portion of the index plus the referenced row.

For a range predicate, the tree helps locate the beginning of the range, but then the engine walks through entries until the range ends. The cost therefore includes both navigation and the number of matching entries.

This distinction explains why an index may be excellent for order_id = 123 and much less impressive for ordered_at >= '2020-01-01' when nearly the entire table is newer than that date. The predicate syntax looks indexable in both cases, but the second predicate may not reduce enough work.

Range scans can still be valuable when ordering matters. If the query asks for the newest 50 orders and an index can produce matching rows in the required order, the engine may stop after finding enough rows. An index that helps both filtering and ordering can avoid reading and sorting a much larger intermediate result.

7. Hash indexes trade ordering for equality-oriented lookup

A hash index transforms a key through a hash function and organises entries according to the resulting hash value. The attraction is direct equality-oriented lookup without preserving a global sort order.

Because hash order does not preserve the natural ordering of original values, hash indexes are generally not the natural structure for range queries such as “between 100 and 200” or for ordered traversal. Their usefulness therefore depends heavily on equality access patterns and engine implementation.

PostgreSQL documents its hash indexes as supporting simple equality comparisons, while its B-tree indexes support a wider set of ordered comparisons. PostgreSQL — Index Types.

Hash collisions are an implementation fact rather than evidence that two source keys are equal. A robust hash index stores enough information or uses appropriate collision-handling machinery so different source values that map into the same hash area can still be distinguished.

Do not choose a hash index merely because a column is queried with =. A B-tree may already serve equality well while also supporting range and order requirements. The decision belongs to a measured workload and the particular database engine.

8. Specialised index structures exist because not every value behaves like a scalar key

Traditional B-tree intuition works well for sortable scalar values. Other data shapes need different search strategies. Modern relational databases therefore expose specialised index families for spatial relationships, full-text search, arrays, documents, physical-range summaries and other workloads.

PostgreSQL, for example, documents B-tree, Hash, GiST, SP-GiST, GIN and BRIN index families. GiST is an extensible framework that can support several search strategies; SP-GiST supports partitioned search structures such as tries or spatial decompositions; GIN is an inverted-index approach suited to values with components such as arrays; BRIN stores summaries over physical block ranges and is particularly useful when values correlate with physical row order. PostgreSQL — Index Types.

The general lesson is not to memorise acronyms. It is to align the structure with the query’s relationship. Equality, range, containment, nearest-neighbour, token membership and spatial overlap are different search problems. An index family is useful when its operator semantics match the predicates the workload actually uses.

Specialised structures also introduce specialised maintenance and selectivity behaviour. A full-text inverted index can grow differently from a numeric B-tree. A BRIN index may remain tiny but return broad candidate ranges requiring rechecks. A spatial tree may depend on the distribution of geometries. Measure the whole path, including false-positive rechecks and downstream row access.

9. Selectivity and cardinality drive many optimiser decisions

Cardinality is a count: how many rows, groups or distinct values are expected at a stage. Selectivity is a fraction: what proportion of candidate rows is expected to survive a condition.

If a table contains ten million rows and a predicate is estimated to have selectivity 0.001, the optimiser expects roughly ten thousand rows after the filter:

10,000,000 × 0.001 = 10,000.

If the actual result is four million rows, the estimate is wrong by a factor of 400. That error can affect whether an index appears attractive, which join should be outer or inner, whether a nested loop seems safe, how much memory a hash operation needs and whether a sort is expected to spill.

Selectivity is not simply “number of distinct values inverted”. If values are unevenly distributed, a common category and a rare category can have radically different selectivity even though they come from the same column. Correlation between columns can make independent estimates wrong. Temporal clustering, skewed tenants, seasonal behaviour and stale statistics all matter.

A good optimiser therefore uses statistics rather than only schema definitions. The quality of those statistics strongly influences plan quality.

10. High cardinality can make equality indexes attractive—but not automatically

A customer identifier with ten million distinct values across ten million rows is highly selective for equality if each customer appears once. An index lookup can be extremely effective.

A status column with three values—OPEN, CLOSED, PENDING—is very different. If 70% of rows are CLOSED, an index used for status = 'CLOSED' may still touch most of the table. Yet status = 'PENDING' could be selective if only 0.05% of rows use that value.

This is why “low-cardinality columns should never be indexed” is too broad. The answer depends on distribution, query combination, partial-index possibilities, ordering and the engine. A Boolean processed field can still participate usefully in an index that targets the small unprocessed subset.

Likewise, “high-cardinality columns should always be indexed” ignores workload. A randomly generated trace identifier queried once a year may not justify a persistent index if inserts dominate and the table is small enough for the occasional scan.

11. Statistics turn stored data into optimiser beliefs

The optimiser cannot normally inspect every row before choosing every plan; doing so would defeat the purpose of planning. Instead, it uses stored statistical summaries to estimate row counts and distributions.

Typical statistical ideas include table row counts, distinct-value estimates, null fractions, most-common values, histograms or distribution summaries, correlation information and multi-column statistics where supported. Exact details differ by engine.

MySQL 8.4 exposes optimiser statistics and supports histograms, with management through features such as ANALYZE TABLE. Its documentation also describes a cost model that helps compare candidate plans. MySQL 8.4 — Controlling the Query Optimizer.

Statistics are evidence about a changing dataset, not timeless truth. Bulk loads, deletions, rapid growth, tenant migration or changing seasonal distributions can make them stale. A plan regression after a major data shift can be a statistics problem even when the SQL and index definitions did not change.

However, “run analyse” should not become a ritual answer to every slow query. First inspect whether estimates are wrong, which estimate is wrong and why. A missing index, non-sargable predicate or huge unavoidable result will not become cheap merely because statistics are fresh.

12. Histograms matter when values are skewed

Suppose an orders.country_code column contains 60% SG, 20% MY, 10% US and the remaining 10% spread across 180 countries. A uniform model based only on the number of distinct values would estimate each country at roughly 1/183 of the table, badly underestimating Singapore and overestimating most rare countries.

A histogram or most-common-values summary gives the optimiser a better description of the distribution. It can then understand that country_code = 'SG' is broad while country_code = 'IS' may be narrow.

The challenge becomes harder with combined predicates. If almost every enterprise customer is in Singapore and consumer customers are globally distributed, estimates for customer_type = 'enterprise' AND country_code = 'SG' depend on correlation between columns. Multiplying independent selectivities can be badly wrong.

This is one reason why production plan analysis compares estimated rows with actual rows when the engine provides that evidence. A large divergence is often more diagnostically useful than staring at operator names alone.

13. Composite indexes encode an order of questions

A composite index stores more than one key column. For a B-tree index on (customer_id, ordered_at), entries are primarily ordered by customer_id and then by ordered_at within each customer.

This arrangement is excellent for a predicate such as:

WHERE customer_id = 48291
  AND ordered_at >= DATE '2026-08-01'

The engine can navigate to customer 48291 and then scan the ordered date range within that customer. It may also help with queries that constrain only the leading customer key, depending on engine rules.

The reverse query—“all customers who ordered after this date”—may not gain the same benefit from a B-tree whose first column is customer. The index is grouped by customer before date, so recent orders are scattered across many customer ranges.

PostgreSQL’s current documentation describes multicolumn B-tree behaviour in terms of leading-column constraints and recommends using multicolumn indexes sparingly rather than indiscriminately adding wide combinations. PostgreSQL 18 — Multicolumn Indexes.

The design question is therefore not “Which columns appear in the WHERE clause?” It is “Which equality, range and ordering pattern appears repeatedly, and in which sequence does the index need to narrow the search?”

14. The leading-key rule is useful, but slogans are dangerous

People often summarise composite B-tree behaviour with the phrase “leftmost prefix”. The phrase is useful when it reminds us that ordered composite keys begin with one column sequence. It becomes misleading when treated as a universal law that every database either uses an index perfectly or cannot use it at all.

For an index on (a, b, c), a predicate fixing a and b generally allows the engine to navigate much more tightly than a predicate on c alone. But engines can combine indexes, scan broader portions of an index, apply later-column filters, use skip-scan-like techniques in some versions, or decide that a scan is cheaper.

The practical lesson is to inspect the plan for the actual engine and query rather than convert one indexing heuristic into a parser rule in your head. Ask how much of the index is navigationally useful, how much remains as a residual filter, and how many rows the path is expected to touch.

A common design mistake is to build separate three-column indexes for every permutation of the same fields. That can multiply write and storage cost while still failing to match the most important queries. Start from workload patterns, not combinatorial completeness.

15. Covering indexes reduce trips back to the base table

An index is covering for a query when it contains enough information to satisfy that query without retrieving additional columns from the underlying table—or with substantially fewer table visits, depending on the engine’s storage and visibility rules.

Consider:

SELECT ordered_at, total_amount
FROM orders
WHERE customer_id = 48291
ORDER BY ordered_at DESC
LIMIT 20;

An index whose search key begins with customer_id and ordered_at, and that also makes total_amount available to the access path, may allow the engine to find the first twenty qualifying entries in order without fetching the full base row for every candidate.

SQLite’s query-planner documentation uses the term covering index for an index containing the search terms and output values needed by a query, allowing it to avoid a second lookup into the original table. SQLite — Query Planning.

PostgreSQL supports index-only scans and allows non-key columns to be stored with INCLUDE for appropriate indexes. But an index containing all referenced columns does not guarantee that every execution becomes physically table-free; visibility and storage details still matter. The correct claim is that the index makes an index-only strategy possible under the engine’s conditions.

Covering also has a cost. Adding wide output columns increases index size, can reduce page density and increases maintenance work when included values change. “Cover every query” can turn a concise access structure into a second copy of the table.

16. Uniqueness is a correctness rule with optimisation consequences

A unique index is not merely a faster ordinary index. It enforces a constraint: the indexed key combination cannot appear more than once, subject to the engine’s null and collation semantics.

That constraint gives the optimiser stronger information. If a join uses a key known to be unique on one side, the planner knows that each matching key contributes at most one row from that side. This can improve cardinality reasoning and allow transformations unavailable when duplicates are possible.

Do not add a unique index solely because uniqueness might improve a plan. Declare uniqueness only when it is true in the data model. A student identifier unique within one school is not necessarily globally unique across all schools. A product code reused after archival may need a compound scope.

Correct constraints can improve optimisation because they reduce uncertainty. Incorrect constraints improve neither the data nor the plan; they simply reject legitimate states or encourage brittle workarounds.

17. Partial indexes focus on a useful subset

A partial index indexes only rows satisfying a defined predicate. It can be powerful when a small operational subset receives disproportionate query traffic.

Suppose 99.8% of jobs are complete and operators repeatedly ask for jobs still waiting:

WHERE status = 'PENDING'

An index containing only pending rows can remain far smaller than an index covering every historical job. It reduces storage and maintenance for rows outside the indexed condition while providing a highly focused path for the operational query.

The trade-off is implication. The optimiser must be able to establish that the query’s predicate is compatible with the index predicate. If the application expresses the condition in a logically related but unrecognised form, the index may not be eligible.

Partial indexes are also vulnerable to changing distributions. If “pending” grows from 0.2% to 40% after a business process changes, the same design may no longer be attractive. An index definition can remain syntactically valid while its original cost assumptions disappear.

18. Expression indexes move selected computation into the access structure

An expression or functional index stores the result of a defined expression rather than only the raw column value, where the engine supports that feature.

If users frequently search case-insensitively:

WHERE lower(email) = lower(:email)

an index on the corresponding lower-cased expression can make that predicate indexable under the engine’s expression-matching rules.

This is not a licence to index every function. The expression becomes part of write-time maintenance, storage and semantic design. Locale, collation, determinism and function behaviour matter. If the expression changes meaning across versions or configuration, a persisted index can become an operational migration issue.

Expression indexes are especially useful when they turn a repeated transformation into a stable access key. They are less attractive when the transformation changes frequently or when only a rare ad hoc query uses it.

19. SARGability asks whether a predicate exposes a searchable argument

SARGable is database-performance shorthand for a predicate form that allows the optimiser to use an indexable search condition effectively. The exact eligibility rules are engine-specific, but the intuition is valuable: can the access path compare the stored/indexed key directly to a useful boundary?

Compare:

WHERE ordered_at >= DATE '2026-09-01'
  AND ordered_at <  DATE '2026-10-01'

with a form such as:

WHERE EXTRACT(YEAR FROM ordered_at) = 2026
  AND EXTRACT(MONTH FROM ordered_at) = 9

Unless an appropriate expression index or engine transformation applies, wrapping the indexed column in functions can make direct range navigation harder. The first form exposes a contiguous timestamp range clearly.

The lesson is not “never use functions in WHERE”. It is “understand which side of the predicate the engine can search and which transformations it can recognise”. Query clarity and index design can often cooperate without encoding engine tricks into every line of SQL.

20. Implicit casts can quietly change access-path eligibility

Suppose a numeric identifier is stored as text because leading zeros matter, but an application supplies an integer parameter. The database may cast the parameter to text, cast the indexed column to a number, reject the comparison or choose another behaviour according to its type system and query formulation.

If the indexed column is transformed row by row, the engine may be unable to use the ordinary index in the intended way. Even when an index remains usable, type conversion can change comparison semantics, collation or selectivity estimates.

Use the right data type for the domain and bind parameters using compatible types. An identifier that is not mathematically a number should not be made numeric merely because it contains digits. Type correctness improves both data modelling and optimiser predictability.

When a plan changes unexpectedly after an application migration, parameter types and implicit casts belong on the investigation list alongside indexes and statistics.

21. An index can sometimes make ORDER BY disappear as an operator

Sorting N rows usually requires memory and comparison work, with possible disk spill when the sort exceeds its working memory. If an access path already produces the rows in the required order, the engine may avoid an explicit sort.

For example:

SELECT order_id, ordered_at
FROM orders
WHERE customer_id = 48291
ORDER BY ordered_at DESC
LIMIT 20;

An appropriately ordered composite index can let the engine navigate to the customer’s entries and walk them in descending date order, stopping after twenty. That can be dramatically cheaper than collecting thousands of rows and sorting them.

But the index order must match the relevant predicate/order pattern under the engine’s rules. Mixed ascending/descending requirements, null ordering, collation and leading-column constraints can affect whether the sort can be avoided.

SQLite’s planner documentation gives clear worked examples of using index order for sorting and of covering indexes that combine search and sort. SQLite — Query Planning.

22. LIMIT changes the economics of a query when the plan can stop early

A query returning the top ten rows is not merely the same as a query returning all rows with a smaller network payload. A good plan can sometimes stop once it has found the required ten.

If the engine can walk an index in the desired order and apply the relevant filter during that walk, LIMIT 10 can keep the examined set tiny. If it must scan a million rows, sort them and then take ten, the logical result is small while the physical work remains large.

For pagination, deep OFFSET values can force the engine to locate and discard many earlier rows. Keyset or seek pagination can be more efficient when a stable ordered key is available:

WHERE (ordered_at, order_id) < (:last_time, :last_id)
ORDER BY ordered_at DESC, order_id DESC
LIMIT 50

The exact tuple syntax and plan behaviour depend on the engine. The principle is to continue from a known key boundary rather than recounting and discarding an ever-growing prefix.

23. Several indexes can sometimes cooperate

If a query has predicates on several columns, the database does not always need one composite index containing all of them. Some engines can combine results from multiple indexes through bitmap operations, index merge or related strategies.

This can be useful when independent predicates are individually selective or when workload diversity makes a giant composite index unattractive. But combined-index plans have their own cost: multiple index traversals, intermediate structures and later row fetches.

A purpose-built composite index may still be superior for a frequent critical query because it can exploit key ordering and avoid extra merging. Conversely, several smaller indexes may support a wider set of queries and reduce write cost relative to many wide composite permutations.

Measure the actual plan. The existence of index combination means “no composite index” is not automatically a scan, but it does not mean the combination is always efficient.

24. OR predicates create a union problem

A predicate such as:

WHERE customer_id = 48291
   OR email = 'learner@example.com'

can be evaluated by scanning, by combining index results for the separate conditions, or by another engine-specific strategy. The optimiser must account for overlap between the two result sets so duplicate rows are not returned incorrectly.

OR-heavy queries can become difficult when one branch is highly selective and another branch is broad or non-indexable. A single weak branch may make the combined path unattractive.

Rewriting as UNION is not automatically faster. It changes duplicate semantics unless carefully chosen, can repeat other work and can prevent optimisations available to the original form. Rewrite only after comparing plans and preserving exact query meaning.

25. Physical row organisation changes the cost of a secondary lookup

Some database/storage-engine designs organise table data around a primary or clustered key, while secondary indexes contain another key plus a reference to the clustered record. Other systems use heap storage with row locations referenced by indexes. These physical differences change what an “index lookup” costs.

In an InnoDB table, for example, secondary index entries include the primary-key columns needed to locate the clustered record. A wide primary key can therefore enlarge every secondary index. That is an engine-specific consequence with schema-design implications.

The broader lesson is that index size and lookup cost depend on how the engine represents row identity, not only on the columns named in CREATE INDEX. Before generalising tuning advice from another database, understand how your own engine connects index entries to base data.

26. Key width, page density and splits shape the hidden cost of an index

Pages hold a finite number of entries. Wider keys reduce the number of index entries that fit on each page. Lower page density can increase tree size, memory footprint and I/O. Frequently changing insertion patterns can cause page splits or other reorganisation depending on engine design.

This gives another reason to avoid thoughtlessly indexing long text or duplicating wide columns across many covering indexes. A logically elegant index with eight large fields may have very different physical characteristics from a compact numeric index.

Insertion pattern matters too. Monotonically increasing keys tend to concentrate new entries at one edge of an ordered index. Random keys distribute writes more broadly. Either pattern can interact with concurrency, caching and storage in useful or problematic ways depending on the engine and workload.

Do not infer a universal winner from this observation. Sequential keys can have strong locality benefits; random keys can avoid certain hotspots while increasing fragmentation. Measure the full workload and engine behaviour.

27. Every read optimisation spends write and maintenance budget

If a table has one base representation and eight indexes, one inserted row can require changes to several structures. If an update changes four indexed columns, it can touch several index entries in addition to the table row. Replication, logging and backup may carry the resulting changes.

This is write amplification in practical database design: a small logical modification produces more physical work because the system maintains derived access structures.

The cost becomes visible in high-ingest systems. An index that saves 20 milliseconds on a query executed ten times a day may not justify persistent write cost on a stream ingesting 50,000 rows per second. Conversely, an index that prevents a critical user-facing lookup from scanning billions of rows may easily justify its maintenance.

Measure reads and writes together. Index tuning is workload tuning, not isolated SELECT tuning.

28. Index debt is the accumulation of structures nobody can justify

Indexes tend to accumulate because adding one is visible and deleting one feels risky. Years later, a table may contain overlapping single-column, composite and covering indexes created for workloads that no longer exist.

Index debt consumes storage, cache, maintenance time and cognitive attention. It can also slow schema changes and bulk loads.

A disciplined inventory records why each important index exists:

• which query or workload it supports;
• which predicates/order it serves;
• whether it enforces a constraint;
• observed usage where the engine exposes it;
• write/storage cost;
• owner;
• last review date.

Do not drop an apparently unused index solely because a short observation window saw no reads. A monthly close, annual audit or emergency incident query may matter enormously while being rare. Combine usage evidence with workload ownership and consequence.

29. Partition pruning and indexing solve different levels of the search problem

Partitioning divides a logical dataset into physical or logical segments. Pruning can exclude entire partitions that cannot satisfy the predicate. An index then helps locate rows inside the remaining partition or partitions.

For a time-partitioned events table, a date predicate may prune eleven monthly partitions and leave only September. An index on customer_id inside September can then find one customer’s events. These techniques complement rather than replace each other.

If the query does not include a usable partition key, the engine may need to inspect many partitions even when each has an index. Conversely, perfect pruning may make an additional index unnecessary when the remaining partition is already small enough for a scan.

See Data Partitioning and Sharding for distribution design. This article keeps ownership of the access path inside the chosen storage scope.

30. Cost-based planning turns alternatives into a ranking problem

A cost-based optimiser assigns estimated costs to candidate plan components and compares complete plans. The cost is not usually a promise that the query will take a particular number of milliseconds. It is a model used to rank alternatives under assumptions about page access, CPU work, memory, parallelism and other engine-specific factors.

MySQL’s documentation explicitly describes an optimiser cost model and statistics used during plan selection. SQLite likewise describes a cost-based query planner that estimates the work associated with alternative solutions. PostgreSQL’s planner also uses internal cost estimates to compare paths.

This matters because a plan can be the cheapest according to the model while performing poorly in reality. The model may have stale or incomplete statistics, inaccurate correlation assumptions, misleading row-count estimates, unusual hardware characteristics or runtime contention that planning does not fully capture.

The right response is not to dismiss cost-based planning. It is to compare model expectations with execution evidence and improve the inputs or structure that caused the mismatch.

31. Planner cost units are comparative, not a stopwatch

When an EXPLAIN plan shows a cost such as 123.45..9876.54, novice readers often interpret those values as milliseconds. In many systems they are internal relative units.

That distinction changes how plans should be read. A path with estimated cost 100 is preferred to one with cost 1,000 under the model, but the ratio does not necessarily predict a tenfold wall-clock difference. A plan’s actual runtime depends on cache state, concurrent load, storage, parameter values and the work performed after the plan was chosen.

Use cost values to understand why the optimiser preferred one path. Use measured execution data to understand what happened in the environment.

32. Join order can matter more than one missing index

A query joining five tables does not merely need five access paths. The optimiser must decide the order in which intermediate results are formed. A poor early join can produce millions of rows that later filters discard; a selective early filter can reduce the rest of the query dramatically.

Suppose table A has one million rows, B has one million and C has one hundred. If a filter on C reduces it to one row and that row maps to only a few records in A, starting from C can be attractive. Joining A and B broadly before applying C may create a huge intermediate result.

The number of possible join orders grows rapidly as the number of joined relations increases, so optimisers use search strategies and heuristics rather than naively executing every possible plan. Accurate cardinality estimates become increasingly valuable because each intermediate estimate influences later choices.

A query that becomes slow after adding one innocent-looking join deserves a plan-level review. The problem may be not the new table itself but the new join order or altered row estimates across the whole plan.

33. Nested-loop joins are excellent when the inner lookup is cheap

A nested-loop join conceptually takes rows from one input and finds matching rows in another input for each outer row. This can be extremely efficient when the outer side is small and the inner side has a selective indexed lookup.

Imagine 50 active students and a well-indexed table of millions of attendance records. For each active student, an index lookup can retrieve the relevant attendance range. The number of inner probes remains manageable.

The same plan becomes dangerous if the outer side unexpectedly contains 500,000 rows. Half a million inner index probes can be far more expensive than building a hash table once or scanning another structure sequentially.

This is why row-estimation errors can turn a sensible nested loop into a performance disaster. The operator is not inherently bad. Its suitability depends on the size and access cost of its inputs.

34. Hash joins exchange ordered access for build-and-probe work

A hash join typically builds an in-memory hash structure from one input using the join key, then probes that structure with rows from the other input. It is often attractive for equality joins when inputs are large enough that repeated indexed probes would be expensive.

The planner must decide which side to build. Building from a smaller input usually reduces memory. If the build side is much larger than estimated, the structure may exceed available memory and spill or partition work to disk according to engine behaviour.

A hash join does not preserve useful ordering by itself. If the query later needs sorted output, an additional sort may be required. An index-nested-loop or merge join could sometimes satisfy other ordering needs more naturally.

Again, there is no universally superior join algorithm. Query optimisation is the selection of a combination whose total cost fits the observed data and receiver job.

35. Merge joins exploit ordered inputs

A merge join walks two inputs ordered by the join key and advances through them to find matches. When both sides are already available in suitable order—perhaps through indexes or earlier sort operations—the join can be efficient and stream-friendly.

If neither input is ordered, the cost of sorting them may outweigh the benefits. If later operations also need the same order, the sort can have additional value.

Merge joins illustrate why optimising one operator in isolation is insufficient. An index that creates order may reduce sort cost and make a merge plan viable, even if the index lookup itself is not dramatically cheaper than a scan.

36. A join estimate is a claim about overlap, not just table size

Suppose table students has 10,000 rows and assessments has one million rows. A join on student_id does not produce ten billion rows simply because those are the Cartesian dimensions. Constraints and value distributions matter.

If each assessment belongs to exactly one valid student, the join may return roughly one million assessment rows with student attributes attached. If identifiers are duplicated unexpectedly, or if a many-to-many relationship is mistaken for one-to-many, the result can expand dramatically.

Schema constraints, uniqueness and foreign-key relationships can therefore help both correctness and planning. But declared relationships must match reality. A missing or unenforced logical constraint can leave the optimiser with weaker assumptions and leave analysts vulnerable to double counting.

37. Push filters toward the data when semantics permit

Predicate pushdown means applying a filter as early or as close to the storage source as possible instead of retrieving broad data and filtering later. In local databases this can mean using an index or scan filter before a join. In federated systems it can mean sending a predicate to the remote source.

Early filtering can shrink intermediate results, network traffic and later join work. But a filter cannot always be pushed safely. Outer joins, non-deterministic functions, security boundaries, collations, source capability and semantic transformations can make a seemingly obvious pushdown invalid or unsupported.

See Data Virtualisation and Federated Query for remote pushdown and source authority. Here the indexing question is whether the selected access path can exploit the predicate once it reaches the relevant storage layer.

38. Reading fewer columns can matter as much as reading fewer rows

SELECT * can increase I/O, memory, network transfer and the chance that a covering path becomes impossible. Wide text, JSON or binary fields are especially costly when the receiver does not need them.

Projection pushdown or column pruning lets the engine carry only required fields through parts of the plan. Columnar analytical storage benefits particularly from avoiding irrelevant columns, but row-store queries can also improve through narrower tuples and covering indexes.

Do not remove fields only for performance if the receiver actually needs them. The principle is requirement discipline: retrieve the information the job requires, not every attribute because it is easy to type an asterisk.

39. Aggregation has its own access-path economics

A query such as:

SELECT customer_id, SUM(total_amount)
FROM orders
WHERE ordered_at >= DATE '2026-09-01'
GROUP BY customer_id;

may scan qualifying rows, aggregate through a hash table, sort by customer and aggregate in order, use a partial/parallel strategy, or exploit pre-existing ordering. The right plan depends on qualifying row count, number of groups, memory and storage layout.

An index on customer_id alone does not necessarily make the query efficient if the date predicate still requires scanning almost every index entry. An index on date can narrow the month but may produce customer IDs in an order that requires another grouping structure.

Sometimes the workload truly needs repeated expensive summaries and a materialised aggregate is appropriate. That becomes the ownership of Data Caching and Materialisation. Query tuning should first establish whether the live calculation itself is reasonably designed before adding persisted derived state.

40. Memory shortage can turn an acceptable plan into a disk-heavy plan

Sorts, hash joins and hash aggregations often need working memory. When an operation exceeds its allocated memory, the engine may spill to temporary disk structures, partition the work or use another fallback.

A query plan can therefore remain structurally identical while runtime changes sharply because concurrent workload leaves less effective memory, data volume grows or the number of groups increases.

Do not respond by increasing memory limits blindly. A too-large per-query memory allowance multiplied across many concurrent queries can create system-wide pressure. First determine which operators spill, how often, and whether better selectivity, indexes or query structure can reduce the working set.

41. Parameter values can make one prepared query represent several workloads

Consider:

SELECT * FROM orders WHERE country_code = :country;

If :country = 'SG' matches 60% of the table while another value matches 0.01%, the ideal access path may differ. A scan can be reasonable for the common value while an index lookup can be excellent for the rare value.

Prepared statements and plan caching create an optimisation challenge: should the engine optimise separately for each value, reuse a generic plan, or choose another strategy? Details differ by database and configuration.

The practical diagnostic is to test representative parameter values rather than one convenient example. A plan that is perfect for a rare tenant can be disastrous for the dominant tenant, and vice versa.

42. Parameter skew is a data-distribution problem before it is a plan-cache problem

Teams sometimes label every parameter-sensitive slowdown “parameter sniffing”. That phrase belongs to particular engine behaviours and can obscure the more general issue: the same SQL text describes substantially different selectivities under different values.

First establish the distribution. How many rows match each representative parameter? Does the plan estimate that distribution accurately? Does one value represent most traffic? Are values correlated with another filter?

Then inspect the engine’s prepared-plan behaviour. The repair might be better statistics, a different index, query decomposition, plan recompilation, a generic plan choice, partitioning or an engine-specific hint. Do not begin with the remedy implied by a borrowed nickname.

43. EXPLAIN turns the optimiser’s reasoning into inspectable evidence

An execution plan is the primary diagnostic artefact for query optimisation. It typically reveals the chosen access paths, join order, join algorithms, estimated row counts and plan costs. Engine-specific variants can also execute the query and report actual row counts, timings, loops, memory or I/O.

MySQL’s documentation describes EXPLAIN as a tool for understanding how a query is processed and for identifying inefficient access choices. MySQL 8.4 — Understanding the Query Execution Plan.

Read a plan as a tree of work, not as a list of mysterious operator names. For each node ask:

• What rows enter this operator?
• What condition reduces them?
• How many rows were estimated?
• How many actually emerged, if known?
• How many times did the operator run?
• Which storage path did it use?
• Did it sort, hash, spill or fetch base rows?

The first large divergence between estimated and actual cardinality often points toward the root of a poor downstream plan.

44. Estimated rows and actual rows tell different stories

An estimated plan explains what the optimiser believed before execution. An actual plan explains what happened during one execution. Both are valuable.

If a node estimates 100 rows and actually returns 105, its estimate is close. If it estimates 100 and returns five million, later choices were made under a profoundly different world model.

But actual execution has risk. Running EXPLAIN ANALYZE or an equivalent command may execute a slow or modifying statement depending on syntax and engine. Use appropriate environments and engine documentation, especially for production or data-changing statements.

Actual timing can also be perturbed by instrumentation, cache state and concurrent activity. Treat one measured execution as evidence, not as a universal benchmark.

45. Multiply row counts by loops before declaring a small node harmless

A nested-loop inner index scan may show only two rows per execution, which looks trivial. If it runs two million times, the total work can be enormous.

This is a common plan-reading mistake: judging each operator by per-loop rows without considering how often it executes. The effective work is closer to rows-per-loop multiplied by loop count, plus the cost of each access and any caching effects.

Conversely, a large scan executed once can be cheaper than a small random lookup repeated millions of times. Plan analysis is about total work across the tree.

46. Cold-cache and warm-cache benchmarks answer different questions

The first execution may read pages from storage; later executions may find them in memory. A benchmark that runs the same query ten times and reports only the fastest result can therefore exaggerate steady-state performance for workloads that do not repeatedly touch the same data.

Cold-cache testing can approximate first-touch or broader working-set behaviour, but forcibly dropping caches can be disruptive and unrealistic in shared systems. Warm-cache testing represents another legitimate condition.

Record the condition rather than pretend one is universally correct. For a user-facing repeated lookup, warm cache may be representative. For an overnight report scanning a new partition once, first-touch I/O can dominate.

47. A fast query in isolation can still harm the system under concurrency

A plan that completes in 50 milliseconds alone might saturate CPU, memory bandwidth or storage when 500 sessions execute it simultaneously. An index lookup pattern with many random reads can behave differently under contention than a sequential scan.

Measure throughput and tail latency at realistic concurrency, not only one-query latency. Observe whether a tuning change shifts cost onto writes, background maintenance or other tenants.

Query optimisation is a system problem. The fastest single query is not always the best overall plan if it consumes disproportionate shared resources.

48. Optimiser hints are diagnostic tools and contracts of last resort

Several database engines provide hints or controls that influence join order, index choice or optimiser transformations. MySQL, for example, documents optimiser and index hints as well as cost-model controls. MySQL 8.4 — Optimizer Hints.

Hints can be useful when the optimiser lacks information, when a regression needs immediate containment or when a highly stable workload has a known better path. But they also freeze an assumption about future data and engine behaviour.

Before forcing an index, ask why the optimiser rejected it. Perhaps the index truly is expensive. Perhaps statistics are wrong. Perhaps a parameter value differs. Perhaps the query returns too many rows. A hint can mask the cause and become harmful later.

Treat a persistent hint as owned technical debt: document the evidence that justified it, the engine/version assumptions, the validation workload and a review trigger.

49. Plan regressions are changes in strategy, not necessarily changes in SQL

A query can slow dramatically even when its text is unchanged. Possible causes include:

• data volume growth;
• distribution skew;
• statistics changes;
• index creation or removal;
• schema changes;
• engine upgrades;
• configuration changes;
• changed parameter values;
• partition growth;
• changed cache or concurrency conditions.

A regression investigation should therefore capture both the old and new context: query text, parameter values, schema/index definitions, statistics state, plan, row counts, engine version and relevant workload conditions.

Do not assume the old plan is permanently correct. The database may have grown enough that a previously good plan is no longer best. The objective is not to restore familiarity; it is to restore justified performance.

50. Index changes deserve release engineering

Creating a large index can consume I/O, CPU, temporary storage, locks or replication bandwidth depending on the database and chosen build mode. Dropping an index can expose hidden consumers. Rebuilding can create temporary duplication.

Plan the change:

• identify the target workload;
• estimate build size and operational impact;
• choose an engine-appropriate online/concurrent method where supported and required;
• test representative queries;
• observe writes and maintenance;
• verify replication/recovery implications;
• retain a rollback or forward-repair path.

An index is schema infrastructure. Treating it as a harmless query-level tweak underestimates its lifecycle.

51. Query plans should be observable through time

For important recurring queries, preserve enough history to answer:

• Which plan ran?
• Which parameter class was used?
• How many rows were estimated and returned?
• What was the latency distribution?
• How much I/O or temporary work occurred?
• Which schema/index version existed?
• Did an engine upgrade change the plan?

This is not an argument for logging every sensitive query value indefinitely. Query telemetry should follow data classification and minimisation principles. Normalised query identity, plan fingerprints, aggregate metrics and appropriately protected examples can often support diagnosis without creating a new sensitive-data store.

See Data Observability and Monitoring for the broader observability discipline.

Deep Architecture: correlation breaks naïve independence assumptions

Suppose a table records country_code and currency_code. If the optimiser estimates these predicates independently, it might treat country_code = 'SG' and currency_code = 'SGD' as two separate filters whose selectivities should be multiplied. In real business data they can be strongly correlated.

Imagine 60% of orders are from Singapore and 62% use SGD. Multiplying 0.60 × 0.62 gives an estimated joint selectivity of 37.2%. But if nearly every Singapore order uses SGD, the true joint selectivity can be close to 60%. A join or access path chosen under the 37.2% estimate is working from the wrong model even when each single-column statistic is accurate.

The same problem appears with school level and age, product category and price band, tenant and region, status and completion timestamp, or country and timezone. Correlation is a property of the domain, not merely a statistical nuisance.

Where an engine supports extended or multi-column statistics, they can represent dependencies that ordinary per-column summaries miss. Where it does not, schema design, query structure or targeted indexes may need to carry more of the burden. The important habit is to recognise correlation when estimate errors appear systematically on combinations of otherwise well-estimated predicates.

Do not add multi-column statistics indiscriminately. Like indexes, they have maintenance and planning value only when an important workload needs the extra information. Use estimate evidence to justify them.

Deep Architecture: nulls are a semantic state, not just another key value

Indexes and optimisers must operate under each database’s null semantics. A null often means that a value is unknown, not applicable or absent. SQL three-valued logic means column = NULL is not equivalent to column IS NULL.

From a planning perspective, the fraction of nulls can strongly affect selectivity. If 70% of rows have resolved_at IS NULL, an index designed solely for that predicate may touch most of the table. If only 0.1% are unresolved, the same predicate can be highly selective.

From a modelling perspective, several kinds of missingness should not be collapsed into one sentinel such as zero or an empty string merely to simplify indexing. That would move an operational inconvenience into the data model and make later analytics less trustworthy.

Partial indexes can sometimes focus on IS NULL or IS NOT NULL subsets where supported and useful. Again, the right design depends on the size and importance of that subset, not on a slogan such as “index nulls” or “indexes ignore nulls”. Engine behaviour differs.

Deep Architecture: collation and operator semantics determine what “ordered” means

An index orders values according to comparison semantics. For text, those semantics can depend on collation, locale, case rules and the operator class or equivalent mechanism supported by the engine.

The sequence expected by a user can differ from byte order. “Å”, “A”, “a” and accented variants may compare differently under different locales. A case-insensitive search can require semantics distinct from a case-sensitive index. A query using one collation may not be able to use an index built under incompatible ordering rules.

This matters for correctness as well as performance. An index that accelerates the wrong comparison semantics is not a valid optimisation. Before adding expression indexes or changing collations to speed a query, confirm how equality and ordering are supposed to behave for the application’s language and domain.

Do not assume advice from English ASCII examples transfers unchanged to multilingual education data, names or free text. Query optimisation always inherits the meaning of the comparison operators it is trying to accelerate.

Deep Architecture: bitmap-style access sits between point lookup and full scan

Some database engines can use one or more indexes to construct an intermediate bitmap or equivalent structure representing candidate row locations. The engine then visits relevant table pages in a more organised way than following each index entry independently.

This can be attractive when a predicate matches too many rows for individual random lookups but still excludes enough of the table to make a full scan wasteful. Combining two moderately selective indexes can also produce a useful intersection without a purpose-built composite index.

For example, suppose region = 'NORTH' matches 10% of rows and status = 'PENDING' matches 5%, with the intersection around 0.6%. A bitmap combination may find that intersection efficiently. But if one predicate matches 80%, its contribution may not justify the extra index scan.

The lesson is that access paths form a spectrum. A database is not restricted to “single index seek” versus “full table scan”. Plan reading should identify the exact access operator rather than treating every index-related plan as equivalent.

Deep Architecture: index-only does not always mean zero base-table work

A query whose referenced columns all appear in an index may be eligible for an index-only or covering strategy. Whether the engine can avoid consulting the base table on every entry depends on implementation details.

In MVCC systems, for example, visibility information may live separately from the index entry. The engine can sometimes use auxiliary visibility metadata to determine that a page’s tuples are visible without visiting each row, but recently modified pages may still require checks against the base storage.

This creates an operational subtlety. A covering index tested on a quiet, mostly stable table may appear nearly table-free. The same index on a heavily updated table can perform more base-row visits. The index definition did not change; the visibility state did.

Do not promise “no table reads” solely from the column list. Inspect actual plan evidence and engine documentation. The strong, portable statement is that the index can contain the required data and may enable an index-only path under suitable conditions.

Deep Architecture: MVCC history can change index and table economics

Multi-version concurrency control allows readers and writers to coexist by retaining versions according to transaction visibility rules. Those older row versions, deleted entries or dead tuples can increase the physical amount of data that storage and indexes must navigate until maintenance reclaims or marks them reusable according to the engine.

A table with ten million logical current rows can therefore occupy more physical space after heavy update/delete churn. Index pages can also contain entries associated with versions that no longer matter to new transactions until maintenance catches up.

When a query slows after a period of intense churn, do not inspect only the logical row count. Look at physical size, maintenance state and engine-specific indicators of bloat or fragmentation. A new index may mask the problem while doubling the amount of structure that maintenance must manage.

This does not transfer ownership of vacuuming, transaction visibility or storage reclamation to this article; those belong to database operations and transactional integrity. The indexing point is narrower: physical history changes access-path cost, so logical schema plus row count is not the whole performance picture.

Deep Architecture: foreign keys and indexes solve related but distinct problems

A foreign key expresses a correctness relationship: child values must reference permitted parent values under the database’s constraint rules. An index is an access structure. One does not automatically imply the other in every engine or direction.

The parent key referenced by a foreign key is commonly backed by a unique or primary-key structure because the database must identify a valid parent. The child foreign-key column may still benefit from its own index for joins, parent deletion/update checks or common child lookups, depending on engine behaviour and workload.

Suppose one school record has one million attendance children. Deleting or checking that school can require finding every matching child. A child-side index on school identity may turn a broad scan into a targeted range. But adding that index should still be justified against write cost and actual referential operations.

Do not cargo-cult “always index every foreign key” or “the database already indexes foreign keys”. Check your engine and workload. The semantic relationship and the physical access path should both be explicit.

Deep Architecture: window functions can move cost without changing row counts

A window function such as ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) can require rows to be grouped or ordered according to partition and ordering keys. An index matching that order can sometimes reduce sorting, but the query may still need to process many rows before a later filter removes most of them.

A common pattern selects the latest row per entity by ranking all historical rows and then filtering to row number one. On a huge table, a lateral lookup, engine-specific distinct-on pattern, aggregate/join strategy or appropriately ordered index can sometimes reduce work. The best form depends on the engine and exact semantics.

Do not rewrite window functions reflexively. They are expressive and can be well optimised. Instead, inspect how many rows enter the window operator, whether ordering is already available and whether the logical requirement truly needs every row ranked.

Deep Architecture: query shape can expose or hide optimisation opportunities

Common table expressions, subqueries, views and derived tables make SQL easier to organise. Depending on engine/version and semantics, the optimiser may inline, materialise, push predicates through or treat them as planning boundaries.

A readable CTE should not be removed merely because someone once learned that “CTEs are slow”. Nor should a complex nested query be assumed equivalent to its apparent text order. Modern optimisers often rewrite queries aggressively.

Use the plan to determine whether the query shape creates a harmful materialisation, prevents predicate pushdown or repeats expensive work. If it does, rewrite with a specific physical reason while preserving logical clarity.

Query optimisation is strongest when SQL remains understandable enough for humans to verify while giving the engine access to the information it needs. Obscure rewrites that depend on accidental planner quirks create maintenance risk.

Deep Architecture: zone maps and data skipping are cousins of coarse indexing

Analytical and columnar systems often maintain metadata such as minimum and maximum values for storage blocks or files. A query can skip entire regions whose summaries prove they cannot satisfy the predicate.

This resembles BRIN conceptually: store a compact summary about a range of physical data rather than an entry for every row. The result can be tiny metadata with large pruning value when physical clustering aligns with query predicates.

If data is poorly clustered, summaries overlap broadly and skipping becomes less effective. Sorting or clustering data can therefore improve scan pruning without adding a traditional row-level index.

The optimisation principle is the same: reduce the amount of physical data that must be opened before evaluation. The mechanism differs from a B-tree seek, so plan interpretation should name it accurately.

Deep Architecture: probabilistic filters can reject many non-matches cheaply

Bloom filters and related probabilistic structures can answer “definitely not present” or “possibly present” using compact memory. They permit false positives but not false negatives under the intended construction.

In join or storage contexts, a Bloom filter can eliminate many rows or blocks that cannot match a key set before more expensive processing. A false positive merely causes extra work; it should not cause an incorrect result because the real equality check still occurs later.

The useful design question is whether the eliminated work outweighs building and applying the filter. When nearly every value matches, the filter adds overhead with little benefit. When the build set is compact and most probes do not match, it can save substantial downstream work.

This is another example of optimisation as staged evidence: a cheap approximate test narrows candidates, then an exact operation preserves correctness.

Deep Architecture: there is no universal selectivity threshold for index use

Advice such as “use an index only when fewer than 5% of rows match” is tempting because it converts a complex decision into one number. It is not portable enough to be a law.

The break-even point depends on row width, index clustering, storage latency, cache state, table size, engine, access pattern, covering ability, parallel scan efficiency and result ordering. A covering index returning 20% of a narrow table may still be attractive. A badly clustered secondary index returning 1% of very wide rows can still be costly.

Use selectivity thresholds as local empirical observations, not inherited constants. If your production evidence repeatedly shows a break-even region for one table and workload, document that context and re-check it after significant growth or storage changes.

Deep Architecture: trustworthy constraints reduce optimiser uncertainty

Primary keys, unique constraints, not-null constraints and valid foreign-key relationships encode facts that the optimiser may use during transformation and cardinality reasoning. They also protect the data model from states that would invalidate those assumptions.

A query joining a child to a unique parent is different from a query joining two uncontrolled many-valued keys. A predicate on a not-null column has different selectivity possibilities from one where nulls dominate. A unique filter can establish at most one row.

Do not invent constraints for performance. The constraint must be true. But when a relationship is genuinely invariant, expressing it declaratively can improve correctness, documentation and optimisation together.

Deep Architecture: plan stability is not the same as plan quality

Teams under incident pressure sometimes ask for a plan that “never changes”. Stability can reduce surprise, but a frozen plan ignores data growth, distribution change and engine improvements.

A better objective is bounded adaptability: critical queries have representative regression tests, plan changes are observable, severe regressions trigger review, and emergency containment is possible. The optimiser remains free to choose a better route when evidence changes.

If a workload truly requires a fixed physical contract for regulatory or safety reasons, document that as an explicit requirement and own the lifecycle. Most ordinary application queries need reliable performance, not permanent allegiance to one operator tree.

Deep Architecture: multi-tenant skew can make one logical table several physical workloads

In a shared SaaS table, tenant A may hold 40% of rows while thousands of small tenants share the remainder. A query filtered by tenant_id has radically different selectivity depending on which tenant is supplied.

A composite index starting with tenant can be useful for isolation of small tenants, yet the dominant tenant may still match so many rows that broad scans or partitioning become attractive for some jobs. Generic statistics may underrepresent the dominant tenant unless the distribution summary captures it.

Operational dashboards should therefore test at least three parameter classes: dominant tenants, ordinary tenants and very small tenants. A benchmark using only a random small tenant can produce a misleadingly optimistic plan picture.

Deep Architecture: generated columns can turn repeated derivation into an indexable contract

Some engines allow generated or computed columns derived from other fields and permit indexing them under defined rules. This can make a repeated transformation explicit in the schema rather than scattering function logic across queries.

For example, an application might derive a normalised search key from several source fields. If that derivation is stable, deterministic and broadly useful, a generated column plus index can create a clear access path.

The cost is coupling. Changing the derivation becomes a schema/data migration, and the stored or indexed representation must remain consistent with source values. Use this technique when the derived key has durable domain meaning or clear workload value, not merely to accommodate one temporary report.

Deep Architecture: compare plans by total work and receiver outcome

Suppose Plan A reads 500 pages and returns in 12 milliseconds while Plan B reads 50 pages and returns in 18 milliseconds because of CPU-heavy expression evaluation. Which is better? For one query, A is faster. Under storage contention, B may scale better. If Plan B consumes much more CPU, A may scale better under another workload.

There is no one-dimensional answer without the receiver objective. Latency, throughput, resource isolation, predictable tail behaviour and infrastructure cost can all matter.

Record the objective before the optimisation. “Reduce p99 API latency without reducing peak insert throughput by more than 2%” is a clearer engineering target than “make query faster”. The plan comparison can then use evidence relevant to the actual decision.

52. Plan laboratory: one hundred million orders

We will now work through a synthetic example end to end. The numbers are invented for teaching. The point is not to prove a benchmark result for any database engine; it is to show how selectivity, index shape and plan choice interact.

Assume an orders table with 100,000,000 rows. It contains 2,000,000 distinct customers. Most customers have modest histories, but a small business segment has very large histories. The table spans five years. About 4% of rows are from the most recent thirty days.

We need this query:

SELECT order_id, ordered_at, total_amount
FROM orders
WHERE customer_id = :customer_id
  AND ordered_at >= :cutoff
ORDER BY ordered_at DESC
LIMIT 20;

The first mistake would be to declare an index before examining the workload. We need to know whether typical customers have 10 orders or 100,000, whether the newest twenty orders are usually recent, whether the query runs thousands of times a second or once a day, and whether writes dominate the table.

For the exercise, assume a typical customer has 50 lifetime orders and an enterprise customer has 50,000. That skew will become important.

53. Candidate A: full scan

A full scan tests 100,000,000 rows for customer and date conditions. If each query concerns one customer, the logical result can still be tiny while the examined population is enormous.

For a one-off analytical query in a columnar engine, parallel scan behaviour might be acceptable. For a high-frequency operational lookup in a row-store, it is an obvious candidate for improvement.

The important diagnosis is not “scan bad”. It is the ratio between work and result. If the query returns twenty rows after examining one hundred million, we have strong reason to look for a selective access path.

54. Candidate B: index on customer_id

Now assume a B-tree index on customer_id. For a typical customer with 50 lifetime rows, the engine can find those 50 candidates quickly, filter by cutoff and sort or traverse them as needed.

That is already a dramatic reduction from 100,000,000 candidates to roughly 50 for an ordinary customer. But the enterprise customer with 50,000 rows is different. The same equality lookup now produces a much larger candidate set. If only 4% are recent, approximately 2,000 rows survive the date condition before the top twenty are returned.

The same index can therefore produce excellent performance for one parameter class and mediocre performance for another. Parameter skew is visible in the data rather than hidden in the SQL text.

55. Candidate C: composite index on customer_id and ordered_at

Now build the conceptual access path (customer_id, ordered_at). For each customer, entries are ordered by time. The engine can navigate to the customer’s range and begin from the newest end, applying the cutoff while walking only as far as needed.

With ORDER BY ordered_at DESC LIMIT 20, a typical query may stop after roughly twenty matching entries rather than gathering and sorting the customer’s complete history. For the enterprise customer, the advantage can be even more pronounced because the engine does not need to examine 50,000 historical rows merely to find the newest twenty.

This is the key insight: the composite index is not merely “more columns therefore faster”. It matches the sequence of the question: identify one customer, then traverse that customer’s time order.

56. Candidate D: make the path covering

If the query needs order_id, ordered_at and total_amount, an engine-specific covering strategy can make those values available directly from the index. This can reduce base-table lookups for the final twenty rows.

But widening the index means every order now carries more bytes in the access structure. If total_amount changes after adjustments, the index may require additional maintenance. If many queries need different output columns, attempting to cover all of them can bloat the index.

The measured question is whether the reduced table access justifies the larger structure for this workload. Covering is an optimisation, not a moral virtue.

57. Work the selectivity arithmetic explicitly

Suppose the typical customer has 50 rows. The customer predicate selectivity for that customer is:

50 / 100,000,000 = 0.0000005, or 0.00005%.

That is extraordinarily selective. If four percent of those rows fall in the recent window, the combined expectation is about two recent rows—not enough for the LIMIT 20, so the engine may need to walk past the cutoff or return fewer than twenty.

For the enterprise customer with 50,000 rows, customer selectivity is 0.05%. Four percent recent implies about 2,000 recent orders. The date condition still leaves far more than twenty rows, which makes ordered early-stop behaviour especially valuable.

These are synthetic assumptions, but the calculation demonstrates why one global “average customer” estimate can be misleading. If the statistics or planner model the customer distribution too coarsely, they may fail to distinguish ordinary and enterprise parameters.

58. A small estimation assumption can change the chosen plan

Imagine the optimiser estimates that every customer has 50 rows. For the enterprise customer it predicts 50 but receives 50,000—a thousandfold error before the date filter.

If the query joins those orders to another large table, a nested-loop strategy chosen under the 50-row belief can execute far more inner lookups than expected. The root problem may appear downstream as a join slowdown, but the first major error occurred at the customer selectivity estimate.

This is why plan diagnosis begins at the earliest large estimate divergence rather than the slowest visible operator alone.

59. Quantify the write trade-off too

Suppose the table receives 10,000 new orders per second during peak ingestion. Adding one index means each insert must also maintain that index. Adding four overlapping indexes multiplies that maintenance further.

Even if each index update is individually cheap, the total affects CPU, logging, storage bandwidth and replication. A read improvement measured on a quiet laptop does not establish that the production write workload can absorb the new structure.

For a high-write table, test insertion throughput and tail latency before and after the proposed index. Measure replica lag and storage growth as well as SELECT performance. An index that saves one query but destabilises ingestion has not optimised the system.

60. Add a join and watch the plan space change

Now extend the query:

SELECT o.order_id, o.ordered_at, o.total_amount, s.status_name
FROM orders o
JOIN order_status s ON s.status_code = o.status_code
WHERE o.customer_id = :customer_id
  AND o.ordered_at >= :cutoff
ORDER BY o.ordered_at DESC
LIMIT 20;

If order_status contains only a few dozen rows and status_code is unique, the join is cheap relative to finding the orders. The optimiser can use the small table efficiently through a lookup, hash or other plan.

But if status_code is unexpectedly duplicated, each order can expand into several output rows. A relationship assumed to be many-to-one has become many-to-many. Performance and correctness can fail together.

Indexes cannot repair a wrong data model. The optimiser needs true constraints and the application needs a true join relationship.

61. Read the hypothetical plan from the first row-count error

Assume an actual plan reports these simplified numbers:

The sort may consume visible runtime, but its unexpectedly large input began with the access estimate: 40 estimated versus 18,000 actual. Fixing or better representing that distribution can change the entire downstream plan.

Maybe the customer is a large enterprise tenant. Maybe statistics are stale. Maybe customer and date are correlated. Maybe the cutoff parameter differs from the assumption used in planning. The plan tells us where to investigate; it does not tell us which explanation is true automatically.

62. Tune in an order that preserves causality

A disciplined tuning order for this case is:

1. Confirm the query result is logically correct.
2. Capture representative parameters and the current plan.
3. Compare estimated and actual rows.
4. Identify the first material estimation or access-path problem.
5. Check existing indexes and statistics.
6. Test one bounded change.
7. Re-run the representative workload.
8. Measure write and system impact.
9. Record the reason for retaining or rejecting the change.

Changing five indexes, three parameters and two SQL clauses simultaneously can make the query faster while destroying the ability to know why. One bounded change at a time creates evidence that can be maintained later.

63. Full-text, spatial, JSON and vector search need purpose-built access paths

A B-tree excels at ordered scalar comparisons. Searching for words inside long documents, spatial overlap, component membership in JSON-like structures or nearest vectors involves different relationships.

Full-text systems often use inverted indexes mapping terms to documents or positions. Spatial systems use geometric access methods. Document/array containment can use inverted or specialised operator-class structures. Vector search systems may use exact scans or approximate-nearest-neighbour index structures depending on engine and extension.

The general design rule remains the same: identify the operator semantics first. “Find rows where identifier equals X” and “find the twenty vectors nearest to query vector Q” are not variations of one B-tree problem.

Do not add a vector index merely because a table contains embeddings. If the corpus is small, exact search may be adequate and simpler. Approximate indexes trade memory, build time and possibly recall for faster search; those trade-offs need measured evaluation against the retrieval job.

See AI Data Management for embedding provenance and index synchronisation. This article owns the physical search-path reasoning, not the AI source-authority problem.

64. A leading wildcard is a clue that ordinary ordered lookup may not fit the question

A predicate such as:

WHERE title LIKE '%algebra%'

asks whether a substring occurs anywhere inside the value. A conventional B-tree ordered by the full string cannot normally jump directly to arbitrary internal substrings the way it can jump to a leading prefix such as 'algebra%', subject to collation and engine rules.

The repair may be full-text indexing, trigram-like indexing where available, a search service or a redesigned requirement. Blindly adding another ordinary B-tree on the same text column does not change the relationship being searched.

65. BRIN illustrates the difference between exact entry indexing and range summaries

Imagine a telemetry table physically appended by timestamp. A full B-tree on timestamp can be large because it stores entries for individual values or rows. A block-range summary index can instead record min/max-like information for physical ranges.

If the query asks for a narrow recent time range and timestamps are strongly correlated with physical location, most block ranges can be rejected quickly. The engine then scans the remaining candidate ranges.

If timestamps are randomly distributed across the table, those summaries become much less selective. The index is not “bad”; its physical-correlation assumption does not match the data.

PostgreSQL documents BRIN as storing summaries for consecutive physical block ranges and notes that it works especially well when indexed values correlate with physical row order. PostgreSQL — Index Types.

66. OLTP and analytical workloads reward different access strategies

An online transaction system often needs many small, selective, low-latency lookups and updates. Indexes that find one account, one order or one active task can be valuable.

An analytical workload may intentionally scan large fractions of a table, reading only a few columns and aggregating billions of rows in parallel. Large numbers of row-oriented secondary indexes can contribute less value there than partition pruning, columnar layout and vectorised execution.

Do not copy an OLTP indexing checklist into a warehouse or a warehouse tuning rule into a transactional application. Query optimisation begins with the receiver workload.

67. Removing an index safely requires more than an unused counter

Before dropping an index, classify its role:

• Does it enforce uniqueness?
• Does a foreign-key or engine feature depend on it?
• Does a rare critical query use it?
• Does it support ordering for an operational job?
• Does another index subsume its useful key order?
• Does a maintenance or emergency script depend on it?

Then collect a sufficiently long usage window for the business cycle. A seven-day observation cannot rule out a month-end close or quarterly report.

Where the engine supports invisible, disabled or hypothetical index techniques, they can help test removal or alternatives with lower risk. Otherwise stage the drop with clear rollback and monitor affected query fingerprints.

Index removal is change management. See Data Versioning and Change Management.

68. Overlapping indexes are not duplicates merely because they share columns

Consider:

INDEX A (customer_id)
INDEX B (customer_id, ordered_at)
INDEX C (customer_id, ordered_at, status)

It is tempting to declare A and B redundant because B begins with customer_id. Depending on the engine and workload, the narrower A may be smaller, denser and cheaper for some lookups. B may support date ranges and order. C may be wide enough to cost more on writes while serving another critical query.

The correct analysis compares actual usage and cost. Prefix overlap is a reason to investigate redundancy, not proof of redundancy.

69. Growth changes the break-even point between plans

A query that was fine as a table scan at 10,000 rows can become unacceptable at 100 million. An index that once fit entirely in memory may later compete with other working sets. A partial index can grow until it is no longer partial in practical effect.

Review index strategy at meaningful scale transitions rather than only after incidents. Track table rows, index bytes, distinct counts, skew, write rate and important query latency. Capacity trends help explain why a once-good plan deteriorated.

70. Statistics maintenance should follow change rate and consequence

Statistics on a stable reference table may remain representative for long periods. Statistics on a rapidly changing events table can age quickly. Engines also differ in automatic statistics collection and thresholds.

Design maintenance around evidence:

• How quickly does the distribution change?
• Which queries depend on accurate estimates?
• How expensive is statistics collection?
• Do bulk loads create known stale periods?
• Does a plan regression correlate with estimate drift?

MySQL’s documentation describes persistent InnoDB optimiser statistics and exposes last-update information for statistics tables. MySQL 8.4 — InnoDB Optimizer Statistics.

71. Benchmark the decision you actually need to make

A useful benchmark is not “index query is 3 ms on my laptop”. It states the dataset size, distribution, parameter classes, cache condition, concurrency, hardware or service tier, engine/version and write workload relevant to the decision.

If choosing between two indexes for a customer lookup, test ordinary and high-volume customers. Test recent and broad date ranges. Test warm and representative first-touch conditions if both matter. Include insert/update throughput if the index affects a write-heavy table.

Report distributions such as median and tail latency rather than a single fastest number. One spectacular run can be a cache artefact.

72. Performance regression tests need tolerances, not frozen exact timings

Exact wall-clock times vary. A regression test that fails whenever a query changes from 20.0 ms to 20.1 ms will create noise.

Better tests can combine structural and performance evidence:

• critical query still uses an acceptable access-path family;
• estimated/actual row error remains within a reviewed band for representative cases;
• no unexpected full scan appears on a huge table;
• no disk spill appears under the target workload;
• p95 latency remains within the service objective;
• write throughput remains within the accepted envelope.

A plan hash can help detect change, but a changed plan is not automatically a regression. The new plan may be better. Use the change as a trigger for comparison.

73. Indexes can expose information through access and operational side channels

An index is part of the protected data system. Permissions to inspect index definitions, plans, statistics and query text can reveal schema names, sensitive categories, tenant sizes or search patterns.

Plan telemetry should follow Data Classification and Sensitivity. A query plan containing a literal personal identifier should not be copied into a public incident report merely because it is “metadata”.

Likewise, an index built on a sensitive field is not de-identified by virtue of being an index. Search structures remain derived representations of the underlying data.

74. Education case: latest work for one learner

An education platform stores millions of submission records across many learners. A teacher opens one learner’s page and expects the latest ten submissions.

A plausible access pattern is learner_id = ? ORDER BY submitted_at DESC LIMIT 10. A composite ordered index beginning with learner identity and continuing with submission time can match that reader job well.

Now add a school-wide dashboard asking for all submissions in the last hour. That workload leads with time rather than learner. The original learner-first index is not automatically optimal for the new dashboard.

This illustrates why a database can legitimately need more than one access path over the same table. The two queries ask different ordered questions.

Do not index private student text into an unrestricted search service merely to accelerate teacher lookup. The access path must preserve the same entitlement boundary as the source data.

75. Finance case: selective account lookup versus broad close reporting

A finance system serves two distinct jobs. Customer support retrieves one account’s recent transactions. Month-end finance aggregates nearly all transactions for the period.

The support query benefits from a selective account/date path. The month-end report may prefer partition pruning and broad scans because it needs most rows in the month.

Trying to force the support index onto the close report can create millions of random row fetches. Trying to serve the support screen with the broad reporting scan creates unacceptable latency. The same table supports different physical strategies because the receiver jobs differ.

76. AI case: feature lookup and training scan are different queries

An online model may need the latest feature vector for one entity in milliseconds. Training may scan years of feature history for millions of entities.

The online path may use an entity/time index or dedicated online feature store. The training path may prefer partitioned columnar scans. A single physical representation rarely dominates both extremes.

Training-serving consistency is therefore not achieved by making training and serving execute identical queries. It is achieved by keeping feature definitions and point-in-time semantics aligned while allowing storage/access paths appropriate to each job.

See AI Data Management for the broader lifecycle.

77. Named failure modes

• Index-everything reflex: read latency improves in one test while writes, storage and maintenance deteriorate.
• Scan shame: a perfectly reasonable broad scan is “fixed” into millions of random lookups.
• Average-parameter blindness: a plan tuned for ordinary values collapses for a dominant or extreme tenant.
• Cardinality cascade: one early underestimate causes poor joins, undersized memory and spills downstream.
• Covering-index obesity: indexes become wide copies of the table.
• Permutation explosion: every query variation receives another overlapping composite index.
• Function-wrapped key: a predicate hides the searchable boundary from an ordinary index.
• Implicit-cast surprise: mismatched parameter types change index eligibility or estimates.
• Hint fossil: a forced plan survives long after its original data distribution changed.
• One-plan benchmark: only one parameter value and warm-cache run are measured.
• Unused-counter deletion: a rare critical index is dropped after a short observation window.
• Wrong relationship: an index is blamed for row explosion caused by an unintended many-to-many join.
• Plan-cost stopwatch: internal cost units are interpreted as milliseconds.
• Fast-alone failure: single-query latency improves while concurrency throughput collapses.
• Statistics ritual: ANALYZE is run repeatedly without identifying the estimation error.

78. A troubleshooting route for a slow query

1. State the receiver job and acceptable latency/throughput.
2. Capture the exact query and representative parameters.
3. Confirm result correctness first.
4. Inspect the current plan.
5. Compare estimated and actual rows where safe and supported.
6. Find the first major row-estimation or work-amplification point.
7. Check whether the predicate is indexable and type-compatible.
8. Inspect existing indexes before creating another.
9. Check statistics age/distribution only where estimates suggest a problem.
10. Check joins, sort, aggregation, loops and spills.
11. Test one bounded change.
12. Measure representative parameter classes and concurrency.
13. Measure write/storage impact.
14. Record why the change is retained.
15. Set a review trigger for future growth or version change.

79. An index review checklist

1. Which exact queries does this index support?
2. Which columns are navigation keys and which are merely included outputs?
3. What are the typical and worst-case selectivities?
4. Does key order match equality/range/order patterns?
5. Can another existing index already serve the job adequately?
6. Does the index enforce a correctness constraint?
7. How large is it relative to the table?
8. How often do indexed values change?
9. What write amplification does it introduce?
10. Does it increase replication or backup burden materially?
11. Can the query become index-only/covering under realistic engine conditions?
12. How does it behave for skewed parameter values?
13. Does it remain useful as the table grows?
14. Which owner will review it?
15. What evidence would justify dropping or replacing it?

80. A query-optimiser review checklist

1. What plan did the optimiser choose?
2. Which alternative access paths exist?
3. Where is the first large estimate/actual divergence?
4. Are histograms or multi-column correlations relevant?
5. Is a nested loop executing more probes than expected?
6. Is a hash/sort operation spilling?
7. Can filters or projection be applied earlier?
8. Does the plan exploit required ordering?
9. Is LIMIT enabling early stop or merely trimming after full work?
10. Do prepared parameters represent several selectivity classes?
11. Did an engine/schema/statistics change alter the plan?
12. Would a hint treat the cause or merely freeze the symptom?
13. What happens under realistic concurrency?
14. What is the effect on writes and other workloads?
15. Can the final decision be reproduced from recorded evidence?

81. A maturity ladder

1. Reactive: add indexes when users complain.
2. Plan-aware: inspect execution plans before changing schema.
3. Statistics-aware: compare optimiser beliefs with actual row distributions.
4. Workload-aware: design indexes around representative query classes and writes.
5. Lifecycle-aware: own, review and retire indexes intentionally.
6. Regression-aware: monitor plan and performance changes through releases.
7. System-aware: optimise throughput, tail latency, storage and maintenance together.
8. Adaptive: data growth, distribution change and incidents continuously refine access paths.

82. Teaching workshop: choose the path, then defend it

Exercise 1: one row from one hundred million

A table contains 100 million immutable records. A unique identifier lookup runs 5,000 times per second. No index exists. What is the first design candidate?

Suggested answer: an index supporting equality on the unique identifier is strongly justified as a candidate because the workload repeatedly requests tiny results from a huge population. The exact index type and storage implications depend on the engine. Verify write/build cost and correctness constraints before release.

Exercise 2: 85% of the table

A reporting query filters status = 'CLOSED' and 85% of rows are closed. An index exists on status, but the optimiser chooses a scan. Is the optimiser obviously wrong?

Suggested answer: no. Following index references for most rows may cost more than scanning. Inspect row width, storage, cache and plan evidence before forcing the index.

Exercise 3: composite order

Queries almost always use tenant_id = ? AND created_at BETWEEN ? AND ?. Should the index be (created_at, tenant_id) or (tenant_id, created_at)?

Suggested answer: the second order is the natural first candidate for a B-tree because equality on tenant narrows the key space before the time range. But representative workload, ordering requirements and engine behaviour still need testing.

Exercise 4: wrong estimate

A plan estimates 200 rows from an access path but returns 2,000,000. The downstream hash aggregate spills. Where should investigation begin?

Suggested answer: at the access-path estimate. Determine whether skew, stale statistics, correlation, parameter sensitivity or predicate formulation explains the millionfold error. Increasing aggregate memory may hide the symptom without correcting the planner’s world model.

Exercise 5: unused index

An index has zero recorded reads for 21 days. Can it be dropped?

Suggested answer: not from that fact alone. Determine whether it enforces uniqueness, supports monthly/quarterly work, is used by another path not reflected in the counter, or exists for emergency operations. Use a business-cycle-appropriate review window and owner evidence.

Exercise 6: fast on laptop

A new index reduces one query from 800 ms to 15 ms on a development laptop. Production inserts slow by 20%, and replica lag increases. Is the change a success?

Suggested answer: not yet. The read improvement is real evidence for one path, but the system-level cost may be unacceptable. Evaluate the receiver importance, production write SLOs and whether a narrower alternative achieves enough read benefit with less maintenance.

Exercise 7: covering everything

A team proposes adding fifteen included columns so a dashboard query never visits the base table. What questions should be asked?

Suggested answer: how often the query runs, index size, update frequency of included columns, cache impact, whether all fields are needed, whether a materialised analytical product is actually the right owner, and whether a smaller covering set achieves most of the benefit.

83. Primary-source reading guide

The following documentation supports the engine-specific examples in this article. It is not a cross-engine benchmark and does not establish that one database is generally superior.

• PostgreSQL 18 — Chapter 11: Indexes, including index types, multicolumn indexes, expression/partial indexes and index-only scans.
• PostgreSQL 18 — Multicolumn Indexes, for current documented multicolumn behaviour and cautions.
• PostgreSQL 17 — Index Types, for B-tree, Hash, GiST, SP-GiST, GIN and BRIN examples.
• MySQL 8.4 — Optimization and Indexes, for index trade-offs and engine-specific optimisation guidance.
• MySQL 8.4 — Controlling the Query Optimizer, including cost model and optimiser statistics.
• MySQL 8.4 — Understanding the Query Execution Plan.
• SQLite — Query Planning, with clear examples of multi-column and covering indexes, sorting and cost-based choices.

Documentation evolves. Check the exact engine/version in use before applying syntax, limits or planner controls from an example.

84. The deeper principle: make the cheap path correspond to the real question

An index is useful when its organisation mirrors a repeated question closely enough that the database can avoid unnecessary work. The optimiser is useful when it can compare several possible routes using evidence that resembles the current data. Query tuning fails when those two layers drift away from the receiver job.

A wide table scan can be correct. A narrow index lookup can be correct. A hash join, merge join or nested loop can each be correct. The engineering task is to make the cheapest valid path visible and to give the optimiser enough truthful information to choose it.

This is why indexing is not a collection of magic recipes. It is applied modelling: what is being searched, how selective is the question, what order matters, how values are distributed, how often data changes, what other workloads share the system, and what evidence proves the improvement after release.

Final idea: do not optimise the syntax. Optimise the path from the receiver’s question to the smallest justified amount of physical work—and verify that the database is choosing that path for the data and workload that actually exist.

Data Management Series

• What Is Data Management?
• Database Management and Transactional Integrity
• Data Partitioning and Sharding
• Data Caching and Materialisation
• Data Observability and Monitoring
• Data Dependency and Impact Analysis
• Active Metadata and Metadata Automation

Data Management Series · DATA.MANAGEMENT.062 · Educational technical edition.