You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@asterixdb.apache.org by Preetham Poluparthi <pr...@apache.org> on 2025/08/11 11:06:19 UTC
[DISCUSS][APE] Proposal to Add Index Advisor Feature
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
Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Ali Alsuliman <al...@apache.org>.
+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
> > <pr...@couchbase.com.invalid> wrote:
> >
> >> [heart] Preetham Poluparthi reacted to your message:
> >> ________________________________
> >> From: Mike Carey<dt...@gmail.com>
> >> Sent: Tuesday, August 12, 2025 6:08:37 PM
> >> To:dev@asterixdb.apache.org <de...@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 <preetham02@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
> >>>>>
Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Mike Carey <dt...@gmail.com>.
+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
> <pr...@couchbase.com.invalid> wrote:
>
>> [heart] Preetham Poluparthi reacted to your message:
>> ________________________________
>> From: Mike Carey<dt...@gmail.com>
>> Sent: Tuesday, August 12, 2025 6:08:37 PM
>> To:dev@asterixdb.apache.org <de...@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 <preetham02@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
>>>>>
Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Ian Maxon <im...@uci.edu>.
+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
<pr...@couchbase.com.invalid> wrote:
> [heart] Preetham Poluparthi reacted to your message:
> ________________________________
> From: Mike Carey <dt...@gmail.com>
> Sent: Tuesday, August 12, 2025 6:08:37 PM
> To: dev@asterixdb.apache.org <de...@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 <preetham02@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
> > > >
> >
>
RE: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Preetham Poluparthi <pr...@couchbase.com.INVALID>.
[heart] Preetham Poluparthi reacted to your message:
________________________________
From: Mike Carey <dt...@gmail.com>
Sent: Tuesday, August 12, 2025 6:08:37 PM
To: dev@asterixdb.apache.org <de...@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 <pr...@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
> > >
>
Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Mike Carey <dt...@gmail.com>.
Excellent!
On Tue, Aug 12, 2025 at 5:31 AM Preetham Poluparthi <pr...@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
> > >
>
Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Preetham Poluparthi <pr...@apache.org>.
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
> >
Re: [DISCUSS][APE] Proposal to Add Index Advisor Feature
Posted by Mike Carey <dt...@gmail.com>.
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
>