ziggie
65f6d75511
Remove four indexes from the invoices table that either duplicate existing UNIQUE constraint indexes or are never used in WHERE clauses: - invoices_hash_idx: redundant, UNIQUE constraint on hash already creates an implicit index - invoices_payment_addr_idx: redundant, UNIQUE constraint on payment_addr already creates an implicit index - invoices_preimage_idx: unused, preimage is NULL on all new invoices and is never used as a query filter - invoices_settled_at_idx: unused, settled_at is NULL on all pending invoices and is never used as a query filter (settle_index is used instead) Dropping these reduces B-tree working set size, which improves page cache utilization as the invoice table grows.
17 lines
706 B
SQL
17 lines
706 B
SQL
-- invoices_hash_idx is redundant: The UNIQUE constraint on invoices(hash)
|
|
-- already creates an implicit index.
|
|
DROP INDEX IF EXISTS invoices_hash_idx;
|
|
|
|
-- invoices_payment_addr_idx is redundant: The UNIQUE constraint on
|
|
-- invoices(payment_addr) already creates an implicit index.
|
|
DROP INDEX IF EXISTS invoices_payment_addr_idx;
|
|
|
|
-- invoices_preimage_idx is useless: There are no queries that filter on
|
|
-- preimage so there is no need to index it.
|
|
DROP INDEX IF EXISTS invoices_preimage_idx;
|
|
|
|
-- invoices_settled_at_idx is useless: settled_at is NULL for all pending
|
|
-- invoices and is never used as a filter in any query (settle_index is used
|
|
-- instead).
|
|
DROP INDEX IF EXISTS invoices_settled_at_idx;
|