Costs Associated with Indexes

Costs Associated with Indexes

Indexes entail certain costs. Memory consumption and incremental maintenance are two major cost factors that need to be considered.

Memory Consumption

To store the mapping information of value IDs to records, each index uses an inverted list that needs to be kept in main memory. This list typically requires an amount of memory that is of the same order of magnitude as the index vector of the corresponding attribute. When creating a concatenated index (for more information, see above), there is even more overhead because an additional dictionary containing the concatenated values of all participating columns needs to be created as well. It is difficult to estimate the corresponding overhead, but it is usually notably higher than the summed-up size of the dictionaries of the participating columns. Therefore, concatenated indexes should be created with care.

The memory needed for inverted individual indexes includes the memory for inverted indexes on all indexed columns. This memory usage can be queried as a sum of M_CS_COLUMNS.MEMORY_SIZE_INDEX for all indexed columns.

The main advantage of the inverted individual index is its low memory footprint. The memory needed for inverted individual indexes is much smaller than the memory used for the internal index key column that is required for inverted value and inverted hash indexes. In addition, the data and log I/O overhead, table load time, CPU needed for the delta merge, and the overhead of DML update operations are also reduced because an internal column does not need to be maintained. Similarly, the DDL to create an inverted individual index is also much faster than for the other index types because the concatenated string does not need to be generated.

Incremental Maintenance

Whenever a DML operation is performed on the base table, the corresponding index structures need to be updated as well (for example, by inserting or deleting entries). These additional maintenance costs add to the costs on the base relation, and, depending on the number of indexes created, the number of attributes in the base table, and the number of attributes in the individual indexes, might even dominate the actual update time. Again, this requires that care is taken when creating additional indexes.