Query Optimisation Basics Every Developer Should Know

Picture the scene: it’s 9am on a Monday, and a UK fintech dashboard that normally loads in under a second is suddenly crawling. Support tickets are piling up, the product team is asking questions, and after twenty minutes of digging, the culprit turns out to be a single SQL query doing a full table scan across millions of transaction rows. We’ve seen this happen more times than we can count, and it’s almost never a hardware problem — it’s a query problem. In this post, we’re walking through the query optimisation fundamentals every developer working with database systems should have in their toolkit, from reading execution plans to choosing the right indexes and knowing when to cache.

Why Query Optimisation Matters in Modern Database Systems

Slow queries aren’t just an engineering annoyance — they directly affect revenue, retention, and infrastructure costs. Every extra second of latency on a checkout page or a banking app erodes user trust, and every inefficient scan burns CPU and memory that you’re paying for in the cloud. Even teams with sophisticated big data management pipelines in place often find that the weakest link is still a hand-written SQL query that nobody has reviewed since it was first deployed.

The Cost of Slow Queries in UK Fintech and Media

Monzo, a UK digital bank, relies on efficient SQL queries for real-time transaction processing. When you tap your card, the balance update, fraud checks, and notification all depend on queries returning in milliseconds, not seconds. The same principle applies in media: BBC iPlayer uses big data management to handle millions of streaming requests daily, and any query that slows down recommendation or playback-start logic gets multiplied across that entire audience. A 200ms regression in one query can become hours of aggregate user waiting time across a single evening.

How Optimisation Fits into Big Data Management

Big data management is often associated with distributed systems like Spark or Kafka, but the relational layer underneath still matters enormously. Data lakes and warehouses frequently feed into SQL-based reporting tools, and a poorly optimised join in a warehouse query can cost far more than the same query on a small transactional database. Optimisation is not a legacy concern — it’s a core discipline that sits alongside partitioning, replication, and schema design.

Understanding SQL Execution Plans: Your First Diagnostic Tool

Before you change a single line of SQL, you need to know what the database is actually doing. Execution plans — produced by EXPLAIN in PostgreSQL and MySQL — show the access paths, join strategies, and estimated costs the optimiser has chosen. If you want realistic datasets to practise on, data.gov.uk provides open datasets that developers can use to practice query optimisation, from transport statistics to NHS waiting times.

Reading EXPLAIN Output in PostgreSQL

Running EXPLAIN ANALYZE gives you both the planner’s estimates and the actual runtime. Look for the difference between estimated rows and actual rows — a large gap usually means stale statistics and a bad plan. Also check the node types: Seq Scan, Index Scan, Hash Join, Nested Loop, and Sort each tell a different story about how the data is being accessed.

Common Red Flags: Full Scans and Nested Loops

Two patterns show up again and again in slow queries:

• Sequential scans on large tables — often caused by a missing index, a function wrapped around an indexed column, or a type mismatch in the WHERE clause.
• Nested loops over big result sets — fine for a handful of rows, catastrophic when the outer relation returns hundreds of thousands.

If you see either of these on a hot path, that’s your first place to investigate.

Indexing Strategies That Actually Work

Indexes are the single highest-leverage tool in query optimisation, but they’re also the most commonly misused. Ocado, a UK online grocery company, uses data analytics for inventory and delivery optimisation, and its workloads are a good illustration: read-heavy lookups by product ID, order ID, and time window, mixed with periodic batch writes. Getting the index mix right is the difference between sub-second queries and constant firefighting.

When to Use Composite vs. Single-Column Indexes

A composite index on (customer_id, created_at) will serve queries filtering by customer alone, or by customer and date together — but not by date alone. Column order matters. As a rule of thumb, put the most selective and most frequently filtered column first, and only build a composite index when you actually have queries that use the leading columns.

Covering Indexes for Read-Heavy Workloads

A covering index includes every column a query needs, so the database can answer it from the index alone without touching the table. In PostgreSQL you can use the INCLUDE clause; in MySQL, add the extra columns to the index. The trade-off is real: every index you add speeds up reads but slows down inserts, updates, and deletes. Over-indexing is a genuine problem on write-heavy tables, so audit regularly and drop what isn’t being used.

Writing Smarter SQL: Joins, Subqueries, and Aggregations

Query rewriting is often cheaper than adding infrastructure. A few habits pay off consistently:

