Eight predicates, and why each plan was chosen

import java.util.*;

/**
 * Challenge: eight queries, eight plans. Say why each plan was chosen.
 *
 * Three causes are in play — the predicate cannot use an index, the optimiser
 * costed a scan cheaper, or the statistics are stale — and they need three
 * different fixes. Two of the eight have no index problem at all.
 */
public class Starter {

    // Schema:
    //   orders(id bigint pk, status varchar(20), created_at timestamptz,
    //          customer_id bigint, total numeric)
    //   Indexes: (status), (created_at), (customer_id), (lower(reference))
    //   10 million rows. 60% of them have status = 'CLOSED'.

    // ── 1 ── WHERE created_at::date = CURRENT_DATE
    //         Plan: Seq Scan, Filter: ((created_at)::date = CURRENT_DATE)

    // ── 2 ── WHERE status = 'CLOSED'
    //         Plan: Seq Scan, rows=6000000, actual rows=6000000

    // ── 3 ── WHERE status = 'REFUNDED'
    //         Plan: Seq Scan, rows=2000000, actual rows=1200

    // ── 4 ── WHERE customer_id = 90210
    //         Plan: Index Scan, Index Cond: (customer_id = 90210)

    // ── 5 ── WHERE status = 1
    //         Plan: Seq Scan, Filter: ((status)::integer = 1)

    // ── 6 ── WHERE reference ILIKE 'ord-99%'
    //         Plan: Seq Scan. There is an index on lower(reference).

    // ── 7 ── WHERE customer_id = 90210 OR status = 'OPEN'
    //         Plan: Seq Scan

    // ── 8 ── WHERE created_at > now() - interval '1 hour'
    //         Plan: Index Scan, then 40 seconds spent in a Sort node

    public static void main(String[] args) {

        // TODO 1: put each of the eight into one bucket — cannot use an index,
        // correctly costed scan, or stale statistics. State the evidence from
        // the plan, not a guess.

        // TODO 2: two plans say "Filter:" on an indexed column. Explain what
        // that word means about where the predicate was applied, and why it
        // is conclusive.

        // TODO 3: number 3 has an estimate off by three orders of magnitude.
        // Say what produces that, what to run, and why adding an index would
        // not have helped.

        // TODO 4: number 5's SQL looks fine. Explain where the cast comes
        // from, why it lands on the column rather than the literal, and the
        // one-character fix.

        // TODO 5: number 6 has an expression index and does not use it.
        // Explain the exact-match requirement, then give two fixes and say
        // which survives a new call site being written by someone else.

        // TODO 6: number 7 cannot use either index. Say why one index cannot
        // serve an OR across two columns, and rewrite it so both can be used.

        // TODO 7: number 8 uses the index and is still slow. The time is in a
        // Sort. Say what would remove it — the answer is about the index
        // definition, not the query.

        // TODO 8: number 2 is a correctly-costed scan over 60% of the table.
        // Give the only two things that would make it faster, and say which
        // one makes the selectivity crossover irrelevant.
    }
}