The Thing The Last Two Lessons Kept Pointing At
Soft delete ended at a partial index. Hard delete ended at an index on every foreign key. Both lessons treated the index as the obvious fix and neither said what one actually is, which matters because three of the answers contradict what most people assume.
Two million rows in a real PostgreSQL, with EXPLAIN run on every query, and none of the three findings appears in the model most people carry.
The model most people carry is that an index is a switch which makes a table faster. Every measurement in this lesson is a case where that model gives the wrong answer.
Four Questions, One Database
The lab starts a PostgreSQL on a scratch directory, loads two million orders, asks four questions and tears the whole thing down.
At the narrow end the plan is a bitmap heap scan through the index. Past the turn the plan is a parallel sequential scan of the whole table, which is the faster choice.
The flat right-hand end is the part worth carrying. Once the plan is a full scan, selecting 40 percent of a table costs almost exactly what selecting all of it costs, because the work is reading the pages, not deciding about the rows.
An index scan gives you an address. Fetching the row is a separate visit to a page, in index order and not in page order, and that second step is the expensive one.
Forcing the index past the crossover makes the query slower. This is the most common misdiagnosis in query tuning: the plan is not wrong, the expectation about what an index does is.