1. Prefer EXISTS over IN when the subquery returns a large set — the optimiser can short-circuit as soon as it finds a match.
2. Avoid SELECT * in production code; fetch only the columns you need, which helps covering indexes and reduces network overhead.
3. Use window functions (ROW_NUMBER, RANK, SUM() OVER) for analytics instead of correlated subqueries or self-joins.

DASFAA (Database Systems for Advanced Applications) is a leading international conference with UK academic participation, and much of its published research on join ordering and cost-based optimisation directly informs how modern query planners behave. Understanding that research helps you write SQL that works with the optimiser rather than against it.

Join Order and Join Types: What the Optimiser Sees

The optimiser picks join order based on cardinality estimates and available indexes. If statistics are stale, it may choose a nested loop where a hash join would be far faster. Running ANALYZE regularly, and giving the planner accurate row estimates, is often the cheapest performance win available.

Subqueries vs. CTEs: Performance Trade-offs

In older PostgreSQL versions, CTEs were an optimisation fence — the planner couldn’t push predicates into them. Modern versions (12+) can inline them, but the behaviour still depends on whether the CTE is referenced once or multiple times. If a CTE is used repeatedly, materialisation is usually correct; if it’s used once, a plain subquery often performs better.

Caching, Partitioning, and Other Big Data Management Tricks

When query rewriting and indexing aren’t enough, architectural techniques take over. Query result caching with Redis or Memcached can eliminate repeated work entirely for dashboard-style workloads where the same aggregate is requested every few seconds. Partitioning keeps large tables manageable, and columnar storage engines dramatically accelerate analytical scans.

Partitioning Large Tables in PostgreSQL

Declarative partitioning (range, list, or hash) lets the planner prune irrelevant partitions. A time-series table partitioned by month means a query for “last 7 days” only touches one or two partitions instead of the full history. This is especially valuable for public sector datasets — the kind of large, append-only data published on data.gov.uk — where queries almost always target a recent window.

When to Cache Query Results

Cache when the underlying data changes infrequently relative to read frequency, and when the query is expensive. Avoid caching user-specific data with short TTLs unless you have a clear invalidation strategy. A cached query that returns stale financial data is worse than a slow one that returns the truth.

Tools and Habits for Continuous Query Optimisation

Optimisation isn’t a one-off project. We recommend building a routine around it: review slow query logs weekly, run EXPLAIN ANALYZE on any query over your latency threshold, and A/B test index changes against realistic workloads before rolling them out.

Monitoring Slow Query Logs in Production

PostgreSQL’s log_min_duration_statement and MySQL’s slow query log give you a ranked list of offenders. Pair them with tools like pgAdmin, MySQL Workbench, or Datadog for trend analysis and alerting. The goal is to catch regressions before users do.

Learning from DASFAA and UK User Groups

Beyond tooling, staying current matters. DASFAA conferences publish research that frequently becomes the next generation of planner features, and UK user groups for PostgreSQL, MySQL, and broader data engineering communities offer practical, peer-tested advice. Attending or reading proceedings is one of the highest-return habits we’ve adopted as a team.

Query optimisation is an ongoing practice, not a one-time fix. Start with execution plans, index deliberately, rewrite the worst offenders, and layer in caching and partitioning where they earn their keep. Whether you’re building the next Monzo-scale fintech platform or just tuning a reporting query on data.gov.uk, these basics will serve you well in any UK-based project.

FAQ

How often should we review query performance?

We recommend a weekly review of slow query logs and a deeper audit each quarter, plus an immediate review any time a new feature ships that touches a high-traffic table.

Is indexing always the answer to a slow query?

No. Indexes help reads but hurt writes, and they can’t fix bad join logic or missing predicates. Always check the execution plan before adding an index.

Do we need a separate analytics database for big data workloads?

Not always, but for heavy analytical queries on large datasets, a columnar warehouse or a partitioned PostgreSQL setup often outperforms the transactional database by a wide margin.

What’s the quickest win for a slow SQL query?

In our experience, running ANALYZE to refresh statistics and removing SELECT * from hot queries solves a surprising number of cases before any structural change is needed.

Where can we keep learning about query optimisation?

Follow DASFAA proceedings, experiment with open datasets on data.gov.uk, and join a UK user group for PostgreSQL or MySQL to swap real-world war stories.