algorand / algorand/indexer

txn_participation_i index shrinking

Aperta
#816 1 commento 1 reazione 0 assegnatari Vedi su GitHub
new-feature-request performance Team Lamprey
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.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.