SQL Optimization for 100M Rows

BitGo custodies crypto wallets for institutional clients. A data engineer is asked to optimize the query below, used by the compliance team to look up outbound or large blockchain transactions for a customer's BTC wallets on a specific day. The blockchain_transactions table holds over 100 million rows and this query is timing out in production.

CREATE TABLE wallets (
    wallet_id     BIGINT PRIMARY KEY,
    customer_id     BIGINT,
    currency           TEXT
);

CREATE TABLE addresses (
    address_id    BIGINT PRIMARY KEY,
    wallet_id       BIGINT REFERENCES wallets(wallet_id),
    address_hash      TEXT
);

CREATE TABLE blockchain_transactions (
    tx_id          BIGINT PRIMARY KEY,
    address_id       BIGINT REFERENCES addresses(address_id),
    tx_hash            TEXT,
    amount               NUMERIC(20,8),
    confirmed_at           TIMESTAMP,
    direction                TEXT     -- 'in' or 'out'
);
CREATE INDEX idx_tx_confirmed ON blockchain_transactions(confirmed_at);

SELECT
    w.wallet_id,
    w.customer_id,
    bt.tx_hash,
    bt.amount
FROM wallets w
JOIN addresses a ON a.wallet_id = w.wallet_id
JOIN blockchain_transactions bt ON bt.address_id = a.address_id
WHERE (LOWER(bt.direction) = 'out' OR bt.amount > 5)
  AND bt.tx_hash LIKE '%a1b2c3'
  AND DATE(bt.confirmed_at) = '2024-09-01'
  AND w.wallet_id IN (SELECT wallet_id FROM wallets WHERE currency = 'BTC')
ORDER BY bt.confirmed_at DESC;

Optimize this SQL query for a table with 100 million rows.