Optimizing SQL for Large Datasets

You're a Forward-Deployed Engineer at Valon, a mortgage-servicing platform, embedded with an operations team that needs a fast list of delinquent loans. They hand you this query, which currently takes minutes to return on a servicing book of a few million loans.

CREATE TABLE borrowers (
    borrower_id BIGINT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL
);

CREATE TABLE loans (
    loan_id BIGINT PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    principal_balance NUMERIC(12,2) NOT NULL,
    origination_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL
);
-- Index: idx_loans_status(status)

CREATE TABLE payments (
    payment_id BIGINT PRIMARY KEY,
    loan_id BIGINT NOT NULL,
    payment_date DATE NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    payment_type VARCHAR(20) NOT NULL
);
-- Index: idx_payments_loan_date(loan_id, payment_date)

SELECT DISTINCT
    l.loan_id,
    b.full_name,
    l.principal_balance,
    (SELECT MAX(p2.payment_date) FROM payments p2 WHERE p2.loan_id = l.loan_id) AS last_payment_date
FROM loans l
JOIN borrowers b ON l.borrower_id = b.borrower_id
JOIN payments p ON p.loan_id = l.loan_id
WHERE UPPER(l.status) = 'DELINQUENT'
  AND b.full_name LIKE '%smith%'
  AND YEAR(p.payment_date) = 2024;

Walk through why this is slow, and rewrite it so it's fast and still returns the right one-row-per-loan result.