+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
<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
