Insights · Technology careers · 6 minute read

Build a SQL Query Tuning Work Sample That Preserves the Answer

Show a hiring team how you make a query faster while keeping its meaning, and leave a comparison another engineer can repeat.

By Vitaver Staffing · Published

A query tuning work sample should show that you can make a useful answer faster while preserving its meaning. Give a hiring reviewer a small database, a repeatable comparison and a reason to trust the result. A screenshot of a lower execution time leaves too many questions unanswered.

This article applies Signal vs. Noise and individual ownership from The Vitaver Playbook: Operating Doctrine & Craft. Focus on the observations that change your next technical decision, then leave another person able to verify it.

Define the answer before tuning the query

Build a local PostgreSQL lab for a fictional service desk. A supervisor needs the oldest open requests for one service category as of a specified time. Define what counts as open, how ties are ordered, what happens to requests without a category and which time boundary applies. Use synthetic records and publish a deterministic data generator, schema and reset procedure.

Include a small fixture whose expected answer can be inspected manually. Add requests closed exactly at the boundary, several with identical creation times and a category with no open work. State the desired row order. An optimization that omits a tie or changes the boundary has changed the question.

Create a separate, larger dataset for performance work. Record its size and distribution, including a busy category and a rare category. Keep the data seed and database version fixed for the comparison. Do not copy an employer's query plans, schemas or operational data into a public repository.

Read the evidence the plan supplies

Start with the baseline SQL and its plan. PostgreSQL's EXPLAIN describes how the planner expects to execute a statement. Its costs use planner units, while row estimates describe expected output at each node. A cost value should not be presented as elapsed milliseconds. PostgreSQL 18 guidance on using EXPLAIN.

In your isolated lab, use EXPLAIN (ANALYZE, BUFFERS) on the chosen read query to collect execution and buffer evidence. The ANALYZE option actually executes the statement. Use lab data and known read queries, and inspect any functions they call before running them. PostgreSQL 18 EXPLAIN command reference.

Choose one discrepancy to investigate. Does an intermediate step return many more rows than estimated? Does the query discard most rows after reading them? Does a sort consume a large part of the observed work? Explain which observation would support your hypothesis. A sequential scan can be reasonable when much of a table is needed. Make the plan answer your question before trying to force a different plan.

Change one thing and preserve correctness

For the service desk query, consider whether an index matching the filter and ordering could help. State the proposed column order and why it fits the query. Alternatively, investigate whether a rewritten predicate preserves the same time and null behavior. Keep index changes and SQL rewrites as separate experiments so the reviewer can identify what caused a difference.

First run the small fixture. Compare the full rows and their order against the expected answer. Then compare baseline and candidate outputs on the larger dataset. A matching count can conceal different request identifiers. If only the first page is returned, preserve a deterministic tie-breaker and verify that the selected requests are the intended ones.

Keep a failed experiment if it teaches something specific. Perhaps an index helps the rare category but adds little for the busy one. Show that observation and choose based on the intended workload. Dropping an unsuccessful change is evidence of judgment when you can explain why.

Make the timing comparison honest

Record hardware, PostgreSQL version, relevant settings, data generation and statistics preparation. Apply the same preparation to both versions. Run repeated trials under stated conditions and retain the observations, including variation. Alternate the order where practical so the second query does not always benefit from the first one's work.

Describe cache conditions you actually controlled. Restarting a database alone does not establish that the operating system's file cache is empty. A warmed local test can still answer a useful question if labeled accurately. Keep plan collection and ordinary query timing distinguishable, since instrumentation adds work.

State both the returned result and the measured scope. A local read-query improvement does not establish production response time under concurrent traffic. An added index also creates storage and maintenance work. Measure its size and a representative lab write operation if you want to discuss that tradeoff. Otherwise identify write performance as untested. Avoid a percentage improvement claim until you have comparable recorded runs.

Give the reviewer a short route through the work

Package setup, reset, correctness checks, baseline and candidate SQL, saved plans and the timing procedure. Explain your own contribution and credit borrowed components or AI assistance. Start the walkthrough with the supervisor's question. Show the expected result, one plan observation that led to a change, and the evidence that justified keeping or rejecting it.

Ask a peer to rebuild the lab and reproduce the conclusion without coaching. Correct any missing instruction. The receiving person should be able to remove your proposed index or revert your query and repeat the baseline. Keep a short note about what additional evidence a production owner would need before approving a similar change.

Connect the demonstration to the role

A recruiter can accurately report that you supplied a reproducible PostgreSQL investigation with correctness and performance comparisons. Separate that demonstration from production database administration, incident response or another engine you have only studied. Confirm the actual site, support hours and responsibilities before accepting an assignment.

A buyer adapting this exercise should provide the same fictional dataset, permitted tools, effort limit and scoring criteria to each applicant for the role. The technical evaluator can assess whether the person preserves semantics, reads evidence and explains the tradeoff. The hiring manager then decides which remaining duties need separate evidence. A focused sample earns its place by making that decision easier to inspect.

Questions for discussion

  1. Which changed row would invalidate an otherwise faster query?
  2. What would you need to test before taking a local tuning result into production?
← All Insights