- Plain, normal index
- Unique index (is also a plain index)
- Primary key index (is also a unique index)
So the algorithm that optimizes indexes uses
- Some partial order on index specifications, that can be implemented as predicate that says "this index is covered by other"/"this index adds new query optimizations compared to other"
- Procedure that finds "supremum" of given index set
There is one further optimization that I have not implemeneted because improvements from it would be minimal for our project. This optimizaation would re-order fields to try to reduce number of indexes. For example if there is laready indexes [a, b, c, d] and [a, d] then one may leave [a, d, b, c] instead.
Note that if you you use indexes not only for query/filtering but also for ordering queries then reordering filelds in indexes can be not appropriate. In this case one should also consider "ascending"/"descending" property of indexes (and do not mix them).
No comments:
Post a Comment