Logs Cycle Tables - Missing Indexes

Logs Cycle Tables - Missing Indexes


I've identified that, in version, all of the Logs Cycle Tables, oslog_*, are missing indexes on the [Instant] attribute.
Seeing that these indexes could help accelerate queries on systems with lots of Log records, should't they be present?

Thank you.

Hi Carlos

Great talking to you again. I was surprised at your question so I dug a bit into it and reached some interesting conclusions.

There are barely any logs in the log tables.
This is the first and more interesting finding. I actually expected this, so I was not surprised. You see, one of the biggest concerns with the logging mechanism is performance - have as little impact with the logging in the runtime performance.
The fact these tables reach multiple millions of records means that, the more indexes they have, the slower the write is. We already have things like bulk insert to maximize insert performance, and having less indexes ensures a lesser weight on insert on these tables.
I did find some indexes on "eSpace_Id" in the occasional table ("Extension_Id" on the Extension log) and the SMS log table has more complex indexes (related to the delivery part, in which they are looked up). But these are very scarce.

These indexes are not missing in, they never existed.
At least in SQL Server I could not find indexes on [Instant] all the way back to Platform 6.

Doesn't this have the potential to make querying these tables extremely slow?
Yes. Queries on these tables can take a long time and take a performance hit on the server. So anything that does analysis on the log tables should take it into consideration. 
If really really needed, in particular cases, I would advise copying the data (assynchronously) to similar data models with more indexes.

Hope this helps.

Hi Acácio,

Thank you for your comprehensive answer, it's a sound reason to maximize the insert performance of the Logs.
When the Logs tables have many thousands of records, it does get slow querying them, sometimes even getting time-outs, but that only impacts troubleshooting tasks.

I actually came across the lack of indexes when building my Logs Management tool - https://www.outsystems.com/forge/component/1195/logs-management/ - and started doing temporal analisys to the Logs tables.