How to Make SQL Queries Fly with Indexes and Execution Plans

SQL is the silent workhorse behind most of Australia's digital infrastructure. Whether it is the Australian Taxation Office processing millions of lodgements ahead of the 30 June deadline, a Sydney fintech running real-time fraud checks, or a Melbourne logistics company dispatching parcels across the country, slow queries translate directly into unhappy customers and wasted cloud spend. The difference between a query that finishes in milliseconds and one that crawls for minutes is almost always a question of how it is indexed and how the database engine decides to execute it.

Most developers learn SQL by writing queries that just happen to work, then quietly suffer as tables grow past a few million rows. The fix is rarely a bigger server or a clever rewrite of business logic. It comes down to two related skills: building the right indexes for the patterns your queries actually use, and learning to read the execution plan your database produces when you ask it to explain itself.

Understanding How the Optimiser Resolves a Query

When you run a SELECT statement, the database engine does not simply scan the table from top to bottom. It parses your text, validates it against the schema, and hands the result to the cost-based optimiser. The optimiser considers the tables involved, the available indexes, the join order, and the estimated number of rows, then it produces an execution plan. That plan is what the storage engine actually follows to retrieve the data.

The optimiser makes its choices based on statistics about the data distribution. If those statistics are stale, or if the database has no useful index, it falls back to a sequential scan, which is the database equivalent of reading every page of a phone book to find one name. For an Australian retailer running a sale during a long weekend, that scan can take long enough that customers abandon their carts before checkout completes.

The Mechanics of a Database Index

An index is a separate data structure that lets the engine locate rows matching a key without scanning the whole table. Most relational engines use a B-tree under the hood, which keeps the keys sorted and balanced so that a lookup, range scan, or insert all run in logarithmic time. You can think of it as the index at the back of a textbook. Instead of flipping through every page to find the section on recursive joins, you jump straight to the right page.

A useful mental model is the Yellow Pages. The directory is sorted alphabetically by trading name, but you only find businesses quickly if you know their name. If you want to find every plumber in Brisbane sorted by suburb, the phone book is the wrong tool, and you need a different index, ideally one keyed on the suburb column. The same logic applies to database indexes: they accelerate specific access patterns and do almost nothing for others.

When you are building out tooling around queries, especially for reporting pipelines, it pays to know where the supporting logic lives. A practical walkthrough of Python programming on the hello ML site covers how Python scripts often sit alongside relational stores, glueing them to APIs and dashboards.

Choosing the Right Index Type for the Workload

The simplest index is a single-column B-tree, suitable for equality lookups and bounded ranges. Composite indexes extend this idea across multiple columns, and their order matters enormously. An index on (state, postcode, suburb) will serve queries that filter on state alone, on state and postcode, or on all three, but it will not help a query that filters only on suburb. This is a frequent source of confusion for newcomers who assume any column appearing in the index is freely searchable.

Beyond the standard B-tree, most engines support specialised index types. A covering index includes every column the query needs, so the engine can answer the request straight from the index without visiting the table at all. Partial or filtered indexes restrict the index to a subset of rows, which is handy when a small slice of the table dominates the queries, such as the open orders in an e-commerce order book. For full-text search or geographic queries, you may want a GIN, GiST, or R-tree variant instead.

The temptation, when learning about indexes, is to add them everywhere. Resist it. Every index speeds up reads but slows down writes, because each INSERT, UPDATE, and DELETE has to maintain every index on the table. In a system that processes millions of events per hour, an overzealous indexing strategy can quietly double the write load on the primary.

Reading EXPLAIN Plans Without Fear

The EXPLAIN command returns the execution plan the optimiser intends to follow, expressed as a tree of operators. Each node describes a step: a sequential scan, an index scan, a hash join, a nested loop, a sort, an aggregation. The trick is to read the tree from the inside out, starting with the leaves, because those are the operations that actually touch the data.

Pay close attention to the estimated row counts and the actual row counts when your engine supports them. A massive gap between the two means the optimiser is making decisions on stale information. In a Commonwealth Bank transaction ledger, for example, the optimiser might think a date-range query will return a handful of rows, then discover at runtime that it returns millions, leading to a catastrophic plan choice and a stalled dashboard.

