algorand / algorand/indexer

txn_participation_i index shrinking

Aberta
#816 1 comentário 1 reação 0 responsáveis Ver no GitHub
new-feature-request performance Team Lamprey
Linguagem predominante
Go
Estrelas
116
Forks
95
Métricas de merge de PRs
Nenhum PR com merge em 30d

Descrição

## 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`

Guia de contribuição

Nenhum guia de contribuição indexado para este repositório

Avaliação

Esta issue ainda não foi avaliada.

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.