Queries on versioned_kv stream slow down with large number of keys

[nabil-monoceros]

Describe what's wrong

A versioned_kv stream with the following counts

select count() from table(options_iv_greeks_versioned) settings compact_kv_stream=false;

689612

And unique count

select count() from table(options_iv_greeks_versioned) settings compact_kv_stream=true;

364742

We noticed that simple queries such as select count(*) from table_name have been taking much longer as the number of total versions increased. After speaking with Ken, the likely cause is that lots of keys are not compacted in the background as it is an async process.

We tried to run optimize table table_name FINAL to force compaction but that did not seem to work.

How to reproduce
Generate similar versioned_kv stream with above properties and attempt to run a count(*) query.

Error message and/or stacktrace

N/A

Additional context

N/A