Fix slow query when getting threshold for multisig-transactions #1876
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Uxio0
force-pushed
the
fix-slow-query
branch
from
eaaf0d3
to
65b390d
Compare
|
The number of result rows are not the same. 37 VS 25. |
- Getting the threshold for executed transactions was slow, as sorting was done using multiple tables.
- Sorting was optimized to use the `internal_tx_id`. As traces are inserted in order and atomically, that should do it, otherwise is not a critical error.
- A index was created to optimize `MultisigTransaction` retrieval.
Another solution would be to use a Window function, but performance now is quite good.
Query went from
```sql
Limit (cost=2161.92..23561.22 rows=21 width=1993) (actual time=24695.459..29421.259 rows=21 loops=1)
-> Result (cost=2161.92..83326974.35 rows=81770 width=1993) (actual time=24695.458..29421.247 rows=21 loops=1)
-> Incremental Sort (cost=2161.92..8347228.91 rows=81770 width=1952) (actual time=177.971..177.991 rows=21 loops=1)
Sort Key: history_multisigtransaction.nonce DESC, history_multisigtransaction.created DESC
Presorted Key: history_multisigtransaction.nonce
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 98kB Peak Memory: 98kB
-> Nested Loop Left Join (cost=1.42..8342967.85 rows=81770 width=1952) (actual time=13.972..177.805 rows=22 loops=1)
-> Nested Loop Left Join (cost=0.99..7839429.23 rows=81770 width=1862) (actual time=10.742..117.670 rows=22 loops=1)
-> Index Scan Backward using history_multisigtransaction_nonce_a98eecaa on history_multisigtransaction (cost=0.43..7227223.48 rows=81770 width=380) (actual time=5.918..11.412 rows=22 loops=1)
Filter: (safe = '\xXXXXXXXXXXXXXXXXXXXXXX'::bytea)
Rows Removed by Filter: 3
-> Index Scan using history_ethereumtx_pkey on history_ethereumtx (cost=0.56..7.49 rows=1 width=1482) (actual time=4.827..4.827 rows=1 loops=22)
Index Cond: (tx_hash = history_multisigtransaction.ethereum_tx_id)
-> Index Scan using history_ethereumblock_pkey on history_ethereumblock (cost=0.43..6.16 rows=1 width=90) (actual time=2.730..2.730 rows=1 loops=22)
Index Cond: (number = history_ethereumtx.block_id)
SubPlan 1
-> Limit (cost=0.42..8.44 rows=1 width=5) (never executed)
-> Index Scan using history_safelaststatus_pkey on history_safelaststatus u0 (cost=0.42..8.44 rows=1 width=5) (never executed)
Index Cond: (address = history_multisigtransaction.safe)
SubPlan 2
-> Limit (cost=606.17..908.51 rows=1 width=46) (actual time=1392.528..1392.528 rows=1 loops=21)
-> Incremental Sort (cost=606.17..1210.85 rows=2 width=46) (actual time=1392.526..1392.526 rows=1 loops=21)
Sort Key: u0_1.nonce, u1.block_number, u2.transaction_index, u0_1.internal_tx_id
Presorted Key: u0_1.nonce
Full-sort Groups: 21 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
-> Nested Loop (cost=1.55..1210.76 rows=1 width=46) (actual time=1392.407..1392.497 rows=1 loops=21)
-> Nested Loop (cost=0.99..1202.18 rows=1 width=75) (actual time=1392.254..1392.343 rows=1 loops=21)
-> Index Scan Backward using history_saf_address_aa71bd_idx on history_safestatus u0_1 (cost=0.56..390.22 rows=96 width=38) (actual time=0.378..182.739 rows=83301 loops=21)
Index Cond: (address = history_multisigtransaction.safe)
-> Index Scan using history_internaltx_pkey on history_internaltx u1 (cost=0.44..8.46 rows=1 width=45) (actual time=0.014..0.014 rows=0 loops=1749321)
Index Cond: (id = u0_1.internal_tx_id)
Filter: (ethereum_tx_id = history_multisigtransaction.ethereum_tx_id)
Rows Removed by Filter: 1
-> Index Scan using history_ethereumtx_pkey on history_ethereumtx u2 (cost=0.56..8.57 rows=1 width=37) (actual time=0.150..0.150 rows=1 loops=21)
Index Cond: (tx_hash = history_multisigtransaction.ethereum_tx_id)
Planning Time: 84.834 ms
Execution Time: 29421.447 ms
(37 rows)
```
to
```sql
Limit (cost=2161.92..4662.61 rows=21 width=1993) (actual time=0.377..0.485 rows=21 loops=1)
-> Result (cost=2161.92..9739363.16 rows=81770 width=1993) (actual time=0.376..0.481 rows=21 loops=1)
-> Incremental Sort (cost=2161.92..8347228.91 rows=81770 width=1952) (actual time=0.348..0.350 rows=21 loops=1)
Sort Key: history_multisigtransaction.nonce DESC, history_multisigtransaction.created DESC
Presorted Key: history_multisigtransaction.nonce
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 98kB Peak Memory: 98kB
-> Nested Loop Left Join (cost=1.42..8342967.85 rows=81770 width=1952) (actual time=0.041..0.287 rows=22 loops=1)
-> Nested Loop Left Join (cost=0.99..7839429.23 rows=81770 width=1862) (actual time=0.032..0.213 rows=22 loops=1)
-> Index Scan Backward using history_multisigtransaction_nonce_a98eecaa on history_multisigtransaction (cost=0.43..7227223.48 rows=81770 width=380) (actual time=0.017..0.049 rows=22 loops=1)
Filter: (safe = '\xXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx'::bytea)
Rows Removed by Filter: 3
-> Index Scan using history_ethereumtx_pkey on history_ethereumtx (cost=0.56..7.49 rows=1 width=1482) (actual time=0.007..0.007 rows=1 loops=22)
Index Cond: (tx_hash = history_multisigtransaction.ethereum_tx_id)
-> Index Scan using history_ethereumblock_pkey on history_ethereumblock (cost=0.43..6.16 rows=1 width=90) (actual time=0.003..0.003 rows=1 loops=22)
Index Cond: (number = history_ethereumtx.block_id)
SubPlan 1
-> Limit (cost=0.42..8.44 rows=1 width=5) (never executed)
-> Index Scan using history_safelaststatus_pkey on history_safelaststatus u0 (cost=0.42..8.44 rows=1 width=5) (never executed)
Index Cond: (address = history_multisigtransaction.safe)
SubPlan 2
-> Limit (cost=0.56..8.57 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=21)
-> Index Scan Backward using history_saf_address_1c362b_idx on history_safestatus u0_1 (cost=0.56..8.57 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=21)
Index Cond: ((address = history_multisigtransaction.safe) AND (nonce = history_multisigtransaction.nonce))
Planning Time: 0.577 ms
Execution Time: 0.580 ms
(25 rows)
```
Uxio0
force-pushed
the
fix-slow-query
branch
from
5b80391
to
48fa70a
Compare
I think I changed the |
|
@moisses89 Those are the rows of the SQL planner, not the rows returned 😂 |
I see, very useful information |
moisses89
approved these changes
Feb 19, 2024
|
Looks good to me! |
|
Good to know, thanks @JagoFigueroa |
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
internal_tx_id. As traces are inserted in order and atomically, that should do it, otherwise is not a critical error.MultisigTransactionretrieval.SafeStatusAnother solution would be to use a Window function, but performance now is quite good.
Query went from
to