Query-tuning exercises

Twelve slow queries from a real-looking shop. Fix each one; the database checks your plan.

Draft. This page is new and has not yet been reviewed by a second person. If something looks wrong, report it.

Before thisReading EXPLAIN
On this page
  1. Getting started
  2. Indexes that fit the query
  3. Rewriting the query
  4. When the plan itself goes wrong

Twelve slow queries from an online shop, each with a real cause and a real fix. For each one, read the scenario, explain the query, decide what is wrong, and change the schema or the query. The database judges your answer by the plan it actually chooses, not by matching your text, so any correct fix passes.

How each check works:

  • Your changes (for example a CREATE INDEX) and your query run inside a transaction that is rolled back afterwards, so every attempt starts from the same data.
  • Checks look at the plan: which kinds of step it uses, how many pages it reads, how many rows it reads and throws away. Never at timings, which depend on your machine.
  • Your query must return exactly the same rows as the original. Making a query fast by making it wrong does not count.
  • Hints go from a nudge to the answer. Your attempts and solved exercises are saved in this browser and shown on your progress page.

If you are new to plans, read Reading EXPLAIN first, or warm up in the playground.

Getting started#

Exercise

One customer's orders

The "Your orders" page lists one customer's orders, newest first. Every visit reads the whole orders table, and the page gets slower as the shop grows.

Your task. Make the query read only the rows it needs, without changing what it returns.

select id, status, created_at, total
from orders
where user_id = 42
order by created_at desc

This exercise runs a real database in your browser and needs JavaScript.

Exercise

Log in with any capitalisation

Users type their email address however they like, so the login query compares lower-cased values. There is already a unique index on email, yet every login reads the whole users table.

Your task. Make the login lookup use an index, without changing the query.

select id from users where lower(email) = 'user123@example.com'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

Who bought this product?

The product page lists the orders that included it. order_items has a foreign key to products, so it seems as if it ought to be indexed, but the query reads well over a thousand pages.

Your task. Cut the pages read to a few hundred.

select o.id, o.created_at
from order_items oi
join orders o on o.id = oi.order_id
where oi.product_id = 77

This exercise runs a real database in your browser and needs JavaScript.

Indexes that fit the query#

Exercise

Sign-ups on one day

A report counts the users who signed up on a given day. Someone added an index on created_at, but the report is still slow: it reads the whole index and throws almost everything away.

Your task. Make the count read only the entries for that day.

select count(*) from users where created_at::date = '2023-06-15'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

The oldest pending orders

The warehouse screen shows the 50 oldest orders that are still pending, so staff can deal with them first. Only about 4% of orders are pending, yet the screen reads the whole table and sorts what it finds.

Your task. Let the database find the oldest pending orders already in order, without sorting and without reading the whole table.

select id, user_id, created_at
from orders
where status = 'pending'
order by created_at
limit 50

This exercise runs a real database in your browser and needs JavaScript.

Exercise

A customer's latest orders, from the index alone

The account page shows a customer's 20 most recent orders: date and total only. It is called on every page view, so it should touch as little as possible.

Your task. Serve the query from the index alone, with no sort step.

select created_at, total
from orders
where user_id = 7
order by created_at desc
limit 20

This exercise runs a real database in your browser and needs JavaScript.

Exercise

The newest sign-ups in one country

The growth dashboard shows the ten newest users in a chosen country. Many users sign up on the same day, so ties are broken by id.

Your task. Find an index that returns exactly these ten rows without sorting and without reading rows it then discards.

select id, email, created_at
from users
where country = 'IN'
order by created_at desc, id desc
limit 10

This exercise runs a real database in your browser and needs JavaScript.

Rewriting the query#

Exercise

Page 2,501 of the order list

An admin list shows orders newest first, 20 per page, using OFFSET. Page 2,501 is painfully slow: to skip 50,000 rows the database has to produce them first. The previous page ended with the order created at 2023-12-31 14:10:51+00 (created_at is unique in this table).

Your task. Fetch the same 20 rows by starting after the last row you already have, instead of skipping 50,000.

select id, created_at, total
from orders
order by created_at desc
offset 50000
limit 20

This exercise runs a real database in your browser and needs JavaScript.

Exercise

One month of events

The events table is partitioned by month, so a query for one month should only touch that month's partition. This one reads all twelve.

Your task. Make the query read only November's partition.

select count(*) from events where date_trunc('month', at) = '2024-11-01'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

An order count per user

A page shows twenty users with how many orders each has placed. The count is a subquery in the select list, and the page takes seconds.

Your task. Make it at least twenty times cheaper. Either change the schema or the query; both can work.

select u.id, (select count(*) from orders o where o.user_id = u.id) as orders
from users u
where u.id <= 20

This exercise runs a real database in your browser and needs JavaScript.

When the plan itself goes wrong#

Exercise

After a bulk import

A nightly job has just imported 30,000 new pending orders. Straight afterwards, a query that joins new pending orders to their users is dramatically slower than usual. Nothing about the schema has changed.

Your task. Get the planner to choose a sensible plan again, without adding an index or changing the query.

select o.id, u.email
from orders o
join users u on u.id = o.user_id
where o.status = 'pending' and o.created_at >= '2025-01-01'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

A ranking that spills to disk

A report ranks products by how many different orders included them. The plan shows temporary files being written: part of the work did not fit in memory.

Your task. Let the work run in memory, for this report only, without changing the query.

select product_id, count(distinct order_id) as orders
from order_items
group by product_id
order by orders desc, product_id
limit 10

This exercise runs a real database in your browser and needs JavaScript.

Esc
↑ ↓ to move↵ to open