The query for pruning expired logs with a max limit set was taking longer than it should. This was part due to needing to join on an awkward combination combination of columns due to their being no single primary key.
Solution:
Add id column as PRIMARY KEY for evm.logs & evm.log_poller_blocks
Join on id column instead of previous primary keys
Replace all SELECT *'s with helper functions for selecting all columns
Refactor nestedBlockQuery into withConfs, and make a bit more use of it## Motivation
While adding the id column, we can't just remove the old primary key because the index on it was helping to accelerate some queries.
Instead of just resurrecting it as-is, I took the opportunity to clean up several of the indices on the logs & blocks table. Some indexed columns (eg created_at) were never actually being used,
while others were not ordered in the most optimal way for accelerating the queries we have. Also, at least one of them was redundant with the primary key just in a different order.
Problem:
The query for pruning expired logs with a max limit set was taking longer than it should. This was part due to needing to join on an awkward combination combination of columns due to their being no single primary key.
Solution:
While adding the id column, we can't just remove the old primary key because the index on it was helping to accelerate some queries. Instead of just resurrecting it as-is, I took the opportunity to clean up several of the indices on the logs & blocks table. Some indexed columns (eg created_at) were never actually being used, while others were not ordered in the most optimal way for accelerating the queries we have. Also, at least one of them was redundant with the primary key just in a different order.