Query Execution Plans and Bottlenecks

American Express's disputes team pulls a cardholder's disputed high-value transactions for case review. The query below is used to build that view, but on the production card_transactions table, which holds billions of rows, it's showing up as one of the slowest queries in the nightly performance report.

CREATE TABLE card_transactions (
    transaction_id      BIGINT PRIMARY KEY,
    cardholder_id        BIGINT,
    card_number_masked   TEXT,       -- e.g. '**** **** **** 4471'
    merchant_id           INT,
    amount                 NUMERIC(10,2),
    txn_time                TIMESTAMP,
    status                   TEXT
);
CREATE TABLE disputes (
    dispute_id      BIGINT PRIMARY KEY,
    transaction_id   BIGINT REFERENCES card_transactions(transaction_id),
    filed_at          TIMESTAMP,
    reason             TEXT
);
CREATE INDEX idx_txn_cardholder ON card_transactions(cardholder_id);

SELECT t.transaction_id, t.amount, t.txn_time, t.merchant_id
FROM card_transactions t
WHERE t.cardholder_id = 88213471
  AND EXTRACT(YEAR FROM t.txn_time) = 2024
  AND t.amount > 500
  AND t.card_number_masked LIKE '%4471'
  AND (SELECT COUNT(*) FROM disputes d WHERE d.transaction_id = t.transaction_id) > 0
ORDER BY t.amount DESC
OFFSET 5000 LIMIT 50;

Explain the execution plan of this query and identify potential bottlenecks.

Output

1. Explain the likely PostgreSQL execution plan at a high level, including how each filter affects row elimination.
2. Identify the main performance bottlenecks in the query.
3. Mention any indexing or query-shape issues that make the plan expensive on a billion-row table.
4. Keep the answer focused on this exact query and its runtime behavior.