-- 047_stocklist_premises_stocklist_idx.sql
-- Index stocklists.stocklist_premises by stocklist_id.
--
-- The table carries (uprn, stocklist_id) and (uprn) - both leading on uprn, so "which
-- stocklist holds this premise" is fast and "which premises are in this stocklist" has no
-- index at all. The second is the direction every stocklist screen reads in.
--
-- Found in the plan for the wayleave pending diff, which hash-joins the whole table:
--
--   Seq Scan on stocklist_premises sp (rows=563525) (actual time=0.006..30.434)
--
-- 30ms today and linear in the size of the table, on a query that wants the premises of
-- one attached stocklist. Not wayleave-specific: stocklist_edit reads the same direction.
--
-- A btree on (stocklist_id) rather than (stocklist_id, uprn): the join needs the uprn
-- from the heap anyway, and a second column would double the index for no index-only
-- scan.

DO $$
BEGIN
    IF to_regclass('stocklists.stocklist_premises') IS NULL THEN
        RAISE EXCEPTION '047: stocklists.stocklist_premises not found';
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS stocklist_premises_stocklist_idx
    ON stocklists.stocklist_premises USING btree (stocklist_id);

ANALYZE stocklists.stocklist_premises;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes
                    WHERE schemaname = 'stocklists' AND tablename = 'stocklist_premises'
                      AND indexname = 'stocklist_premises_stocklist_idx') THEN
        RAISE EXCEPTION '047: index not created';
    END IF;
END $$;
