Indexing Basics: Why Some Queries Are Fast and Others Crawl
Picture two queries hitting the same orders table in a UK e-commerce database. The first returns a customer’s recent purchases in 4 milliseconds. The second grinds away for two and a half minutes, pins a CPU core, and times out the checkout page. Same table, same server, same data volume — the only difference is how the query interacts with indexes. In this post we’ll walk through what indexes actually do, why the query planner sometimes uses them and sometimes doesn’t, and the mistakes we see most often when tuning database systems for UK teams.
What an Index Actually Does (and Why It Matters for Database Systems)
An index is a sorted lookup structure that the database maintains alongside your table. Instead of scanning every row to find matching values, the engine walks a compact structure and jumps straight to the relevant pages. The trade-off is blunt: you pay extra storage and slower writes in exchange for dramatically faster reads. Every index you add has to be updated on every INSERT, UPDATE and DELETE that touches its columns.
The library catalogue analogy for database systems
Think of a library with a million books. Without a catalogue, finding every book by a particular author means walking every shelf. With a catalogue sorted by author, you flip to the right section and pull the exact shelf references. That’s what a B-tree index does in PostgreSQL and MySQL — it keeps keys sorted so the engine can binary-search its way to matching rows instead of reading the whole table.
B-tree vs hash indexes: when each helps
B-tree indexes are the default for good reason. They support equality, range queries, sorting and prefix matching. Hash indexes only support equality lookups, but they can be marginally faster for exact-match workloads. In practice, we rarely reach for hash indexes in PostgreSQL or MySQL because B-trees cover so much more ground with similar performance on equality checks.
Why every index slows down INSERT, UPDATE and DELETE
Each index is a separate structure that must stay consistent with the table. A single INSERT into a table with eight indexes triggers eight index updates. On write-heavy workloads — think a UK fintech ingesting transaction events — over-indexing can halve throughput. We always ask: which indexes does the read path actually use, and can we drop the rest?
Why Some Queries Are Fast: The Query Planner and Selectivity
The query planner is the component that decides how to execute your SQL. It estimates the cost of each plan — sequential scan, index scan, index-only scan, bitmap scan — and picks the cheapest. Selectivity is the key input: how many rows will this predicate match? A predicate returning 5 rows out of 5 million is highly selective and an index scan wins easily. A predicate returning 4 million rows is not, and a sequential scan is often cheaper than bouncing through an index.
Reading EXPLAIN ANALYZE output without panic
PostgreSQL’s EXPLAIN ANALYZE is a standard tool for diagnosing slow queries in UK database systems. It shows the plan the planner chose, the estimated cost, the actual time, and the row counts at each node. The trick is to compare estimated rows to actual rows — a large gap means stale or misleading statistics. Look for sequential scans on large tables, nested loops with high row counts, and sort nodes spilling to disk.
Selectivity, cardinality and statistics
Cardinality is the number of distinct values in a column. Statistics collectors like PostgreSQL’s ANALYZE sample the table to estimate distributions. If your statistics are stale, the planner may think a column has 100 distinct values when it actually has 10 million, and it will pick a wildly wrong plan. Running ANALYZE after bulk loads is one of the cheapest performance wins available.
When the planner ignores your index
Planners ignore indexes for good reasons: the predicate isn’t selective enough, the statistics suggest a sequential scan is cheaper, or the index doesn’t match the query shape. They also ignore indexes for bad reasons: the query wraps an indexed column in a function, uses a leading wildcard, or relies on an implicit type conversion. We’ll cover those next.
Why Other Queries Crawl: Common Indexing Mistakes
Most slow queries we investigate fall into a handful of patterns. Fixing them is often a one-line change.
Wrapping indexed columns in functions
If you write WHERE DATE(created_at) = '2024-06-01', the index on created_at is useless because the planner can’t map the function output back to index keys. Rewrite as a range: WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02'. Alternatively, create an expression index on DATE(created_at). We’ve seen this single fix cut query times from minutes to milliseconds at UK retail clients.
Leading wildcards and full-text search alternatives
LIKE '%smith%' cannot use a standard B-tree index because there’s no fixed prefix to seek to. Options include trigram indexes (pg_trgm), full-text search with tsvector, or moving the search workload to Elasticsearch where inverted indexes handle this pattern natively.
Composite index column order mistakes
A composite index on (customer_id, created_at) supports queries filtering by customer_id, or by customer_id and created_at. It does not efficiently support queries filtering only by created_at. Column order matters — put the most selective, most frequently filtered column first.
- Functions on indexed columns — rewrite as ranges or add expression indexes
- Leading wildcards — use trigram or full-text indexes
- Implicit type conversions — match column types in predicates
- Wrong composite column order — put the highest-selectivity column first
- Stale statistics — run ANALYZE after bulk changes
Indexing in SQL and NoSQL: Same Principles, Different Tools
The underlying idea — a sorted structure that trades writes for reads — is universal, but the implementations differ. MongoDB uses B-tree indexes by default, while Cassandra relies on partition keys and clustering columns, which behave more like a distributed primary key than a traditional secondary index.
Secondary indexes in MongoDB and Cassandra
MongoDB supports secondary indexes on any field, including compound and multikey indexes for arrays. Cassandra’s secondary indexes are more limited and often best replaced by denormalised query tables keyed by the access pattern. In Cassandra, you design the table around the query, not the other way around.
Partitioning and clustering in big data management
Snowflake and Google BigQuery use partition pruning and clustering instead of traditional B-tree indexes. Partition pruning skips entire partitions based on the filter, and clustering keeps related rows physically close within partitions. For analytics workloads scanning billions of rows, this is far more effective than any B-tree could be.
When NoSQL denormalisation replaces joins
In distributed NoSQL systems, joins are expensive or impossible. Teams denormalise — duplicating data across tables so each query hits a single partition. This shifts the indexing problem into a data modelling problem, and it’s a trade-off every data engineering UK team eventually confronts.
How We Tune Slow Queries: A Practical Checklist for UK Data Teams
Here’s the workflow we use when a production query misbehaves.
Finding slow queries in production safely
pg_stat_statements and SQL Server Query Store are common starting points for finding slow queries. Both aggregate execution time, call counts and rows returned, so you can rank by total time rather than average. UK fintech and retail teams often face latency SLAs that make index tuning business-critical — a 200ms regression on a payments endpoint is a board-level issue.
Creating indexes without locking tables
In PostgreSQL, CREATE INDEX CONCURRENTLY builds the index without blocking writes. In MySQL 8, online DDL achieves the same. Never run a blocking index build on a busy production table during trading hours.
Measuring improvement with real workloads
Benchmark with production-shaped data, not a toy dataset. A plan that looks great on 10,000 rows can collapse on 10 million. Re-run EXPLAIN ANALYZE, compare buffer reads, and check that the new index is actually being used.
Beyond Indexes: When Data Engineering UK Teams Need More
Indexes have limits. Very large tables, analytics workloads and streaming pipelines often need different tools.
Columnar storage and data analytics workloads
Columnar stores like Snowflake, BigQuery and Redshift read only the columns a query touches, which is ideal for aggregations over wide tables. For data analytics, this beats row-store indexing almost every time.
Materialised views and caching layers
When a query is expensive but the result changes slowly, a materialised view or a Redis cache removes the cost entirely. UK retail dashboards often rely on this pattern for daily sales summaries.
Sharding and read replicas at scale
When a single node can’t keep up, sharding splits data across servers and read replicas offload reporting traffic. Both add operational complexity, so we treat them as last resorts after indexing, partitioning and caching have been exhausted.
FAQ
How do I know if my query is using an index?
Run EXPLAIN or EXPLAIN ANALYZE in PostgreSQL, or view the execution plan in SQL Server. Look for “Index Scan”, “Index Only Scan” or “Bitmap Index Scan” nodes. A “Seq Scan” on a large table usually means the index isn’t being used.
Can I have too many indexes?
Yes. Every index slows writes and consumes storage. We regularly audit indexes and drop those with zero or near-zero usage reported by pg_stat_user_indexes or equivalent monitoring.
Do NoSQL databases use indexes the same way as SQL?
The principles are similar but the mechanics differ. MongoDB uses B-tree indexes by default, while Cassandra relies on partition keys and clustering columns. Snowflake and BigQuery use partition pruning and clustering instead of traditional B-tree indexes.
What’s the fastest way to find slow queries in production?
Enable pg_stat_statements in PostgreSQL or Query Store in SQL Server. Both give you ranked lists of slow queries by total execution time, which is far more useful than watching live activity.
When should I stop tuning indexes and change architecture?
When the workload is analytical rather than transactional, when the table is too large for a single node, or when the query pattern requires full-text search or aggregations across billions of rows. At that point, columnar stores, materialised views, caching or sharding become the right answer.
Indexing is a deliberate trade-off, not a silver bullet. Every index you add costs storage and write throughput; every index you remove risks a slow read. The only reliable way to get it right is to measure with real workloads, test changes safely, and iterate rather than guess.