I should have clarified that point better. What I meant was: The Cost-Based Optimiser selects indexes by first sorting all candidate indexes in ascending order of selectivity, choosing the most selective one as the first index, and then adding additional indexes only if they reduce the overall cost. It does not consider all possible index combinations, so certain index pairings that might be cheaper are never evaluated. For composite indexes with identical selectivity, the optimiser picks one arbitrarily. I’ve included examples in the updated APE to make this clearer.
Cheers, Preetham On 2025/08/11 22:26:43 Mike Carey wrote: > One clarification question (and suggestion): > > The APE says "If multiple indexes have equal selectivity, the CBO > randomly chooses one, and recommends using that one." > > This makes it kinda sound like, in the end, for a given query, only one > index will be chosen, which AFAIK is not the case. My recollection is > that CBO will potentially use multiple indexes for a query, e.g., maybe > a few for single-collection filter predicates and maybe another one for > accessing the inner collection in an index NL joint. It might be > helpful to clarify this and provide a couple of short but richer > examples to illustrate that. (E.g., one where there are two predicates > on a join's outer collection and a join predicate - so it would use 0, > 1, or 2 indexes on the outer and maybe 1 for the join if the filtered > outer intermediate result is small ---- and then one where the > intermediate outer result is big, in which case CBO would choose a hash > join and you'd be able to recommend single-collection predicates for > indexing as well on the inner side.) > > Cheers, > > Mike > > On 8/11/25 7:06 AM, Preetham Poluparthi wrote: > > Hi all, > > I’d like to initiate a discussion on adding the Index Advisor feature. > > Details: The goal of this feature is to suggest optimal index > > recommendations for queries, leveraging the cost-based methods we already > > have in place.The APE has the explanation of virtual indexes, which are > > placeholder indexes used during cost-based analysis, and describes the > > enumeration logic that explores possible index configurations to determine > > the most beneficial recommendations. > > > > APE: > > https://cwiki.apache.org/confluence/display/ASTERIXDB/APE+26%3A+Index+Advisor > > > > Thanks, > > Preetham Poluparthi > >
