← Back to the chapter · Next detail →

Technical detail · Time-Series Query Optimization

Why a small result can still require a large scan

The query scan-work tour uses a building temperature dashboard to separate stored rows, scan work, and returned rows. A query asks storage for data. Setting a start time, an end time, and a building defines the question. Grouping puts measurements into time buckets; aggregation summarizes each group, for example by calculating its average. Those choices must preserve the intended temperature trend.

The teaching model starts with 50,000,000 stored rows, a 24-hour window, and a result budget of 5,000 rows. Keep summaries and indexes disabled for the comparison, so changes in estimated scan work come from time pruning alone. Time pruning means skipping storage outside the requested range.

With pruning disabled, the model multiplies 50,000,000 by 1. Its minimum scan estimate is one row, so the larger value remains 50,000,000. The request contains a time range, but old storage still enters the scan.

For a 24-hour window with pruning enabled, the model assigns a fraction of 0.033. Multiplying 50,000,000 by 0.033 gives 1,650,000 rows. That exceeds the one-row floor, so it becomes the estimated scan work. The requested window remains 24 hours and the output budget remains 5,000 rows. The question stays fixed while the model changes which storage is read.

A 5,000-row output budget therefore does not prove that the database did little work: the first version still scans 50,000,000 rows. Judge both the work needed to produce the answer and the size of the answer. Make storage read less while keeping the question the same.

The separate tour has eight beats, provisional storyboard timing, captions, a transcript, and reduced-motion stills. Narration audio is pending. Its timing is not sensor, query, or protocol time; its bars are not proportional, and it executes no database query. Read the numeric scan labels as teaching estimates, then use a captured plan for actual workload evidence.

← Back to the chapter · Next detail →