Automated index creation

lutter commented 1 year ago

Description

We could employ some simple heuristics to ease the need for manual index creation to support slow queries. The basic idea is that the LoadManager is aware of the load in each shard, and which queries (by shape hash) cause what percentage of the load there.

We could therefore use this information to trigger automatically creating indexes to speed up the query/queries that are causing most of the load. Once a decision has been made to improve a particular GraphQL query, we would run that query in trace mode to identify the slowest SQL query (corresponding to a specific part of the GraphQL query) and then use some heuristics to try and improve the query. For example, those heuristics could use

if the table has a lot of versions/entity: set table to account-like
if the query filters by one attribute with = and orders by another attribute or filters by another attribute with < etc: create a BTree on those two attributes
if most of the query are executed against a recent block: create a partial index

There's lots of experimentation we could do with these heuristics, but they could lower the load for operators considerably.

This issue is in complementary to #4325 as it covers very similar functionality, but could reduce the need for creating indexing rules, too.

Are you aware of any blockers that must be resolved before implementing this feature? If so, which? Link to any relevant GitHub issues.

Issue #4262 needs to addressed first to ensure that the LoadManager has accurate statistics about query execution

Some information to help us out

[ ] Tick this box if you plan on implementing this feature yourself.
[X] I have searched the issue tracker to make sure this issue is not a duplicate.

github-actions[bot] commented 11 months ago

Looks like this issue has been open for 6 months with no activity. Is it still relevant? If not, please remember to close it.

alex-pakalniskis commented 11 months ago

Related request shared with me during this week's IOH

automate creating indexes and deleting shitty ones -Payne

graphprotocol / graph-node