If an index has never been used (or not for a long time), is it really necessary to use hard drive storage for the index?
After a sufficiently long time has passed, it seems like it would be possible to purge storage of index data. The index would need to be rebuilt if the index becomes required for a query.
Alternatively, a different implementation approach could be taken. It seems possible for indices to start off disabled. Once an index is used (for a query), it would become enabled automatically and rebuilt from existing table data.
The benefit of this approach would be somewhat reduced storage footprint. Secondary benefits may includes slightly faster save operations, and (possibly) slightly reduced journal footprint.
This approach would effectively catch cases where indices exist in class definitions but are no longer relevant for queries. It would also catch cases where data is persisted, but queries are never run against the class at all.
Thank you for submitting the idea. The status has been changed to "Future consideration".
Stay tuned!
Hi Nelson,
[apologies for the late response. only got wind of this one last week]
we intend to look into more automation around index analysis in the new calendar year, but because indices are table properties and not runtime aspects, the on-demand thing you're suggesting would require quite fundamental changes to our model. I'd also like to differentiate between system classes that are part of the core system (in IRISLIB) and those at the application layer (where I would categorize HSLIB), as the application has much more knowledge about expected usage patterns to anticipate which indices need to be considered. If you have specific thoughts about yours, please get in touch directly.
Thanks,
benjamin
Hi Nelson! Thank you for clarifying! Your idea has been promoted to the "Idea-A-Thon" status again.
Vadim and Alexander, thanks for the helpful information. Yes I believe my suggestion is similar to running the Index Usage report and looking for indices that are never used. However, I want to include all classes (including system classes). I believe the main difference in my suggestion is that I'm suggesting an automated or at least simplified approach to this. Furthermore, if there are unused indices in System tables, then I don't believe those could or should be modified by our users.
I am also suggesting the possibility that indices might default to this "transient" behavior until they are used the first time. For example, let's suppose I'm a user who never makes use of Embedded Python functionality. As a hypothetical (an probably inaccurate) example, let's also suppose that Embedded Python uses queries and indices against the System CompiledClass table. If I never use Embedded Python and never use these queries, I might be better off not storing the related indices. It seems to me that we currently store all indices just in case they might be used, rather than wait and enable them "on-demand". I realize this would impose a performance penalty the first time, but in some cases this might be an overall improvement.
Happy to take additional questions offline too
Hi Nelson!
Is your idea similar to existing IRIS functionality mentioned in the comment from Alexander Koblov? If not, please explain the difference.
Nelson, there is already Index Analyzer in IRIS. You can use it to find indices that are used and not used
https://docs.intersystems.com/iris20232/csp/docbook/DocBook.UI.Page.cls?KEY=GSOD_indexes#GSOD_indexes_analyze
Rebuilding index might take a long time. And the bigger the index, the longer it might take