+1
On 2025/08/15 17:02:41 Mike Carey wrote: > +1 from me too (in case that wasn't clear :-)) > > On 8/15/25 9:24 AM, Ian Maxon wrote: > > +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 > > <[email protected]> wrote: > > > >> [heart] Preetham Poluparthi reacted to your message: > >> ________________________________ > >> From: Mike Carey<[email protected]> > >> Sent: Tuesday, August 12, 2025 6:08:37 PM > >> To:[email protected] <[email protected]> > >> Subject: Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature > >> > >> Excellent! > >> > >> On Tue, Aug 12, 2025 at 5:31 AM Preetham Poluparthi <[email protected] > >> 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 > >>>>>
