summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorChristian Grothoff <grothoff@gnunet.org>2022-03-19 12:58:12 +0100
committerChristian Grothoff <grothoff@gnunet.org>2022-03-19 12:58:37 +0100
commitf22125ce0fa074f0dbf090ccbbee3f119c94e619 (patch)
tree69fda1fd4d1969c4e4dd82bc42300744bd115f1d
parentb4f7231d17ce995bd4b36776a01e3e2ececbe3bd (diff)
downloadexchange-f22125ce0fa074f0dbf090ccbbee3f119c94e619.tar.gz
exchange-f22125ce0fa074f0dbf090ccbbee3f119c94e619.tar.bz2
exchange-f22125ce0fa074f0dbf090ccbbee3f119c94e619.zip
add missing index, move indices closer to table generation
-rw-r--r--src/exchangedb/exchange-0001.sql168
1 files changed, 96 insertions, 72 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index ba40a91ce..5b6921fd8 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -227,6 +227,13 @@ COMMENT ON TABLE reserves_close
COMMENT ON COLUMN reserves_close.wire_target_h_payto
IS 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.';
+CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index
+ ON reserves_close
+ (close_uuid);
+CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
+ ON reserves_close
+ (reserve_pub);
+
CREATE TABLE IF NOT EXISTS reserves_close_default
PARTITION OF reserves_close
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -248,12 +255,6 @@ $$;
SELECT add_constraints_to_reserves_close_partition('default');
-CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index
- ON reserves_close
- (close_uuid);
-CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
- ON reserves_close
- (reserve_pub);
CREATE TABLE IF NOT EXISTS reserves_out
@@ -275,6 +276,17 @@ COMMENT ON COLUMN reserves_out.h_blind_ev
COMMENT ON COLUMN reserves_out.denominations_serial
IS 'We do not CASCADE ON DELETE here, we may keep the denomination data alive';
+CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
+ ON reserves_out
+ (reserve_out_serial_id);
+CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_uuid_and_execution_date_index
+ ON reserves_out
+ (reserve_uuid, execution_date);
+COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
+ IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
+
+
+
CREATE TABLE IF NOT EXISTS reserves_out_default
PARTITION OF reserves_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -296,15 +308,6 @@ $$;
SELECT add_constraints_to_reserves_out_partition('default');
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
- ON reserves_out
- (reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_uuid_and_execution_date_index
- ON reserves_out
- (reserve_uuid, execution_date);
-COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
- IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
-
CREATE TABLE IF NOT EXISTS auditors
(auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
@@ -514,6 +517,13 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
COMMENT ON COLUMN refresh_revealed_coins.ev_sig
IS 'exchange signature over the envelope';
+CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index
+ ON refresh_revealed_coins
+ (rrc_serial);
+CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
+ ON refresh_revealed_coins
+ (melt_serial_id);
+
CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
PARTITION OF refresh_revealed_coins
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -541,12 +551,6 @@ $$;
SELECT add_constraints_to_refresh_revealed_coins_partition('default');
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index
- ON refresh_revealed_coins
- (rrc_serial);
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
- ON refresh_revealed_coins
- (melt_serial_id);
CREATE TABLE IF NOT EXISTS refresh_transfer_keys
@@ -626,27 +630,6 @@ CREATE TABLE IF NOT EXISTS deposits
)
PARTITION BY HASH (shard); -- FIXME: why not BY RANGE? RANGE would seem better for 'deposits_get_ready'!
-CREATE TABLE IF NOT EXISTS deposits_default
- PARTITION OF deposits
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE deposits_' || partition_suffix || ' '
- 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey '
- 'PRIMARY KEY (deposit_serial_id)'
- );
-END
-$$;
-
-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
@@ -679,6 +662,7 @@ CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index
);
COMMENT ON INDEX deposits_for_get_ready_index
IS 'for deposits_get_ready';
+
CREATE INDEX IF NOT EXISTS deposits_for_iterate_matching_index
ON deposits
(shard
@@ -692,6 +676,29 @@ COMMENT ON INDEX deposits_for_iterate_matching_index
IS 'for deposits_iterate_matching';
+CREATE TABLE IF NOT EXISTS deposits_default
+ PARTITION OF deposits
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE deposits_' || partition_suffix || ' '
+ 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey '
+ 'PRIMARY KEY (deposit_serial_id)'
+ );
+END
+$$;
+
+SELECT add_constraints_to_deposits_partition('default');
+
+
+
CREATE TABLE IF NOT EXISTS deposits_by_coin
(deposit_serial_id BIGINT
,shard INT8 NOT NULL
@@ -701,11 +708,14 @@ CREATE TABLE IF NOT EXISTS deposits_by_coin
COMMENT ON TABLE deposits_by_coin
IS 'Enables fast lookups of deposit by coin_pub, auto-populated via TRIGGER below';
+CREATE INDEX IF NOT EXISTS deposits_by_coin_main_index
+ ON deposits_by_coin
+ (coin_pub);
+
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
@@ -729,7 +739,6 @@ CREATE TRIGGER deposits_on_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
@@ -817,6 +826,14 @@ COMMENT ON COLUMN wire_out.exchange_account_section
COMMENT ON COLUMN wire_out.wire_target_h_payto
IS 'Identifies the credited bank account and KYC status';
+CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index
+ ON wire_out
+ (wireout_uuid);
+CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_h_payto_index
+ ON wire_out
+ (wire_target_h_payto);
+
+
CREATE TABLE IF NOT EXISTS wire_out_default
PARTITION OF wire_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -838,12 +855,6 @@ $$;
SELECT add_constraints_to_wire_out_partition('default');
-CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index
- ON wire_out
- (wireout_uuid);
-CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_h_payto_index
- ON wire_out
- (wire_target_h_payto);
CREATE TABLE IF NOT EXISTS aggregation_tracking
@@ -963,6 +974,17 @@ COMMENT ON COLUMN recoup.coin_sig
COMMENT ON COLUMN recoup.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.';
+CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
+ ON recoup
+ (recoup_uuid);
+CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
+ ON recoup
+ (reserve_out_serial_id);
+CREATE INDEX IF NOT EXISTS recoup_by_coin_pub_index
+ ON recoup
+ (coin_pub);
+
+
CREATE TABLE IF NOT EXISTS recoup_default
PARTITION OF recoup
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -984,16 +1006,6 @@ $$;
SELECT add_constraints_to_recoup_partition('default');
-CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
- ON recoup
- (recoup_uuid);
-CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
- ON recoup
- (reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS recoup_by_coin_pub_index
- ON recoup
- (coin_pub);
-
CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
(reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
@@ -1003,6 +1015,11 @@ CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
COMMENT ON TABLE reserves_out_by_reserve
IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.';
+CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index
+ ON reserves_out_by_reserve
+ (reserve_uuid);
+
+
CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
PARTITION OF reserves_out_by_reserve
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -1047,6 +1064,9 @@ CREATE TRIGGER reserves_out_on_delete
FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+
+
+
CREATE TABLE IF NOT EXISTS recoup_refresh
(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub)
@@ -1070,6 +1090,17 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial
COMMENT ON COLUMN recoup_refresh.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.';
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
+ ON recoup_refresh
+ (recoup_refresh_uuid);
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
+ ON recoup_refresh
+ (rrc_serial);
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
+ ON recoup_refresh
+ (coin_pub);
+
+
CREATE TABLE IF NOT EXISTS recoup_refresh_default
PARTITION OF recoup_refresh
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -1091,15 +1122,6 @@ $$;
SELECT add_constraints_to_recoup_refresh_partition('default');
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
- ON recoup_refresh
- (recoup_refresh_uuid);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
- ON recoup_refresh
- (rrc_serial);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
- ON recoup_refresh
- (coin_pub);
CREATE TABLE IF NOT EXISTS prewire
@@ -1118,9 +1140,6 @@ COMMENT ON COLUMN prewire.finished
IS 'set to TRUE once bank confirmed receiving the wire transfer request';
COMMENT ON COLUMN prewire.buf
IS 'serialized data to send to the bank to execute the wire transfer';
-CREATE TABLE IF NOT EXISTS prewire_default
- PARTITION OF prewire
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
CREATE INDEX IF NOT EXISTS prewire_by_finished_index
ON prewire
@@ -1134,6 +1153,11 @@ CREATE INDEX IF NOT EXISTS prewire_by_failed_finished_index
COMMENT ON INDEX prewire_by_failed_finished_index
IS 'for wire_prepare_data_get';
+CREATE TABLE IF NOT EXISTS prewire_default
+ PARTITION OF prewire
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
CREATE TABLE IF NOT EXISTS wire_accounts
(payto_uri VARCHAR PRIMARY KEY