mirror of
https://mau.dev/mautrix/go.git
synced 2026-03-14 14:25:53 +01:00
fa7c1ae2bc```
2025-08-24T22:23:19Z debug [MatrixBridgeV2] {"level":"warn","component":"matrix","component":"client_loop","subcomponent":"sync_key_backup_loop","rows":0,"duration_seconds":1.046191042,"method":"EndRows","query":"SELECT room_id, sender_key, signing_key, session, forwarding_chains, ratchet_safety, received_at, max_age, max_messages, is_scheduled, key_backup_version FROM crypto_megolm_inbound_session WHERE account_id=?1 AND session IS NOT NULL AND key_backup_version != ?2","time":"2025-08-24T22:23:19.22077Z","message":"Query took long"}
```
before:
```
sqlite> EXPLAIN SELECT room_id, sender_key, signing_key, session, forwarding_chains, ratchet_safety, received_at, max_age, max_messages, is_scheduled, key_backup_version FROM crypto_megolm_inbound_session WHERE account_id='@brad:beeper.com/CHNWOJWEUC' AND sessi
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 25 0 0 Start at 25
1 OpenRead 0 48 0 15 0 root=48 iDb=0; crypto_megolm_inbound_session
2 OpenRead 1 49 0 k(3,,,) 2 root=49 iDb=0; sqlite_autoindex_crypto_megolm_inbound_session_1
3 String8 0 1 0 @brad:beeper.com/CHNWOJWEUC 0 r[1]='@brad:beeper.com/CHNWOJWEUC'
4 SeekGE 1 24 1 1 0 key=r[1]
5 IdxGT 1 24 1 1 0 key=r[1]
6 DeferredSeek 1 0 0 0 Move 0 to 1.rowid if needed
7 Column 0 5 2 128 r[2]= cursor 0 column 5
8 IsNull 2 23 0 0 if r[2]==NULL goto 23
9 Column 0 14 2 0 r[2]=crypto_megolm_inbound_session.key_backup_version
10 Eq 3 23 2 BINARY-8 82 if r[2]==r[3] goto 23
11 Column 0 4 4 0 r[4]= cursor 0 column 4
12 Column 0 2 5 0 r[5]= cursor 0 column 2
13 Column 0 3 6 0 r[6]= cursor 0 column 3
14 Column 0 5 7 0 r[7]= cursor 0 column 5
15 Column 0 6 8 0 r[8]= cursor 0 column 6
16 Column 0 9 9 0 r[9]= cursor 0 column 9
17 Column 0 10 10 0 r[10]= cursor 0 column 10
18 Column 0 11 11 0 r[11]= cursor 0 column 11
19 Column 0 12 12 0 r[12]= cursor 0 column 12
20 Column 0 13 13 0 0 r[13]=crypto_megolm_inbound_session.is_scheduled
21 Column 0 14 14 0 r[14]=crypto_megolm_inbound_session.key_backup_version
22 ResultRow 4 11 0 0 output=r[4..14]
23 Next 1 5 0 0
24 Halt 0 0 0 0
25 Transaction 0 0 55 0 1 usesStmtJournal=0
26 Integer 1 3 0 0 r[3]=1
27 Goto 0 1 0 0
sqlite> SELECT COUNT(*) FROM crypto_megolm_inbound_session ;
+----------+
| COUNT(*) |
+----------+
| 168792 |
+----------+
sqlite> SELECT COUNT(*) FROM crypto_megolm_inbound_session WHERE session IS NULL;
+----------+
| COUNT(*) |
+----------+
| 39 |
+----------+
sqlite> SELECT COUNT(*) FROM crypto_megolm_inbound_session WHERE key_backup_version != 1;
+----------+
| COUNT(*) |
+----------+
| 39 |
+----------+
```
after:
```
sqlite> CREATE INDEX idx_megolm_filtered
...> ON crypto_megolm_inbound_session(account_id, key_backup_version, session);
sqlite> EXPLAIN SELECT room_id, sender_key, signing_key, session, forwarding_chains, ratchet_safety, received_at, max_age, max_messages, is_scheduled, key_backup_version FROM crypto_megolm_inbound_session WHERE account_id='@brad:beeper.com/CHNWOJWEUC' AND session IS NOT NULL AND key_backup_version != 1;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 25 0 0 Start at 25
1 OpenRead 0 48 0 15 0 root=48 iDb=0; crypto_megolm_inbound_session
2 OpenRead 1 91264 0 k(4,,,,) 2 root=91264 iDb=0; idx_megolm_filtered
3 String8 0 1 0 @brad:beeper.com/CHNWOJWEUC 0 r[1]='@brad:beeper.com/CHNWOJWEUC'
4 SeekGE 1 24 1 1 0 key=r[1]
5 IdxGT 1 24 1 1 0 key=r[1]
6 DeferredSeek 1 0 0 0 Move 0 to 1.rowid if needed
7 Column 1 2 2 128 r[2]= cursor 1 column 2
8 IsNull 2 23 0 0 if r[2]==NULL goto 23
9 Column 1 1 2 0 r[2]=crypto_megolm_inbound_session.key_backup_version
10 Eq 3 23 2 BINARY-8 82 if r[2]==r[3] goto 23
11 Column 0 4 4 0 r[4]= cursor 0 column 4
12 Column 0 2 5 0 r[5]= cursor 0 column 2
13 Column 0 3 6 0 r[6]= cursor 0 column 3
14 Column 1 2 7 0 r[7]= cursor 1 column 2
15 Column 0 6 8 0 r[8]= cursor 0 column 6
16 Column 0 9 9 0 r[9]= cursor 0 column 9
17 Column 0 10 10 0 r[10]= cursor 0 column 10
18 Column 0 11 11 0 r[11]= cursor 0 column 11
19 Column 0 12 12 0 r[12]= cursor 0 column 12
20 Column 0 13 13 0 0 r[13]=crypto_megolm_inbound_session.is_scheduled
21 Column 1 1 14 0 r[14]=crypto_megolm_inbound_session.key_backup_version
22 ResultRow 4 11 0 0 output=r[4..14]
23 Next 1 5 0 0
24 Halt 0 0 0 0
25 Transaction 0 0 56 0 1 usesStmtJournal=0
26 Integer 1 3 0 0 r[3]=1
27 Goto 0 1 0 0
sqlite>
```
|
||
|---|---|---|
| .. | ||
| 00-latest-revision.sql | ||
| 04-cross-signing-keys.sql | ||
| 05-varchar-to-text.sql | ||
| 06-olm-session-last-used-split.sql | ||
| 07-trust-state-value-change.sql | ||
| 08-cs-key-expired-field.sql | ||
| 09-max-age-ms.sql | ||
| 10-mark-ratchetable-keys.sql | ||
| 11-outdated-devices.sql | ||
| 12-secrets.sql | ||
| 13-megolm-session-sharing.sql | ||
| 14-account-key-backup-version.sql | ||
| 15-fix-secrets.sql | ||
| 16-crypto-olm-sessions-index.sql | ||
| 17-decrypted-olm-messages.sql | ||
| 18-megolm-inbound-session-backup-index.sql | ||
| upgrade.go | ||