more work on recoup_by_reserve: add new materialized index to avoid hitting all partitions for the query

3 files changed, 71 insertions, 7 deletions

diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql
index aabfdb5e2..27595b819 100644
--- a/src/exchangedb/drop0001.sql
+++ b/src/exchangedb/drop0001.sql
@@ -31,6 +31,8 @@ DROP TRIGGER IF EXISTS reserves_out_on_insert ON reserves_out;
 DROP TRIGGER IF EXISTS reserves_out_on_delete ON reserves_out;
 DROP TRIGGER IF EXISTS deposits_on_insert ON deposits;
 DROP TRIGGER IF EXISTS deposits_on_delete ON deposits;
+DROP TRIGGER IF EXISTS recoup_on_insert ON recoup;
+DROP TRIGGER IF EXISTS recoup_on_delete ON recoup;
 DROP TABLE IF EXISTS revolving_work_shards CASCADE;
 DROP TABLE IF EXISTS extensions CASCADE;
 DROP TABLE IF EXISTS auditors CASCADE;
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 5b6921fd8..8bdd97396 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -1007,6 +1007,66 @@ $$;
 SELECT add_constraints_to_recoup_partition('default');
 
 
+CREATE TABLE IF NOT EXISTS recoup_by_reserve
+  (reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE
+  ,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32) 
+  )
+  PARTITION BY HASH (reserve_out_serial_id);
+COMMENT ON TABLE recoup_by_reserve
+  IS 'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by reserve_out_serial_id.';
+
+CREATE INDEX IF NOT EXISTS recoup_by_reserve_main_index
+  ON recoup_by_reserve
+  (reserve_out_serial_id);
+
+CREATE TABLE IF NOT EXISTS recoup_by_reserve_default
+  PARTITION OF recoup_by_reserve
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION recoup_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO recoup_by_reserve
+    (reserve_out_serial_id
+    ,coin_pub)
+  VALUES
+    (NEW.reserve_out_serial_id
+    ,NEW.coin_pub);
+  RETURN NEW;
+END $$;  
+COMMENT ON FUNCTION recoup_insert_trigger()
+  IS 'Replicate recoup inserts into recoup_by_reserve table.';
+
+CREATE TRIGGER recoup_on_insert
+  AFTER INSERT
+   ON recoup
+   FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
+
+
+CREATE OR REPLACE FUNCTION recoup_delete_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  DELETE FROM recoup_by_reserve
+   WHERE reserve_out_serial_id = OLD.reserve_out_serial_id
+     AND coin_pub = OLD.coin_pub;
+  RETURN OLD;
+END $$;  
+COMMENT ON FUNCTION recoup_delete_trigger()
+  IS 'Replicate recoup deletions into recoup_by_reserve table.';
+
+CREATE TRIGGER recoup_on_delete
+  AFTER DELETE
+    ON recoup
+   FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
+
+
+
+
+
 CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
   (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
   ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) 
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index c078d65a7..1b370ff37 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -1646,12 +1646,12 @@ prepare_statements (struct PostgresClosure *pg)
     GNUNET_PQ_make_prepare (
       "recoup_by_reserve",
       "SELECT "
-      "  coins.coin_pub,"
-      "  coin_sig,"
-      "  coin_blind,"
-      "  amount_val,"
-      "  amount_frac,"
-      "  recoup_timestamp,"
+      "  rc.coin_pub,"
+      "  rc.coin_sig,"
+      "  rc.coin_blind,"
+      "  rc.amount_val,"
+      "  rc.amount_frac,"
+      "  rc.recoup_timestamp,"
       "  denoms.denom_pub_hash,"
       "  coins.denom_sig"
       " FROM reserves res"
@@ -1659,8 +1659,10 @@ prepare_statements (struct PostgresClosure *pg)
       "   ON (res.reserve_uuid = ror.reserve_uuid)"
       " JOIN reserves_out ro"
       "   ON (ror.h_blind_ev = ro.h_blind_ev)"
+      " JOIN recoup_by_reserve rbr"
+      "   ON (rbr.reserve_out_serial_id = ro.reserve_out_serial_id)"
       " JOIN recoup rc"
-      "   ON (ro.reserve_out_serial_id = rc.reserve_out_serial_id)"
+      "   ON (rbr.coin_pub = rc.coin_pub)"
       " JOIN known_coins coins"
       "   ON (rc.coin_pub = coins.coin_pub)"
       " JOIN denominations denoms"