-- Two indexes on `purchases`, both for queries that already exist.
--
-- Found by asking the database which foreign keys had no index behind them and
-- then checking what actually queries the table, rather than by adding indexes
-- on principle: every one of them costs a write on the path that records a
-- sale.
--
-- 1. The conversion report filters `client_id` + `occurred_at`, with the site
--    optional - an owner comparing shops is the whole reason that report
--    exists, and that is precisely the case with no site to narrow it. The
--    existing purchases_site_time_idx cannot serve it. Today the table has a
--    handful of rows and a sequential scan is free; purchases is the table
--    that grows with a shop's trade, so this is the one that stops being free.
--
-- 2. purchases.visit_id is a foreign key with nothing behind it. Every delete
--    of a visit has to prove no purchase references it, which without an index
--    is a full scan per row - and erasing a customer deletes their visits.

BEGIN;

CREATE INDEX IF NOT EXISTS purchases_client_time_idx
    ON purchases (client_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS purchases_visit_idx
    ON purchases (visit_id);

COMMIT;