Hmmm, discovered an embarrassing mistake BUT THIS IS GOOD NEWS.
I used the LIKE operator intstead of =.
The findings after this change are better.
The time to look up an asset in the assets table is negligible – even after 1M records added
SELECT * FROM assets WHERE asset_name = 'X'
On the issuances table` the increase is dramatic. Increasing the rows from <200k to 1.2M (6x) makes the query ~13x slower (from 15ms to 190ms). Worse than O(n) performance!
SELECT COUNT(*) FROM issuances WHERE asset = 'X'
Of course, there may be more that I’ve overlooked. Other, more relevant, queries for example?