safe-global / safe-transaction-service / 17238630797

91%

master: 100%

Build Type

push

github

Committed by Uxio0

Commit Message

Optimize out of order query

- Use `(_from, timestamp)` index (already present for ether transfers) and add `id` for `Index Only Scan`
- Use `MAX` instead of sorting and getting the first element, better for the planner.

Old query is quite fast as there are not unprocessed values in the database

sql```
EXPLAIN ANALYZE
SELECT 1 AS a
FROM history_internaltxdecoded
INNER JOIN history_internaltx ON (history_internaltxdecoded.internal_tx_id = history_internaltx.id)
WHERE (history_internaltx._from = '\x<a class=hub.com/safe-global/safe-transaction-service/commit/f00040a635a907ac93924f2d8c36b3e87afef000">f00040a63' :: BYTEA
       AND NOT history_internaltxdecoded.processed
       AND history_internaltx.block_number <
         (SELECT U1. block_number
          FROM history_internaltxdecoded U0
          INNER JOIN history_internaltx U1 ON (U0. internal_tx_id = U1. id)
          WHERE (U1. _from = '\xf00040a635a907ac93924f2d8c36b3e87afef000' :: BYTEA
                 AND U0. processed)
          ORDER BY U1. block_number DESC
          LIMIT 1))
LIMIT 1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=21170.57..25853.21 rows=1 width=4) (actual time=0.193..0.194 rows=0 loops=1)
   InitPlan 1 (returns $2)
     ->  Limit  (cost=20892.07..21169.61 rows=1 width=4) (never executed)
           ->  Nested Loop  (cost=20892.07..62523.82 rows=150 width=4) (never executed)
                 ->  Gather Merge  (cost=20891.64..21477.35 rows=5029 width=12) (never executed)
                       Workers Planned: 2
                       Workers Launched: 0
                       ->  Sort  (cost=19891.61..19896.85 rows=2095 width=12) (never executed)
                             Sort Key: u1.block_number DESC
                             ->  Parallel Bitmap Heap Scan on history_internaltx u1  (cost=67.54..19776.05 rows=2095 width=12) (never executed)
                                   Recheck Cond: (_from = '... (continued)

Run Details

2 of 2 new or added lines in 1 file covered. (100.0%)

1 existing line in 1 file now uncovered.

16351 of 17299 relevant lines covered (94.52%)

0.95 hits per line