max / makenotwork

859 B · 16 lines History Blame Raw

1	-- Ordered dashboard-transaction indexes (ultra-fuzz Run 11 Perf MOD tail).
2	--
3	-- The buyer/seller dashboard tabs run
4	-- SELECT ... FROM transactions WHERE {seller,buyer}_id = $1 ORDER BY created_at DESC LIMIT $2
5	-- The plain single-column indexes serve the equality but force a sort of every
6	-- one of the user's transactions to satisfy the ORDER BY + LIMIT. A composite
7	-- (id, created_at DESC) lets the planner walk the index in order and stop at the
8	-- limit. The composite covers the single-column equality lookups as a prefix, so
9	-- the originals are redundant and dropped.
10
11	DROP INDEX IF EXISTS idx_transactions_seller_id;
12	DROP INDEX IF EXISTS idx_transactions_buyer_id;
13
14	CREATE INDEX idx_transactions_seller_created ON transactions(seller_id, created_at DESC);
15	CREATE INDEX idx_transactions_buyer_created ON transactions(buyer_id, created_at DESC);
16