Exasol Indexes – Under the Hood
“Exasol is a self-tuning database, and this is especially true when it comes to indexes. Unlike other DBMS’s, indexes are automatically generated, reused, and discarded by the system as necessary. … “
https://docs.exasol.com/db/latest/performance/indexes.htm
No need to care about indexes with Exasol in general. Thus, no need to continue reading, except there is some interest in Exasol indexes – under the hood.
The documentation referenced above explains indexes in detail, creation, maintenance and especially the automatic removal:
„Indexes that are not used (read) after 35 days are automatically dropped. Write operations do not reset the 35 day counter.”
Again, no need to care about indexes in general. Well, eventually, indexes may grow more than expected and may impact system performance and require more DB RAM.
Documentation regarding Index volume:
https://docs.exasol.com/db/latest/administration/on-premise/sizing.htm
„Indexes are automatically created and maintained by the database. The index size depends on the chosen data model and queries, and can range from 2% to over 100% of the compressed data. A typical Exasol system will have an index volume of about 15% of compressed data.
Higher index volumes can negatively impact system performance and require more DB RAM.
If you have a running system, you can get the actual size of the indexes from the AUXILIARY_SIZE_*columns in the EXA_DB_SIZE_* statistical system tables. For more information, see Statistical System Tables. “
Hm, … require more DB RAM. Could be not easy. If, for example, nearly all available RAM of the cluster nodes is used, there may be no room for increasing DB RAM. Is there a chance to decrease Auxiliary Size? Maybe. As mentioned above, indexes, which are not used (read) after 35 days are automatically dropped. There is a (undocumented) DB-Parameter -indexLifetime which can be used to change the lifetime of an unused index, by default -indexLifetime=3024000 (35 days * 24 hours * 60 minutes * 60 seconds).
Let’s assume workload is mainly on a weekly basis. ETL and weekly reporting occurs on weekends, during the week some reporting and online analytics processing. It may be tested to use e.g. -indexLifetime=691200 (8 days) and verify if Auxiliary Size decreases. Indexes just used for some kind of monthly workload will be dropped after 8 days, leading to less Auxiliary Size. It should be kept in mind, the parameter change will take 35 days to be effective, because indexes last used before the change will be dropped after the previously (higher) lifetime interval. Beneath the mentioned advantage, there could be queries which require dropped indexes, e.g. during end of month processing. These indexes will be automatically created when required the first time, this will increase the runtime of the first query usage. It should be kept in mind, findings in pre-production systems may not be transferred to production systems, because workload differs significantly.
In case Auxiliary Size does not decrease noteworthy or to much index builds would occur, parameter -indexLifetime DB parameter may be increased or dropped.
And since -indexLifetime it an undocumented parameter, ask for confirmation by Exasol (simply via Support case).
Exasol indexes under the hood – usually no need to take a look at.