add deposits_by_coin table to speed-up certain queries (in theory)

1 files changed, 95 insertions, 31 deletions

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';