Sefism is free for learners. We are here to help you grow, one step at a time.Join for free
Sefism.
Data & Analytics

SQL Query Performance Lab with Execution Plan Reports

A reproducible SQL performance lab: a realistic e-commerce database with millions of rows, a workload of real queries, a harness that captures execution plans and timings, and documented experiments with indexes and rewrites. An intermediate database final year project that doubles as a teaching tool and a CI check for slow queries.

Topics
Difficulty

Intermediate

A small team, or one strong student willing to learn something new.
Effort

1 semester · 1 to 2 students

Deliverables

6 to ship

3 optional extras

Suggested stack

PostgreSQL (or MySQL or SQL Server)SQLPythonDocker ComposeJupyter or a static site for reportsGitHub Actions
A suggestion, not a requirement. Swap anything for what you already know.

What you should ship

  • A one-command Docker Compose setup that creates the database and generates at least five million rows with realistic skew
  • A workload of 10 to 15 realistic queries, each with a written description of the screen or report it serves
  • A benchmark harness that records execution plans, timings and I/O for each query across repeated warm and cold runs
  • At least six documented experiments with before and after plans and numbers, including each index's cost on insert speed
  • A published report explaining which changes helped, which did not, and why, readable by a junior developer
  • A GitHub Action that runs the workload on a pull request and flags any query that got more than a set percentage slower

If you have time left

  • Run the same workload on a second database engine and compare plans and results
  • A concurrency test that shows lock waits or deadlocks and how a design change removes them
  • A small web viewer that highlights the most expensive node in each captured plan

The problem with slow queries

Slow queries are one of the most common production issues in web applications. An order history page that loaded instantly with a hundred test orders takes eight seconds with two million real ones. A dashboard query that scans a whole table every minute quietly doubles the database bill. Yet students rarely get to practise diagnosing this, because their projects never have enough data for anything to be slow. Advice about indexes is easy to find and hard to verify, and many developers add indexes by guesswork, making writes slower without fixing the read.

What already exists and the gap

  • explain.depesz.com and explain.dalibo.com visualise PostgreSQL execution plans that you paste in.
  • pgMustard is a commercial tool that reviews PostgreSQL plans and suggests fixes.
  • SQL Server Management Studio shows graphical actual execution plans and Query Store history.
  • pg_stat_statements tracks which queries take the most total time in a running database.

These tools explain one plan at a time. None of them gives a student, or a small team, a reproducible lab with realistic data, a fixed workload and a history of experiments. And few teams catch a query regression before it ships. The gap is a repeatable lab and a regression check you can run in CI.

What you build

  • A data generator for a realistic schema (customers, products, orders, order items, payments) with at least five million rows and realistic skew, such as a few very active customers and a long tail of rare products.
  • A workload of 10 to 15 queries taken from real screens: product search, order history, admin dashboards, top-N reports and paginated lists.
  • A harness that runs each query several times, captures the execution plan (EXPLAIN ANALYZE with buffers, or the actual plan in SQL Server), timings and I/O statistics, and stores results in a table.
  • Experiments: composite and covering indexes, rewriting non-sargable predicates, keyset pagination instead of OFFSET, refreshing statistics, partial indexes and one denormalised summary table.
  • A report with each experiment's before and after plans and numbers, including the write cost of each index.
  • A CI check that fails a pull request when a query gets slower beyond a threshold.

The technically interesting part

The hard part is measuring fairly. Query timings vary with cache state, background processes and data distribution. Your harness must separate cold and warm runs, repeat enough times to report a median and spread, and pin the dataset with a fixed random seed so results are reproducible. Then comes the diagnosis: show your supervisor a plan where the planner's row estimate was badly wrong because of skew, explain why it chose a sequential scan, and prove which change fixed it. That is the skill employers value and few graduates can demonstrate.

How to evaluate it

  • Improvement: for each experiment, median time before and after, with the spread across runs.
  • Trade-off: insert throughput with and without each new index.
  • Reproducibility: another student runs the one command on a different laptop and gets the same conclusions, even if absolute times differ.
  • CI check accuracy: introduce 5 deliberate regressions and 5 harmless changes; report how many the check catches and how many false alarms it raises.
  • Teaching value: 5 classmates read two experiments and answer questions about why the fix worked.

Suggested plan

  1. Weeks 1 to 3: schema, data generator with skew, Docker setup.
  2. Weeks 4 to 5: workload queries and the harness.
  3. Weeks 6 to 10: experiments, one or two per week, written up as you go.
  4. Weeks 11 to 12: CI regression check.
  5. Weeks 13 to 15: report site, teaching test, final write-up.

Path to a product

The lab is a natural teaching tool: database lecturers could use it for lab sessions, and an open source repository with good write-ups attracts contributors and stars. The CI check could become a small GitHub Action that teams adopt, with a hosted dashboard of query performance over time as a paid tier. It is also a strong portfolio piece for backend and database roles.

Scope warning

Do not build a query optimiser, a new database engine or a general monitoring product. Use generated or openly licensed data only. Run everything locally or in containers, report hardware alongside numbers, and compare configurations on the same machine.

Learn what you need on Sefism

Get help with this idea

This project is judged on the quality of its evidence. Tauseef can help you choose the workload, design fair measurements and shape the report so examiners see real diagnosis, not guesswork. If you are new here, create a free account first.

Ideas and guidance, not finished projects

These are project ideas and scoping guidance, published free for students to use as a starting point. I do not build, write, or sell final-year projects, and I do not complete coursework for anyone. Take an idea, make it yours, and build it.

Not sure this one fits you?

I help students pick an idea that matches their skills and their deadline, then scope it down until it is finishable. Guidance only, never done for you.

Discuss this idea
Work with me

Stuck on something specific?

Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.

Book a free sessionSee all services

Follow along

New writing, resources and project ideas land here first.