diff options
author | Christian Grothoff <christian@grothoff.org> | 2021-11-29 10:09:21 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2021-11-29 10:09:21 +0100 |
commit | c8480d97a17e1ab8f358dadc48e6e9b3ea17f6da (patch) | |
tree | 6368e671458533a604df6b214d5decff9bd7457f /src/exchangedb | |
parent | 65533e8df5ea5ebf25bd9ae04186a475057efa9e (diff) | |
download | exchange-c8480d97a17e1ab8f358dadc48e6e9b3ea17f6da.tar.xz |
try to optimize aggregator queries
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/exchange-0001.sql | 13 | ||||
-rw-r--r-- | src/exchangedb/plugin_exchangedb_postgres.c | 22 |
2 files changed, 20 insertions, 15 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 4a6c9afb5..7acd67244 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -378,7 +378,7 @@ COMMENT ON COLUMN extension_details.extension_options CREATE TABLE IF NOT EXISTS deposits (deposit_serial_id BIGSERIAL PRIMARY KEY - ,shard INT8 NOT NULL DEFAULT 0 + ,shard INT8 NOT NULL ,known_coin_id INT8 NOT NULL REFERENCES known_coins (known_coin_id) ON DELETE CASCADE ,amount_with_fee_val INT8 NOT NULL ,amount_with_fee_frac INT4 NOT NULL @@ -400,7 +400,7 @@ CREATE TABLE IF NOT EXISTS deposits 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. Default of 0 onlyapplies for columns migrated from a previous version without sharding support. 64-bit value because we need an *unsigned* 32-bit value.'; + 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.'; COMMENT ON COLUMN deposits.wire_target_serial_id IS 'Identifies the target bank account and KYC status';COMMENT ON COLUMN deposits.wire_salt IS 'Salt used when hashing the payto://-URI to get the h_wire'; @@ -423,12 +423,11 @@ COMMENT ON INDEX deposits_coin_pub_merchant_contract_index IS 'for get_deposit_for_wtid and test_deposit_done'; CREATE INDEX IF NOT EXISTS deposits_get_ready_index ON deposits - (shard - ,wire_deadline - ,refund_deadline - ,tiny + (shard ASC ,done ,extension_blocked + ,tiny + ,wire_deadline ASC ); COMMENT ON INDEX deposits_coin_pub_merchant_contract_index IS 'for deposits_get_ready'; @@ -438,7 +437,7 @@ CREATE INDEX IF NOT EXISTS deposits_iterate_matching_index ,wire_target_serial_id ,done ,extension_blocked - ,wire_deadline + ,refund_deadline ASC ); COMMENT ON INDEX deposits_iterate_matching_index IS 'for deposits_iterate_matching'; diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 3591470e7..004516c51 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -1105,10 +1105,10 @@ prepare_statements (struct PostgresClosure *pg) " WHERE " " shard >= $2" " AND shard <= $3" - " AND wire_deadline<=$1" - " AND refund_deadline<$1" - " AND tiny=FALSE" " AND done=FALSE" + " AND extension_blocked=FALSE" + " AND tiny=FALSE" + " AND wire_deadline<=$1" " AND (kyc_ok OR $4)" " ORDER BY " " shard ASC" @@ -1130,14 +1130,16 @@ prepare_statements (struct PostgresClosure *pg) " JOIN known_coins kc USING (known_coin_id)" " JOIN denominations denom USING (denominations_serial)" " WHERE" - " merchant_pub=$1 AND" - " wire_target_serial_id=$2 AND" - " done=FALSE" - " ORDER BY wire_deadline ASC" + " merchant_pub=$1" + " AND wire_target_serial_id=$2" + " AND done=FALSE" + " AND extension_blocked=FALSE" + " AND refund_deadline<$3" + " ORDER BY refund_deadline ASC" " LIMIT " TALER_QUOTE ( TALER_EXCHANGEDB_MATCHING_DEPOSITS_LIMIT) ";", - 2), + 3), /* Used in #postgres_mark_deposit_tiny() */ GNUNET_PQ_make_prepare ( "mark_deposit_tiny", @@ -5505,9 +5507,11 @@ postgres_iterate_matching_deposits ( uint32_t limit) { struct PostgresClosure *pg = cls; + struct GNUNET_TIME_Absolute now; struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_auto_from_type (merchant_pub), GNUNET_PQ_query_param_uint64 (&wire_target), + GNUNET_PQ_query_param_absolute_time (&now), GNUNET_PQ_query_param_end }; struct MatchingDepositContext mdc = { @@ -5520,6 +5524,8 @@ postgres_iterate_matching_deposits ( }; enum GNUNET_DB_QueryStatus qs; + now = GNUNET_TIME_absolute_get (); + (void) GNUNET_TIME_round_abs (&now); qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, "deposits_iterate_matching", params, |