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
