[heart] Preetham Poluparthi reacted to your message: ________________________________ From: Mike Carey <dtab...@gmail.com> Sent: Tuesday, August 12, 2025 6:08:37 PM To: dev@asterixdb.apache.org <dev@asterixdb.apache.org> Subject: Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Excellent! On Tue, Aug 12, 2025 at 5:31 AM Preetham Poluparthi <preetha...@apache.org> wrote: > 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 > > > >