The cost column is another guide, but treat it as a relative signal, not an absolute one. A cost of 100 next to a cost of 1000 means the first plan is cheaper, but the numbers are not seconds or milliseconds. The only way to know how long a plan truly takes is to run it and measure, which is why most teams wrap their queries in a timing harness during development.

Query Patterns That Quietly Drain Performance

Some bad habits are easy to spot, others hide in plain sight. Applying a function to a filtered column, such as WHERE UPPER(customer_name) = 'ACME', defeats any standard index on customer_name because the engine has to compute the function for every row. Wrapping a column in a cast or arithmetic expression has the same effect. The fix is usually to store the canonical form separately or to use a functional index if the database supports one.

SELECT * is another common offender. It forces the engine to fetch every column, breaking covering-index optimisations and bloating network traffic. On a slow NBN link between a regional branch and a Sydney data centre, the extra payload shows up as visible latency. Listing only the columns you need keeps the plan tight.

Leading wildcards in LIKE patterns, such as WHERE description LIKE '%organic%', also disable ordinary B-tree indexes. So do negative predicates like NOT IN and != when they appear in critical paths. None of these are inherently wrong, but they deserve a deliberate EXPLAIN to confirm the engine is not silently falling back to a full scan.

A Repeatable Tuning Workflow

Optimisation is not a one-off task. The teams that keep their databases healthy tend to follow the same loop: capture a representative workload, identify the slowest queries, run EXPLAIN on each one, test a fix, measure again, and ship the change. Most cloud-managed databases offer a slow query log or a query performance insight view that does the first step for you.

Once you have a slow query, the discipline is to change only one thing at a time. Add an index, run the query, compare the plan and the runtime, then decide. If the new index does not move the needle, drop it before it starts costing you on writes. Some Australian engineering teams keep a shared runbook in Confluence or Notion for exactly this purpose, so the next person to hit the same query does not start from zero.

When the optimiser still refuses to cooperate, look at the query shape itself. A correlated subquery that runs once per outer row can often be rewritten as a join with a pre-aggregated CTE. A pagination query that uses OFFSET 100000 LIMIT 20 scans through the first hundred thousand rows every time, which is why cursor-based pagination tends to scale where OFFSET cannot.

Beyond Indexes: Statistics, Schema, and Hardware

Even a perfect index cannot help if the optimiser thinks your table holds a thousand rows when it actually holds fifty million. Most engines ship an ANALYZE or UPDATE STATISTICS command that refreshes the histograms and row counts the planner relies on. Schedule it to run after large data loads, such as the nightly batch that reconciles the Australian Bureau of Statistics survey responses, and your morning dashboards will stop being mysteriously slow.

Schema design matters too. Over-normalised tables force every query to join across many small tables, while wildly denormalised tables make indexes large and slow. Somewhere in the middle is usually right. And when none of the above helps, look at the hardware: a query that is genuinely bound by disk seeks will not improve much from a clever index, but will from moving the working set into memory or onto faster storage.

If you find yourself enjoying the combinatorial side of query planning, the Hungarian algorithm walkthrough on hello ML shows how a classic assignment solver weighs options the way an optimiser does, just on a much smaller playing field.

Indexing and execution plans are not glamorous, but they decide whether a database quietly does its job or pages someone at 2am. The effort pays back the first time a Melbourne campaign manager notices a dashboard is fast, rather than wondering why it is broken.

Index hygiene habits worth keeping

• Review indexes after any schema migration, dropping anything unused by the workload.
• Keep composite index columns ordered by selectivity and by how the query filters.
• Rebuild or reorganise fragmented indexes during scheduled maintenance windows.
• Document each new index alongside the query it was created to serve.

Warning signs in an EXPLAIN plan

• Sequential scans on tables you expected to be indexed.
• Nested loop joins over very large inputs with no underlying index.
• Sort operations that spill to disk because work memory ran out.
• Huge gaps between estimated and actual row counts on the same operator.