Speed Up the Slow Queries
Three queries behind the shop's busiest pages read whole tables. Add the right PostgreSQL indexes, and fix the query an index cannot help, so each one reads a few pages and returns the same rows.
The situation
The shop's database passed 400,000 orders this year, and three pages got slow at the same time. The order history of a business customer times out, logging in takes a second, and the finance dashboard spins. Each of the three queries behind them reads a whole table on every call.
The database team left a note on the ticket: add indexes, but not one on every column. And one of these queries cannot use an index the way it is written.
The test data is like production's: 50,000 customers and about 408,000 orders placed during 2026, a few large business customers with thousands of orders each, and email addresses typed in any mix of upper and lower case. Most orders are stored in the order they were placed, but some came in later from an import, so a higher ID is not always a newer order.
Your task
- The order history query (the 20 newest orders of one customer) reads at most 60 pages.
- The email lookup (find a customer by email, in any case) reads at most 20 pages.
- The revenue query (paid orders and revenue for one day, in UTC) reads at most 200 pages.
- Every query returns the same columns and rows, in the same order, as it does today. You may rewrite the queries, but each stays one SELECT with the same parameter.
- indexes.sql runs on the empty tables and may only hold CREATE INDEX and DROP INDEX statements: no new tables, columns, views, functions, triggers or rows.
What the grader checks
| Check | Passes when |
|---|---|
| The migration runs | indexes.sql holds only CREATE INDEX and DROP INDEX statements, runs without errors as the app's database user, on the empty tables, within 2 minutes, and leaves only indexes behind: the tables, columns and constraints stay as they are, the tables stay empty, and there are no new functions, views, triggers or other objects. |
| Order history | For random customers (large, regular, small, and one with two orders at the same time among its newest), queries/orders_for_customer.sql returns the same rows in the same order as the original query, and EXPLAIN ANALYZE shows it reading at most 60 pages, with no sequential scan. |
| Find a customer by email | For 5 random email addresses in random upper and lower case, one of them shared by two customers and one unknown, queries/find_customer.sql returns the same rows as the original query and reads at most 20 pages, with no sequential scan. |
| Revenue for a day | For 4 random days, one of them with no orders, queries/revenue_for_day.sql returns the same totals as the original query and reads at most 200 pages, with no sequential scan. |
When a check fails, the grader's comment says what it saw, not how to fix it.
Files you may change
challenges/speed-up-the-queries/indexes.sqlchallenges/speed-up-the-queries/queries/orders_for_customer.sqlchallenges/speed-up-the-queries/queries/find_customer.sqlchallenges/speed-up-the-queries/queries/revenue_for_day.sql
A pull request that changes any other file, including the tests or the workflows, is not graded.
Hints
Read the plan
EXPLAIN (ANALYZE, BUFFERS) in front of a query shows each step of the plan, how many rows it produced, and how many pages it touched.
Let the index do the sorting
An index on customer_id alone finds a customer's orders, but they still have to be sorted, and a large customer has thousands. LIMIT 20 can stop after 20 rows only when the index hands out the rows in the order the query wants.
An index on a column does not help a function of it
The index on email is no use for lower(email) = ... PostgreSQL can index an expression too.
created_at::date
PostgreSQL cannot use the index on created_at for created_at::date = $1, and it cannot index created_at::date either, because the result depends on the time zone setting. Ask for a range of created_at instead.
Test it locally
From the challenge folder, with Docker and psql. Then put EXPLAIN (ANALYZE, BUFFERS) in front of a query, with a real value in place of $1:
docker run -d --name shop-db -e POSTGRES_PASSWORD=shop -p 5432:5432 postgres:18.6
export PGHOST=localhost PGUSER=postgres PGPASSWORD=shop PGTZ=UTC
psql -c 'CREATE DATABASE shop'
psql -d shop -v ON_ERROR_STOP=1 -f schema.sql -f indexes.sql -f seed.sql -c 'ANALYZE'
psql -d shopSubmit
- Fork The-DevOps-Daily/challenges and create a branch.
- Commit your changes to the files listed above, and nothing else.
- Open a pull request against main in The-DevOps-Daily/challenges.
- Read the grader's comment. If a check fails, push another commit to the same branch and it grades again.
- When every check passes, the PR gets the passed label and is closed. It is never merged, and your pass is usually counted on this page within a few minutes.
The workflow takes the tests and the grader from the repository's main branch and copies only the files above out of your PR. Your code runs in a container or a throwaway kind cluster, and some inputs are random at grading time. A second workflow checks the result against your PR's real commit before it comments.
Learn the basics first
We earn commissions when you shop through the links below.
More challenges
Docker · Starter
Shrink the Image
A tiny Flask app ships in a container image of more than 1 GB that runs as root. Rewrite the Dockerfile so the image is under 200 MB, runs as a normal user and still works.
About 30 min
Bash · Starter
Script It: Summarize an Access Log
Finish a half-written Bash script that turns an nginx access log into a short report: requests, status classes, top clients and top paths. ShellCheck has to be clean and the edge cases right.
About 45 min
Kubernetes · Intermediate
Fix the Deployment
An inventory API never comes up on Kubernetes. Find and fix the bugs in its Deployment, Service and ConfigMap until the rollout finishes and the Service answers.
About 45 min
Prometheus · Intermediate
Write the Alerts
The checkout service has metrics but no useful alerts. Write four Prometheus alert rules that page for real outages and stay quiet for blips, quiet nights and other services.
About 45 min
Terraform · Intermediate
Fix the Network Module
A Terraform module for a VPC and its subnets broke in a refactor: it does not validate, its subnets overlap and it accepts any input. Fix it until its tests and random plans pass, with no AWS account.
About 60 min