stacks-network/stacks-core

Perf: Add metadata index by block hash to Clarity DB

Closed this issue · 1 comments

The metadata_table in Clarity is normally queried by (key, blockhash) for which there is an index (due to the UNIQUE constraint), but during block commits, the metadata is moved from a placeholder blockhash to the final blockhash. This invokes UPDATE … WHERE blockhash = ? which requires a SCAN. With some naive perf analysis, this represented 388 samples out of 470 perf samples with process_next_nakamoto_block. Obviously, this could be a skewed sample, but adding an index here should be an easy performance boost:

CREATE INDEX IF NOT EXISTS md_blockhashes ON metadata_table(blockhash)

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.