Apr 20, 2023

I've concluded that the central issue I'm dealing with in terms of confirmation query performance is the structure of the input table. Since identification of spender(s) from an output point requires a reverse index which is 1:1 with inputs, the index was attached to the input table. This saves about 6GB in redundant storage (and is super elegant). However as inherently the largest table in the store by far (at 322GB) this space savings is entirely overwhelmed by the paging cost that it introduces. So I'm splitting out the spend index into a 30GB record table that indexes output point to spending tx. The tx is all that is required for the confirmation process. For another 10GB we could store the fk of the spending input as well, however there is no need to optimize this for validation/confirmation. The spender can otherwise be identified in constant time by walking the spending tx's inputs for the matching point, which in most cases is a walk of no more than two items, so prob not worth the extra space. The input table then becomes a blob table with no direct search, just like output. I concluded that the confirmation perf problem was limited to this one query and not a consequence of the query complexity in general after reducing the complex query to the single table. Should have some numbers on this today.