+1 to this APE. It's a great use of CBO to make life much easier for normal users.
On Tue, Aug 12, 2025 at 11:55 AM Preetham Poluparthi <preetham.polupar...@couchbase.com.invalid> wrote: > [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://urldefense.com/v3/__https://cwiki.apache.org/confluence/display/ASTERIXDB/APE*26*3A*Index*Advisor__;KyUrKw!!CzAuKJ42GuquVTTmVmPViYEvSg!K_VUmHCAt6NiCsl5zF9vYkFJ9E6iBEiEyCj9nIq049naYiJrs47fWVaUDkgbONTkPNG9qa5s6tWL8bRAtHJV7Y-Lr96VHk-bS88$ > > > > > > > > Thanks, > > > > Preetham Poluparthi > > > > > > >
