From badfde896217b1f1da3831872acbcda79f6b2cc9 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Fri, 18 Mar 2022 15:40:24 +0100 Subject: add deposits_by_coin table to speed-up certain queries (in theory) --- src/exchangedb/exchange-0001.sql | 126 +++++++++++++++++++++++++++++---------- 1 file changed, 95 insertions(+), 31 deletions(-) (limited to 'src/exchangedb/exchange-0001.sql') diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 8ec025cff..ba40a91ce 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -650,7 +650,7 @@ SELECT add_constraints_to_deposits_partition('default'); COMMENT ON TABLE deposits IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).'; COMMENT ON COLUMN deposits.shard - IS 'Used for load sharding. Should be set based on h_payto and merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.'; + IS 'Used for load sharding. Should be set based on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.'; COMMENT ON COLUMN deposits.known_coin_id IS 'Used for garbage collection'; COMMENT ON COLUMN deposits.wire_target_h_payto @@ -666,11 +666,9 @@ COMMENT ON COLUMN deposits.extension_details_serial_id COMMENT ON COLUMN deposits.tiny IS 'Set to TRUE if we decided that the amount is too small to ever trigger a wire transfer by itself (requires real aggregation)'; --- FIXME: check if we can ALWAYS include the shard in the WHERE clauses, --- thereby resulting in a much better use of the index: we could do (shard,deposit_serial_id)! CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index ON deposits - (deposit_serial_id); + (shard,deposit_serial_id); CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index ON deposits (shard ASC @@ -694,8 +692,68 @@ COMMENT ON INDEX deposits_for_iterate_matching_index IS 'for deposits_iterate_matching'; +CREATE TABLE IF NOT EXISTS deposits_by_coin + (deposit_serial_id BIGINT + ,shard INT8 NOT NULL + ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + ) + PARTITION BY HASH (coin_pub); +COMMENT ON TABLE deposits_by_coin + IS 'Enables fast lookups of deposit by coin_pub, auto-populated via TRIGGER below'; + +CREATE TABLE IF NOT EXISTS deposits_by_coin_default + PARTITION OF deposits_by_coin + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + +CREATE OR REPLACE FUNCTION deposits_by_coin_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO deposits_by_coin + (deposit_serial_id + ,shard + ,coin_pub) + VALUES + (NEW.deposit_serial_id + ,NEW.shard + ,NEW.coin_pub); + RETURN NEW; +END $$; +COMMENT ON FUNCTION deposits_by_coin_insert_trigger() + IS 'Replicate deposit inserts into deposits_by_coin table.'; + +CREATE TRIGGER deposits_on_insert + AFTER INSERT + ON deposits + FOR EACH ROW EXECUTE FUNCTION deposits_by_coin_insert_trigger(); + + +CREATE OR REPLACE FUNCTION deposits_by_coin_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM deposits_by_coin + WHERE coin_pub = OLD.coin_pub + AND shard = OLD.shard + AND deposit_serial_id = OLD.deposit_serial_id; + RETURN OLD; +END $$; +COMMENT ON FUNCTION deposits_by_coin_delete_trigger() + IS 'Replicate deposits deletions into deposits_by_coin table.'; + +CREATE TRIGGER deposit_on_delete + AFTER DELETE + ON deposits + FOR EACH ROW EXECUTE FUNCTION deposits_by_coin_delete_trigger(); + + + CREATE TABLE IF NOT EXISTS refunds (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE + ,shard INT8 NOT NULL -- REFERENCES deposits (shard) ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64) ,rtransaction_id INT8 NOT NULL @@ -703,7 +761,7 @@ CREATE TABLE IF NOT EXISTS refunds ,amount_with_fee_frac INT4 NOT NULL -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! ) - PARTITION BY HASH (deposit_serial_id); + PARTITION BY HASH (shard); COMMENT ON TABLE refunds IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.'; COMMENT ON COLUMN refunds.deposit_serial_id @@ -736,6 +794,10 @@ SELECT add_constraints_to_refunds_partition('default'); CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index ON refunds (refund_serial_id); +CREATE INDEX IF NOT EXISTS refunds_by_deposit_serial_id_index + ON refunds + (shard,deposit_serial_id); + CREATE TABLE IF NOT EXISTS wire_out @@ -959,7 +1021,7 @@ BEGIN RETURN NEW; END $$; COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger() - IS 'Replicate reserve_out inserts into reserve_out_by_reserve_default table.'; + IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.'; CREATE TRIGGER reserves_out_on_insert AFTER INSERT @@ -977,7 +1039,7 @@ BEGIN RETURN OLD; END $$; COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger() - IS 'Replicate reserve_out deletions into reserve_out_by_reserve_default table.'; + IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.'; CREATE TRIGGER reserves_out_on_delete AFTER DELETE @@ -1494,7 +1556,7 @@ DECLARE BEGIN -- Shards: INSERT extension_details (by extension_details_serial_id) -- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING; --- INSERT deposits (by shard + coin_pub, merchant_pub, h_contract_terms), ON CONFLICT DO NOTHING; +-- INSERT deposits (by shard + merchant_pub + h_payto), ON CONFLICT DO NOTHING; -- UPDATE known_coins (by coin_pub) IF NOT NULL in_extension_details @@ -1569,19 +1631,18 @@ THEN -- Idempotency check: see if an identical record exists. -- Note that by checking 'coin_sig', we implicitly check -- identity over everything that the signature covers. - -- We do select over merchant_pub and h_contract_terms + -- We do select over merchant_pub and wire_target_h_payto -- primarily here to maximally use the existing index. SELECT exchange_timestamp INTO out_exchange_timestamp FROM deposits - WHERE - shard=in_shard AND - coin_pub=in_coin_pub AND - merchant_pub=in_merchant_pub AND - h_contract_terms=in_h_contract_terms AND - coin_sig=in_coin_sig; + WHERE shard=in_shard + AND merchant_pub=in_merchant_pub + AND wire_target_h_payto=in_h_payto + AND coin_pub=in_coin_pub + AND coin_sig=in_coin_sig; IF NOT FOUND THEN @@ -1846,20 +1907,21 @@ BEGIN -- UPDATE known_coins (by coin_pub) SELECT - deposit_serial_id - ,amount_with_fee_val - ,amount_with_fee_frac - ,done + dep.deposit_serial_id + ,dep.amount_with_fee_val + ,dep.amount_with_fee_frac + ,dep.done INTO dsi ,deposit_val ,deposit_frac ,out_gone -FROM deposits -WHERE shard=in_deposit_shard - AND coin_pub=in_coin_pub - AND h_contract_terms=in_h_contract_terms - AND merchant_pub=in_merchant_pub; +FROM deposits_by_coin dbc + JOIN deposits dep USING (shard,deposit_serial_id) + WHERE dbc.coin_pub=in_coin_pub + AND dep.shard=in_deposit_shard + AND dep.merchant_pub=in_merchant_pub + AND dep.h_contract_terms=in_h_contract_terms; IF NOT FOUND THEN @@ -1874,6 +1936,7 @@ END IF; INSERT INTO refunds (deposit_serial_id + ,shard ,merchant_sig ,rtransaction_id ,amount_with_fee_val @@ -1881,6 +1944,7 @@ INSERT INTO refunds ) VALUES (dsi + ,in_deposit_shard ,in_merchant_sig ,in_rtransaction_id ,in_amount_with_fee_val @@ -1896,11 +1960,11 @@ THEN -- primarily here to maximally use the existing index. PERFORM FROM refunds - WHERE - deposit_serial_id=dsi AND - rtransaction_id=in_rtransaction_id AND - amount_with_fee_val=in_amount_with_fee_val AND - amount_with_fee_frac=in_amount_with_fee_frac; + WHERE shard=in_deposit_shard + AND deposit_serial_id=dsi + AND rtransaction_id=in_rtransaction_id + AND amount_with_fee_val=in_amount_with_fee_val + AND amount_with_fee_frac=in_amount_with_fee_frac; IF NOT FOUND THEN @@ -1939,8 +2003,8 @@ SELECT tmp_val ,tmp_frac FROM refunds - WHERE - deposit_serial_id=dsi; + WHERE shard=in_deposit_shard + AND deposit_serial_id=dsi; IF tmp_val IS NULL THEN RAISE NOTICE 'failed to sum up existing refunds'; -- cgit v1.2.3