From 0ca93841c0f442bd50e9ccd18a0a6d866cb3adf5 Mon Sep 17 00:00:00 2001 From: Matt Corallo Date: Wed, 14 Sep 2022 20:12:17 +0000 Subject: [PATCH] Add one additional index which postgres prefers as the DB fills If postgres decides walking the full `channel_updates_scid_dir_seen` index and removing old `seen` values is slower than just walking the full table (or this new index) it does so. Sadly this causes re-sorting (usually on-disk), but there doesn't seem to be a way to avoid this. --- src/config.rs | 1 + 1 file changed, 1 insertion(+) diff --git a/src/config.rs b/src/config.rs index 3ed4be1..92d33c1 100644 --- a/src/config.rs +++ b/src/config.rs @@ -79,6 +79,7 @@ pub(crate) fn db_channel_update_table_creation_query() -> &'static str { pub(crate) fn db_index_creation_query() -> &'static str { " + CREATE INDEX IF NOT EXISTS channel_updates_seen ON channel_updates(seen, short_channel_id, direction) INCLUDE (id, blob_signed); CREATE INDEX IF NOT EXISTS channel_updates_scid_seen ON channel_updates(short_channel_id, seen) INCLUDE (blob_signed); CREATE INDEX IF NOT EXISTS channel_updates_seen_scid ON channel_updates(seen, short_channel_id); CREATE INDEX IF NOT EXISTS channel_updates_scid_dir_seen ON channel_updates(short_channel_id ASC, direction ASC, seen DESC) INCLUDE (id, blob_signed); -- 2.39.5