Indexing with stake address

[baymac]

Is it possible to get all transaction from addresses owned by stake address faster? Currently search by stake address takes longer time. Is it because kupo tries to find all owned addresses and find utxos for each address?

[KtorZ]

Searching by stake address takes indeed some time, because it is entire addresses that are stored, and not only the stake part. The index is optimized for addresses because that's the most common use-case. It should still remain relatively fast for stake address too however.

May I ask about how long it takes for you and what version you're using?

Note: I also just noticed that it seems that the index isn't used for searching addresses by stake.. which would explain why it is slow as it has to scan through the whole database. That's a problem though because the index should be used here too! I'll look into this.

[KtorZ]

rm kupo.sqlite3

😅... And restart. I haven't yet pushed the changes though. I need to add a few more comments and details explaining what I did before pushing it upstream. It is non-trivial.

I'll let you know once pushed. You'll need to delete your current working directory / database and restart from scratch for the database to be rebuilt.

[baymac]

Sounds good, thanks 👍

[KtorZ]

Latest master should now perform a lot better when it comes to querying addresses by stake credentials.

[baymac]

The changes looks great. Query by stake credentials response is almost instant. Thanks @KtorZ for working on this. :)

[KtorZ]

Query by stake credentials response is almost instant.

It should be 😄 !

Thanks @KtorZ for working on this. :)

Thanks for raising this now. I was actually right in the middle of benchmarks as I am wrapping up the next major release (v2.0.0). So it would have been really unfortunate to learn about this after the release since this required altering the database schema in a non-backward compatible way. So until v2.0.0 I am still fairly open to breaking changes, it's a good opportunity to pack them altogether.

Cheers!

[longngn]

Does SQLite have hash table index so we could reduce query complexity even more from O(log n) to O(1)?

[KtorZ]

A hash table would still provide O(log n) access to its elements though 🤔 ? A finger tree could possibly gives amortized O(1), but that'd be to maintain separately and likely in-memory.