Key takeaways
- Measure the query before editing it and keep the same data shape for the comparison.
- Read the execution plan and the row estimates together, because either one can mislead alone.
- Check the write cost of a new index, including the tables that will pay for it.
- Test with a data volume close to production rather than a small fixture.
- Record the old and new timings, the plan change, and the command that removes the change.
Start from the query that changed
Begin with the exact statement and the code path that calls it. Copy the query from the repository rather than from a log line that may have been truncated. Record the endpoint, job, or report that depends on it so the reviewer can see the user-facing consequence. Add the date of the last change to that code path, because a recent edit is a useful starting hypothesis.
A slow query is sometimes a symptom of a missing index and sometimes a symptom of a caller running the statement far more often than intended. Check the call count before changing the database. A query that runs once per request may deserve a rewrite even when its individual timing looks acceptable. The call count belongs beside the elapsed time in the review record.
Capture a baseline before edits
Run the query on a representative environment and record the elapsed time, the rows returned, and the time of the run. Note the database version, the configuration, and the current load. A baseline taken on an idle machine will not predict behavior during a busy reporting window. Include the cache state if the query depends on a warm buffer pool.
Save the plan for the baseline as well as the timing. The plan shows which access path the database chose and where the time went. Without it, a later improvement is hard to attribute and a later regression is hard to explain. Keep the raw plan output rather than a screenshot so the next reviewer can compare the steps directly.
Read the plan instead of guessing
Look at the scan type, the join order, and the estimated versus actual row counts. A large gap between estimate and reality usually points to stale statistics or a predicate the planner cannot model well. Fix the estimate first when the plan is choosing a path for the wrong reason. Re-run the plan after the statistics refresh before deciding that the query needs a rewrite.
Avoid adding an index because a plan shows a sequential scan. A scan is appropriate when the query needs most of the table or when the table is small. The review should ask what fraction of rows the predicate returns before deciding that a scan is a problem. A small table can be read faster in full than through an index lookup.
Check index and write costs
An index speeds one read and slows every insert, update, and delete that touches the indexed columns. List the write paths that will pay for the new index and estimate how often they run. A reporting query that saves two seconds may not justify a cost paid by a checkout path. Put the write estimate beside the read gain in the review.
Check for overlapping indexes before creating another one. Two indexes with the same leading columns often provide no extra benefit and double the maintenance work. Remove or merge the redundant index in the same change, with the evidence recorded beside the new plan. Confirm that no query depends on the index you plan to remove. The estimate should use the same time window as the read measurement.
Test with production-shaped data
A fixture with a hundred rows will not reveal the plan the production table uses. Use a masked copy or a generated set with a similar distribution and row count. Keep the statistics fresh so the planner sees the same shape the production database sees. A test that uses a different data distribution can approve a plan that fails in production.
Run the query more than once and report the range, not a single fast run. Cache state, background jobs, and concurrent writes all change the result. If the test environment cannot reproduce the volume, say so in the review and treat the timing as directional. Add a post-release check that compares the real query with the baseline. Record the row count and distribution beside the timing so the next reviewer can reproduce the test.
Put the review in the release record
Attach the baseline, the new plan, and the timing range to the change or pull request. The reviewer should not have to search a chat channel for the evidence behind a database change. A short summary at the top can state the old time, the new time, and the main cause. Keep the raw output below it for the person who wants to verify the claim.
Record the rollback command in the same place as the forward change. For an index, that is usually a drop statement with the exact index name. For a query rewrite, it is the previous statement or a revert commit. A rollback that depends on remembering the old text is not ready for a production window. Store the rollback text next to the forward change in the same commit or ticket.
Watch the query after release
Compare the query with the baseline during the same business window after release. A change that helps at midnight and hurts at month end is not finished. Give an owner to the check and a date to close it. If the result holds for the agreed period, remove the temporary monitoring rather than leaving another stale dashboard behind for review.
Watch the write path as well as the read. A new index can slow an import or a batch job that shares the table. Check the job duration and the lock waits in the first week. If the write cost is higher than expected, revisit the index rather than accepting the trade without a decision. Record the write-path result in the same review so the trade is visible.
Questions about assessing Philippine developers
Should the offshore engineer own the index decision?
The engineer can prepare the evidence, the plan comparison, and the write-cost estimate. The buyer-side data owner should approve a new index on a high-write table because the cost falls on paths the pod may not own.
What if the test data cannot match production?
Record the limitation and use the largest safe sample available. Treat the result as directional and add a post-release check that compares the real query with the baseline.
How many queries should one review cover?
One query or one tightly related family is enough. A review that covers ten unrelated statements turns into a general audit and rarely produces a clear decision.
