Index not being consulted sometimes during searchOperationalIntents

[wing-utm-sharing-airspace]

During subscription creation, when DSS searches for overlapping intents, sometimes the search query becomes ~20X slow when the index is not consulted - resulting in a full table scan. When the index is consulted, the query is very fast.

On wing side, we do sometimes see Put Subscription latency around 4 to 5 seconds - which may have to do with this? It would be great if InterUSS could investigate why this happens. Optionally InterUSS could also consider adding an index hint to force using the index?

[the-glu]

During recent performances checks, I noticed the same thing.

My understanding is that CRDB has wrong statistics, think the database is small and it's faster to just do a full table scan instead of using the index.

However it was only happening at the start of tests, and as soon as CRDB update it's internal metrics the problem is gone, so I didn't investigated more.

Do you see the issue regularly or changes in the performance overtime? (E.g. using the index and then stopping after a while?)

A solution would indeed be to force index usage everywhere it should be, that not complex but require knowing if it's Crdb or yugabyte used in queries in the code.

Potential downside is that we don't leverage automatic optimization where the index should not be used, but that a theoretical issue that may not exist in practice.

[wing-utm-sharing-airspace]

Do you see the issue regularly or changes in the performance overtime?

Great question but I lack a concrete answer: I was generally looking at Subscription Creation latency as a Client (as in, from our internal dashboards - when we request a subscription creation to DSS, how long it takes). The latency is generally good actually - but there were random 4.5 to 5 second jumps. That is when I looked a bit into CRDB and noticed the index issue.

How do you feel about getting CRDB to calculate statistics more often?