Page History

This optimization tries to intersect multiple secondary indexes if the select conditions introduce them. Previously, as in the IntroduceSelectAccessMethodRule, we would pick the first index to contribute to the access path when there were multiple indexes available. Due to the lack of statistical information, the first one may not be the best choice. Moreover, even we chose the index of the lowest selectivity, it still may not be the best solution. Because we can further reduce the selectivity by intersecting it with the other secondary indexes. Having intersection into the plan will avoid the worst path. Furthermore, if we have statistical information later we can have another option to take into considerationkeep improving the decision by whether to introduce the intersection or not.

Optimization Rule

The logical changes are in the IntroduceSelectAccessMethodRule. After we analyzed the interesting functions and indexes, we pair them up as one to one mapping. (E.g. BTreeAccessMethod -> BTreeIndex On Salary.).

If the primary index appears in the mapping, we will simply use it as the access path. Because usually the primary index lookup is very rare, if it indeed happens, then it should have a very high chance to be a high selectivity path. Plus, as a clustered index, primary index search is the fastest one. 

If multiple secondary indexes are selected, then we will let each of them to contribute a secondary index to primary index path. A new interface "createSecondaryToPrimaryPlan()" is added to the IAccessMethod for this purpose. (The implementation is required to be functional. Otherwise, different access methods may introduce the conflict states.) Then we use an Intersection logical operator as a join point to intersect the primary keys coming from different secondary index path. 

The following example shows we intersect the BTree,RTree and NgramInvertedIndex on primary key before goes to primary index lookup.

...

The experiment is slow. Stay tuned. 

Review Patch: 

https://asterix-gerrit.ics.uci.edu/#/c/577  and  https://asterix-gerrit.ics.uci.edu/#/c/578

...