CodeRang
SQL topics

SQL Query Optimization with EXPLAIN ANALYZE and Indexes

SQL query optimization starts with understanding what the database actually does to produce a result. This example uses EXPLAIN ANALYZE concepts, index-friendly predicates, and a composite index to examine SQL query performance.

Updated · SQL Arena team

SQL Performance courseAll problems

What you will practice

  • Read key EXPLAIN ANALYZE nodes
  • Compare sequential and index scans
  • Choose composite index column order
  • Write sargable predicates
  • Measure the actual work performed by a query

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find one account's May transactions without wrapping the date

Return May 2026 transactions for account_id = 1. Output account_name, created_at, and amount. Keep the date predicate compatible with the composite (account_id, created_at) index by avoiding a function around created_at.

Query 1PostgreSQLv16
SELECT a.account_name, t.created_at, t.amount
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.account_id = 1
  AND t.created_at >= TIMESTAMP '2026-05-01'
  AND t.created_at < TIMESTAMP '2026-06-01'
ORDER BY t.created_at, t.id;
Submit
Resultaccountstransactions
Answer accepted

How SQL Query Optimization and EXPLAIN ANALYZE Work

The index starts with account_id and then created_at. The query uses equality on the first column and a range on the second. That is a natural access pattern for this index: narrow the search to one account first, then scan the relevant time range inside that account.

The month is expressed as a half-open interval: created_at >= '2026-05-01' and created_at < '2026-06-01'. The indexed column stays untouched. A condition such as DATE(created_at) = ... applies a function to every value and may prevent a normal index on created_at from being used as a straightforward range condition.

EXPLAIN ANALYZE is still the deciding tool. PostgreSQL may choose a Seq Scan for a tiny table because reading every row is cheaper than using the index. The goal is not to force an Index Scan into every plan. The goal is to understand rows, loops, scan types, timing, and the amount of work behind the result.

EXPLAIN displays the execution plan selected by the query optimizer, while EXPLAIN ANALYZE actually runs the statement and adds runtime statistics. When analyzing a plan, do not look only for an Index Scan. Compare estimated and actual row counts, inspect filters and joins, and check how much data each operation processes. Composite indexes also depend on column order. Predicates are generally easier to use with an index when the indexed column remains directly searchable rather than being unnecessarily wrapped in a function.

SQLPostgreSQL v16
SELECT a.account_name, t.created_at, t.amount
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.account_id = 1
  AND t.created_at >= TIMESTAMP '2026-05-01'
  AND t.created_at < TIMESTAMP '2026-06-01'
ORDER BY t.created_at, t.id;
Query result
account_namecreated_atamount
Northwind2026-05-01 08:30:001400
Northwind2026-05-03 11:15:00900

Common SQL Query Optimization Mistakes

01

Wrapping an indexed column in a function

Predicates such as DATE(created_at) = ... are convenient but change the expression PostgreSQL must evaluate. For a normal timestamp index, a direct lower and upper bound is often easier to use as an index range.

02

Choosing composite index order randomly

(account_id, created_at) and (created_at, account_id) support different access patterns. Column order should follow the queries you need to serve, including equality predicates, ranges, and ordering requirements.

03

Treating every Seq Scan as a bug

Sequential scans are often correct for small tables or queries that return a large portion of a table. An index is not automatically cheaper. EXPLAIN ANALYZE provides the evidence needed before changing the query or schema.

Practice problems on this topic

SQL Performance course

SQL Arena includes optimization exercises where the execution plan is part of the problem rather than an afterthought. Early tasks cover full-table scans, missing foreign-key indexes, recent-order feeds, and payment lookups. Harder problems move into covering indexes, index-only access patterns, and case-insensitive search. The SQL Performance course provides a longer path through the same ideas using data volumes up to 10 million rows, where inefficient access patterns become easier to observe. EXPLAIN is available in the interface, and task solutions are checked against hidden data. Across SQL Arena there are 1,192 tasks, with separate PostgreSQL, MySQL, ClickHouse, and BigQuery sandboxes.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL query optimization?

SQL query optimization is the process of reducing the work required to execute a query while preserving its result. It commonly involves examining execution plans, predicates, joins, indexes, sorting, and the amount of data processed.

What does EXPLAIN ANALYZE do?

EXPLAIN ANALYZE executes the query and reports both the selected execution plan and actual runtime statistics. It helps reveal differences between optimizer estimates and what happened during execution.

How do you optimize SQL query performance?

Start by measuring the current query and inspecting its execution plan. Look for excessive row scans, inefficient joins or filters, unnecessary sorting, and missing or unsuitable indexes before changing the query.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the planned execution strategy and optimizer estimates. EXPLAIN ANALYZE also runs the statement and adds actual row counts and timing information.

How do SQL indexes improve query performance?

Indexes can let the database locate qualifying rows without scanning an entire table. Whether an index is useful depends on the query, index structure, data distribution, and the plan chosen by the optimizer.

See also

Practice SQL Query Optimization

Work through scan types, sargable predicates, and basic indexes first, then move to composite and covering indexes. Continue with the SQL Performance course for a structured path.

Practice SQL optimization

More practice from CodeRang