Break your own index six ways

import java.util.*;
import java.util.function.*;
import java.util.stream.*;

/**
 * Warm-up: make an index useless, then make the optimiser refuse a good one.
 *
 * No database. An index is a sorted list, which is enough to show the property
 * that matters — sorting only helps for the value you sorted by.
 */
public class Starter {

    record Row(int id, String name) {}

    public static void main(String[] args) {

        // TODO 1: build 10,000 rows with about ten distinct surnames, then
        // sort them by name. That sorted list is your index.

        // TODO 2: write canSeek(predicate) returning whether a sorted-by-name
        // structure could answer it. Decide for each of these before coding:
        //
        //     name = 'Ada'          name LIKE 'Ada%'      name > 'M'
        //     UPPER(name) = 'ADA'   name LIKE '%son'      name::int = 42
        //
        // Three can seek and three cannot. Say the rule in one sentence.

        // TODO 3: for each predicate, print the plan, how many rows it
        // MATCHES, and how many keys are examined. A seek examines
        // log2(n) + matches; a scan examines n.

        // TODO 4: find the two pairs where the match counts are identical and
        // the key counts are not. Those pairs are the entire lesson — write
        // down what differs.

        // TODO 5: now the other reason. Write the optimiser's arithmetic with
        // PostgreSQL's defaults:
        //
        //     seqScanCost   = pages * 1.0 + rows * 0.01
        //     indexScanCost = log2(rows) * 4.0 + matched * 4.0 + matched * 0.01
        //
        // For a million rows at 100 rows per page, print the cost of both
        // plans at 1, 100, 5,000, 50,000 and 200,000 matches.

        // TODO 6: find the crossover — the match count where the scan becomes
        // cheaper. Express it as a percentage of the table. Predict it first;
        // most people guess far too high.

        // TODO 7: the 4.0 in indexScanCost is random_page_cost. Recompute the
        // crossover for 4.0, 2.0 and 1.1. Then look up what random_page_cost
        // defaults to and what hardware that default assumes.

        // TODO 8: finally, say why a covering index makes TODO 6 stop
        // applying. The answer is about which term of indexScanCost disappears.
    }
}