safe-global / safe-transaction-service / 10945438456

90%

master: 100%

push

github

Optimize queries for ERC20 balances

Query for used tokens
---------------------

Before:
```
EXPLAIN ANALYZE SELECT DISTINCT "history_erc20transfer"."address"
  FROM "history_erc20transfer"
 WHERE ("history_erc20transfer"."to" = '\x<a class=hub.com/safe-global/safe-transaction-service/commit/<a class="double-link" href="https://git"><a class=hub.com/safe-global/safe-transaction-service/commit/<a class="double-link" href="https://git"><a class=hub.com/safe-global/safe-transaction-service/commit/<a class="double-link" href="https://git"><a class=hub.com/safe-global/safe-transaction-service/commit/<a class="double-link" href="https://git"><a class=hub.com/safe-global/safe-transaction-service/commit/930dedddb92fef1b4ab2665d250877339f064eac">930dedddb'::bytea OR "history_erc20transfer"."_from" = '\x930dedddb92fef1b4ab2665d250877339f064eac'::bytea);
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2871805.29..2871815.76 rows=1047 width=21) (actual time=96564.298..96565.725 rows=1344 loops=1)
   Group Key: address
   Batches: 1  Memory Usage: 193kB
   ->  Gather  (cost=1000.00..2865068.91 rows=2694550 width=21) (actual time=1.969..96005.333 rows=2665149 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Seq Scan on history_erc20transfer  (cost=0.00..2594613.91 rows=1122729 width=21) (actual time=0.986..96075.232 rows=888383 loops=3)
               Filter: (("to" = '\x930dedddb92fef1b4ab2665d250877339f064eac'::bytea) OR (_from = '\x930dedddb92fef1b4ab2665d250877339f064eac'::bytea))
               Rows Removed by Filter: 32790615
 Planning Time: 0.856 ms
 Execution Time: 96565.877 ms
(11 rows)
```

After:
```
EXPLAIN ANALYZE SELECT DISTINCT "history_erc20transfer"."address"
  FROM "history_erc20transfer"
 WHERE ("history_erc20transfer"."_from" = '\x930dedddb92fef1b4ab2665d250877339f064eac'::bytea)
UNION
SELECT DISTINCT "history_erc20transfer"."address"
  FROM "history_erc20transfer"
 WHERE ("history_erc20transfer"."to" = '\x930dedddb92fef1b4ab2665d250877339f064eac'::bytea);
                                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------... (continued)

19 of 19 new or added lines in 3 files covered. (100.0%)

14470 of 15314 relevant lines covered (94.49%)

0.94 hits per line