txn_participation_i index shrinking
- Lingua principale
- Go
- Stelle
- 116
- Fork
- 95
- Metriche di merge delle PR
- Nessuna PR unita negli ultimi 30g
Descrizione
## Problem
The largest index currently is txn_participation_i sitting @ almost 90GB while base table is only 60GB.
Smaller indexes are generally faster for writing and there are more possibilities of having them on dedicated/faster storage.
## Solution
IMHO txn_participation_i does not have to be unique.
Also we do not need to index the whole addr field.
After indexing on the first 8 bytes of the address like:
`create index concurrently txn_participation_i on txn_participation (substring(addr from 1 for 8),round DESC, intra DESC);`
the index down to 34GB (from 88GB).
With the current content of txn_participation there are only 4584 (~0.03%) collisions resulting in a SELECT operation having to filter through more than one row to find the match.
I have no standardized workload to see the perf impact but am happy with the space savings :D
## Dependencies
## Urgency
This requires a change to how queries are made and a reindex so not an urgent fix.
`select * from txn_participation where substring(addr from 1 for 8) = E'\\xe0880274c95396ae'::bytea and addr = E'\\xe0880274c95396ae1cc6eae54b61e2a7b5b1d7bf0d93387bc134f2e2d96bc894'::bytea`
Guida per i contributori
Nessuna guida per i contributori indicizzata per questo repository
Valutazione
Questa issue non è ancora stata valutata.