diff options
author | Christian Grothoff <grothoff@gnunet.org> | 2022-03-19 14:05:45 +0100 |
---|---|---|
committer | Christian Grothoff <grothoff@gnunet.org> | 2022-03-19 14:05:45 +0100 |
commit | f5e5f4b843f09f9b68ea1998ce18b369ccbcccd7 (patch) | |
tree | 88494d966f0b2d435640cfddc4fa2b5442fb9dd3 /src/exchangedb | |
parent | f22125ce0fa074f0dbf090ccbbee3f119c94e619 (diff) | |
download | exchange-f5e5f4b843f09f9b68ea1998ce18b369ccbcccd7.tar.xz |
more work on recoup_by_reserve: add new materialized index to avoid hitting all partitions for the query
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/drop0001.sql | 2 | ||||
-rw-r--r-- | src/exchangedb/exchange-0001.sql | 60 | ||||
-rw-r--r-- | src/exchangedb/plugin_exchangedb_postgres.c | 16 |
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" |