txn_participation_i index shrinking

[urtho]

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

[winder]

Thanks for the detailed suggestion!