Indexes, EXPLAIN and measured query reasoning

An index offers an access path; it does not change the logical result contract. The worked query creates a composite index for country, active status and identity, then requests active Ukrainian identities in order. The accepted result remains 1 and 2, exactly as before the index existed.

The companion lab inspects an engine-specific plan separately. SQLite uses EXPLAIN QUERY PLAN, while PostgreSQL supports EXPLAIN with JSON format. Their output shapes differ, so a test should not compare their textual plans as if they were the same language.

The equality predicates use leading index columns and the final identity can support the requested order. That makes the index a plausible candidate, not a guarantee that every engine will choose it on eight rows. PostgreSQL can reasonably prefer a sequential scan for such a tiny table.

Read plan nodes, estimated rows and actual workload context before drawing conclusions. Statistics, distribution, selected columns and competing indexes matter. EXPLAIN without ANALYZE inspects a plan; ANALYZE execution options actually run the statement and therefore need an appropriate bounded test context.

The counterexample broadens the country-status contract with OR. An index cannot make those wrong results correct. Another mistake applies a function to an indexed column and assumes the original index still supports the same search without inspecting a corresponding expression index or plan.

Do not claim a fixed latency improvement from this teaching fixture. Its purpose is result preservation and plan literacy. A production benchmark would need representative cardinality, warm and cold behavior, concurrent load, hardware context and independently recorded timing evidence.

Create the same disposable index and query active US identities. Predict 3 and 4. Inspect the plan using the lab's two engine-specific commands, but do not make the test fail merely because the tiny PostgreSQL fixture chooses a sequential scan.

This task changes the selected value while preserving the access-pattern shape. Compare exact result membership before and after adding the index. The index exists only in each disposable lesson database and is never applied to the production ApplyDjinn database.

Step 1: Create the index only in the disposable lesson database. Step 2: Query active US rows and inspect native plan output.

The solution returns 3 and 4. The verifier checks that both engines produce their native plan evidence and that result semantics remain correct. It records plan structure without asserting that a particular index node must always be chosen on this small dataset.

If you extend the fixture, populate representative distributions and refresh relevant statistics before comparing plans. More indexes consume space and write maintenance; they are not free universal accelerators. Tie every proposed index to a real predicate, ordering and observed workload.

In an interview, connect the query contract to candidate access paths and explain why the optimizer might choose another plan. Distinguish an estimate from an observed timing and a full scan from an automatically bad plan. Small-table scans can be entirely reasonable.

The next lesson changes data inside a transaction and verifies both intermediate and final state. Keep those exercises isolated: inspecting and mutating a learning fixture is different from performing maintenance on a running service.

Optional stretch — after the core task works: Generate a larger representative fixture and record plan changes without assuming every full scan is wrong.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.