Why Does a Short Query Repeat So Much Work?

The report needs visit counts and minutes for every asset. A convenient query asks two correlated subqueries for every asset. With a small register that looks harmless; a growing visit history can make the repeated work expensive.

We will group the visit data once at the required grain and join it to the register. The larger lesson is how to inspect a plan and test a rewrite without confusing a shorter SQL statement with a faster or correct one.

Check answer identities before counting repeated scan visits, then choose the smaller measured count.

group

A plan describes operations such as scanning a table, searching an index, joining inputs and sorting results. Follow where rows come from and how often an operation is repeated. A nested operation can be cheap once and expensive when executed for every outer row.

SQLite’s EXPLAIN QUERY PLAN gives a high-level tree. Its SCAN and SEARCH descriptions help distinguish broad traversal from a constrained lookup, but interpretation depends on the chosen access path. An index scan can still visit many entries.

The query text describes the result; the plan describes a route the engine currently chooses.

Our report includes assets with zero visits. An inner join would discard them and may look faster because it returns less information. A rewritten aggregate must preserve both the row grain and the intended treatment of missing matches.

Write result tests for empty visit groups, additional assets and repeated visits. Check tie ordering only where the report requires it. Comparing a fast wrong query with a correct one is not a useful optimisation result.

The workshop refuses a measured plan whose answer IDs differ before comparing its scan counts.

Group visits by asset_id so the derived table has one row per asset. Join that table to the asset register with LEFT JOIN, then replace missing count and minute totals with zero under this report’s policy.

A CTE names the intermediate result so its grain is easy to see. It does not universally force materialisation or guarantee that the engine scans the source only once. Optimisers can inline or transform it. Inspect the actual chosen plan rather than inferring execution from the presence of WITH.

An appropriate index can also help a correlated query. Evaluate alternatives instead of declaring one syntactic form universally best.

CREATE TABLE assets(id TEXT PRIMARY KEY NOT NULL); INSERT INTO assets VALUES('P7'),('P8'),('P9');
CREATE TABLE visits(id INTEGER PRIMARY KEY,asset_id TEXT NOT NULL,minutes INTEGER NOT NULL); INSERT INTO visits VALUES(1,'P7',20),(2,'P7',30),(3,'P8',10);

SELECT asset_id,COUNT(*) AS visits,SUM(minutes) AS minutes
FROM visits GROUP BY asset_id ORDER BY asset_id;

An optimiser estimates row counts and costs before running the query. Statistics, parameter values and correlated columns can make those estimates inaccurate. A large estimate error near the beginning of a plan can lead to poor downstream choices.

PostgreSQL EXPLAIN ANALYZE actually executes the statement and reports observations such as actual rows and loops. For a write statement, it can therefore perform writes. Use a deliberate transaction and rollback where appropriate, remembering external effects may not be reversible. SQLite EXPLAIN QUERY PLAN does not provide the same runtime analysis.

Do not copy an ANALYZE command into a production write path as if it were a harmless formatter.

Timing one warm-cache query on a tiny database tells us little about a larger workload. Compare realistic distributions, common and rare parameter values, repeated runs, and the resource pressure the service normally sees. Preserve result checks alongside timing.

Refresh or inspect database statistics when estimates look implausible. Also check temporary sorting, spilling and repeated lookups. The workshop’s row-visits metric isolates one concept; it is not an optimiser cost model or a substitute for elapsed-time and resource measurements.

A smaller measured row count is evidence about that run, not a universal speed guarantee.

Record the query version, schema/index changes, data scale and measurements used to justify an optimisation. Verify that write costs and other important queries did not regress. A new index or materialised result can move work elsewhere rather than remove it.

Plans can change after a database upgrade or a data-distribution shift. Monitor the user-visible outcome and investigate plan changes when they matter. Tests should protect query semantics and deliberate schema contracts rather than exact unstable plan text.

The challenge accepts any equivalent report query. To inspect your route, prefix the final SELECT (including its WITH clause, if present) with EXPLAIN QUERY PLAN and choose Run code. Remove that prefix before Run tests so the challenge receives report rows rather than plan rows.