Ecommerce database indexes: EXPLAIN, query plans and workload-driven maintenance

Start with the real slow query, not a list of columns

A useful index begins with an observed ecommerce path: catalogue search, warehouse availability, cart updates, orders or reconciliation. Adding one because a column appears frequently in filters mistakes frequency for selectivity. First capture the parameterised query, duration, returned rows, execution frequency, transactional context and application version. Preserve the parameter distribution and peak conditions too, because identical SQL can behave very differently for one order and for a broad date range.

Freeze the query shape and its operating context

Redact sensitive values in samples without losing types, predicates, ordering, joins or limits. Check whether the application sends the expected shape or generates variants that prevent reuse. Record concurrency, warm or cold cache conditions and table volumes. The opening question is not “which index is missing?” but “which access is expensive, under which parameters, and what does it do to the customer journey?”

Read EXPLAIN as the optimiser’s hypothesis

EXPLAIN exposes the chosen plan: operation order, access methods, joins, sorting and estimates. Merely finding a full scan is not enough. Sequential access may be sensible for a small table or when the query returns a large share of its rows. Look instead for nodes that amplify work: many rows processed to produce few, avoidable sorting, joins driven by poor cardinality estimates or filters applied too late.

PostgreSQL reports node costs and row estimates; MySQL documents EXPLAIN as a way to see how statements are executed. Their representations and cost values are not interchangeable. Maintain a reading checklist for the engine and version you actually run, and never compare cost numbers directly across database products.

Separate estimated from actual values

The gap between estimated and actual rows is often more useful than an isolated duration. It can point to stale statistics, heavily skewed values, correlated columns or unrepresentative parameters. Before adding an index, refresh statistics through the engine’s normal procedure and repeat the plan with the same sample. If the estimate remains wrong, document the distribution and consider richer statistics or a cautious query rewrite rather than converting every anomaly into another index.

Run EXPLAIN ANALYZE under controlled conditions

A measured plan can reveal actual rows, loops and node timing, but the statement is executed. Do not try it casually on a data-changing statement or an expensive production query. Reproduce representative data and parameters, set time limits, watch load and locks, and agree an observation window for unavoidable production checks. For data-changing statements, rolling back a transaction can help in some situations, but it does not necessarily neutralise every external effect or function with side effects.

Compare at least one baseline plan and one candidate. Keep parameters, data volume and cache conditions as stable as practical. One lower elapsed time is not proof of a durable gain: the result must cover the main workload shapes without moving unacceptable cost to writes, replicas or maintenance.

Design composite key order from predicates and plans

In a multicolumn B-tree, key order affects how much of the structure the engine can narrow. PostgreSQL describes the role of equality constraints on leading columns and the first following inequality, while also documenting optimisations whose use can vary. Begin with real equality predicates, ranges, joins and ordering. Do not blindly apply “most selective first”; inspect plans for the important combinations and assess whether adjacent queries can reuse the structure.

Avoid near duplicates. A composite index may serve access paths that use its prefix, but engines differ in their treatment of skipped columns, sort direction and predicates. List the queries expected to benefit and those deliberately excluded, then confirm that the candidate does not merely copy an existing index with one extra field.

Covering and filtered designs are engine-specific

A covering index stores search keys plus additional data needed by a query, potentially reducing visits to the base structure. The details vary. In SQL Server, included columns are not key columns, and its design guide warns against excessively wide indexes; other engines expose different syntax and eligibility rules. A SQL Server filtered index covers only rows matching a defined predicate and is valuable only when that predicate reflects the workload. Do not translate the technique mechanically into a supposedly equivalent PostgreSQL or MySQL feature: design and test the engine-native option.

Put write and build costs into the same budget

Every index adds work to inserts and deletes, and to updates that change data stored in the index; the cost of other updates depends on the engine. On carts, inventory and orders, faster reads can increase insert or update latency, logging, storage, cache pressure and maintenance work. Before release, record expected size, write volume on affected columns and impact on the critical path. A wide index on a frequently updated table needs a stronger case than a compact structure over mostly static data.

Index creation is an operation to plan in its own right. Check the online or concurrent options offered by the deployed version, estimated duration, temporary space, replication effects and cancellation behaviour. Labels such as “online” do not promise zero locking or zero resource consumption. Read the documentation for the deployed database version and establish abort thresholds.

Treat plans and indexes as a managed lifecycle

Attach an owner, target queries, hypothesis, review date and indicators to every index. Measure percentile latency, rows examined versus returned, plan frequency, reads, writes and structure growth. Retain plan fingerprints so changes after data growth, statistics refreshes or version upgrades become visible. A changed plan is not automatically a regression; correlate it with workload cost and outcome.

Retire an unused index without shortcuts

A zero-use counter is insufficient: it may have been reset by a restart and may omit monthly jobs, emergency procedures or constraint duties. Observe a period that includes relevant trading cycles, verify whether the structure enforces uniqueness or relationships, search plan history and consult owners. Where available, make the index invisible or disable it in a controlled environment; otherwise prepare a reversible removal with the definition preserved, the DDL recorded and immediate monitoring.

Roll out one database change at a time

Create the index, refresh statistics and compare plans without rewriting the query in the same release. Stage exposure while watching the target case, write paths and neighbouring queries. Define rollback conditions beforehand: queue growth, write-operation latency, storage pressure or deterioration of a frequent plan. If the optimiser still chooses another path, avoid making permanent hints the first response; revisit estimates, types, predicates and the expected benefit.

Measure the outcome and keep the index set focused

Success is not “the index was used”; it is less work for an important query at an acceptable operating cost. Compare latency, processed rows, CPU and I/O usage, locks and write throughput over equivalent windows. Revisit overlaps, growth and plan changes on a schedule. To turn analysis, rollout and maintenance into a verifiable practice, explore our digital systems and ecommerce services. A small, justified and observed portfolio is more dependable than a collection created after every spike.