From 99dd36f7f6b22014ed671e661e6553ba7c41810b Mon Sep 17 00:00:00 2001 From: Marco Boss Date: Sat, 26 Feb 2022 17:37:52 +0100 Subject: add index on wire_targets - better performance on local db - many queries use wire_target_serial_id as join condition -> discussion use this as shard key? --- src/exchangedb/exchange-0001.sql | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'src/exchangedb') diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index e8707fbff..184d62a7c 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -88,6 +88,14 @@ CREATE TABLE IF NOT EXISTS wire_targets_default PARTITION OF wire_targets FOR VALUES WITH (MODULUS 1, REMAINDER 0); +-- FIXME partition by serial_id rather than h_payto, +-- it is used more in join conditions - crucial for sharding to select this. +-- Author: (Boss Marco) +CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index + ON wire_targets + (wire_target_serial_id + ); + CREATE TABLE IF NOT EXISTS reserves (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32) -- cgit v1.2.3