// check for an aggregation of the right form
if ((plan instanceof AggregatePlanNode) == false)
return plan;
assert(plan.getChildCount() == 1);
AggregatePlanNode aggplan = (AggregatePlanNode)plan;
// handle one single min() / max() now
// TODO: combination of [min(), max(), count()]
SortDirectionType sortDirection = SortDirectionType.INVALID;
if (aggplan.isTableMin()) {
sortDirection = SortDirectionType.ASC;
} else if (aggplan.isTableMax()) {
sortDirection = SortDirectionType.DESC;
} else {
return plan;
}
AbstractPlanNode child = plan.getChild(0);
AbstractExpression aggExpr = aggplan.getFirstAggregateExpression();
/**
* For generated SeqScan plan, look through all available indexes, if the first key
* of any one index matches the min()/max(), use that index with an inlined LIMIT
* node.
* For generated IndexScan plan, verify current index can be used for min()/max(), if
* so, appending an inlined LIMIT node to it.
* To avoid further handling to locate aggExpr for partitioned table in upper AGGREGATOR
* (coordinator), keep this old trivial AGGREGATOR node.
*/
// for a SEQSCAN, replace it with a INDEXSCAN node with an inline LIMIT plan node
if (child instanceof SeqScanPlanNode) {
// only replace SeqScan when no predicate
// should have other index access plan if any qualified index found for the predicate
if (((SeqScanPlanNode)child).getPredicate() != null) {
return plan;
}
if (((AbstractScanPlanNode)child).isSubQuery()) {
return plan;
}
// create an empty bindingExprs list, used for store (possible) bindings for adHoc query
ArrayList<AbstractExpression> bindings = new ArrayList<AbstractExpression>();
Index ret = findQualifiedIndex(((SeqScanPlanNode)child), aggExpr, bindings);
if (ret == null) {
return plan;
} else {
// 1. create one INDEXSCAN plan node with inlined LIMIT
// and replace the SEQSCAN node with it
// 2. we know which end row we want to fetch, so it's safe to
// specify sorting direction here
IndexScanPlanNode ispn = new IndexScanPlanNode((SeqScanPlanNode) child, aggplan, ret, sortDirection);
ispn.setBindings(bindings);
assert(ispn.getSearchKeyExpressions().size() == 0);
if (sortDirection == SortDirectionType.ASC) {
assert(aggplan.isTableMin());
ispn.setSkipNullPredicate(0);
}
LimitPlanNode lpn = new LimitPlanNode();
lpn.setLimit(1);
lpn.setOffset(0);
ispn.addInlinePlanNode(lpn);
// remove old SeqScan node and link the new generated IndexScan node
plan.clearChildren();
plan.addAndLinkChild(ispn);
return plan;
}
}
if ((child instanceof IndexScanPlanNode) == false) {
return plan;
}
// already have the IndexScanPlanNode
IndexScanPlanNode ispn = (IndexScanPlanNode)child;
// can do optimization only if it has no (post-)predicates
// except those (post-)predicates are artifact predicates
// we added for reverse scan purpose only
if (((IndexScanPlanNode)child).getPredicate() != null &&
!((IndexScanPlanNode)child).isPredicatesOptimizableForAggregate()) {
return plan;
}
// Guard against (possible future?) cases of indexable subquery.
if (((AbstractScanPlanNode)child).isSubQuery()) {
return plan;
}
// 1. Handle ALL equality filters case.
// In the IndexScanPlanNode:
// -- EQFilterExprs were put in searchkeyExpressions and endExpressions
// -- startCondition is only in searchKeyExpressions
// -- endCondition is only in endExpressions
// So, if the lookup type is EQ, then all filters must be equality; or if
// there are extra startCondition / endCondition, some filters are not equality
// 2. Handle equality filters and one other comparison operator (<, <=, >, >=), see comments below
if (ispn.getLookupType() != IndexLookupType.EQ &&
Math.abs(ispn.getSearchKeyExpressions().size() - ExpressionUtil.uncombine(ispn.getEndExpression()).size()) > 1) {
return plan;
}
// exprs will be used as filterExprs to check the index
// For forward scan, the initial value is endExprs and might be changed in different values in variant cases
// For reverse scan, the initial value is initialExprs which is the "old" endExprs
List<AbstractExpression> exprs;
int numOfSearchKeys = ispn.getSearchKeyExpressions().size();
if (ispn.getLookupType() == IndexLookupType.LT || ispn.getLookupType() == IndexLookupType.LTE) {
exprs = ExpressionUtil.uncombine(ispn.getInitialExpression());
numOfSearchKeys -= 1;
} else {
exprs = ExpressionUtil.uncombine(ispn.getEndExpression());
}
int numberOfExprs = exprs.size();
// If there is only 1 difference between searchkeyExprs and endExprs,
// 1. trivial filters can be discarded, 2 possibilities:
// a. SELECT MIN(X) FROM T WHERE [other prefix filters] X < / <= ?
// <=> SELECT MIN(X) FROM T WHERE [other prefix filters] && the X < / <= ? filter
// b. SELECT MAX(X) FROM T WHERE X > / >= ?
// <=> SELECT MAX(X) FROM T with post-filter
// 2. filter should act as equality filter, 2 possibilities
// SELECT MIN(X) FROM T WHERE [other prefix filters] X > / >= ?
// SELECT MAX(X) FROM T WHERE [other prefix filters] X < / <= ?
// check if there is other filters for SELECT MAX(X) FROM T WHERE [other prefix filter AND ] X > / >= ?
// but we should allow SELECT MAX(X) FROM T WHERE X = ?
if (sortDirection == SortDirectionType.DESC && ispn.getSortDirection() == SortDirectionType.INVALID) {
if (numberOfExprs > 1 ||
(numberOfExprs == 1 && aggExpr.bindingToIndexedExpression(exprs.get(0).getLeft()) == null)) {
return plan;
}
}
// have an upper bound: # of endingExpr is more than # of searchExpr
if (numberOfExprs > numOfSearchKeys) {
// check last ending condition, see whether it is
// SELECT MIN(X) FROM T WHERE [other prefix filters] X < / <= ? or
// other filters will be checked later
AbstractExpression lastEndExpr = exprs.get(numberOfExprs - 1);
if ((lastEndExpr.getExpressionType() == ExpressionType.COMPARE_LESSTHAN ||
lastEndExpr.getExpressionType() == ExpressionType.COMPARE_LESSTHANOREQUALTO)
&& lastEndExpr.getLeft().equals(aggExpr)) {
exprs.remove(lastEndExpr);
}
}
// do not aggressively evaluate all indexes, just examine the index currently in use;
// because for all qualified indexes, one access plan must have been generated already,
// and we can take advantage of that
if (!checkIndex(ispn.getCatalogIndex(), aggExpr, exprs, ispn.getBindings(), ispn.getTargetTableAlias())) {
return plan;
} else {
// we know which end we want to fetch, set the sort direction
ispn.setSortDirection(sortDirection);
// for SELECT MIN(X) FROM T WHERE [prefix filters] = ?
if (numberOfExprs == numOfSearchKeys && sortDirection == SortDirectionType.ASC) {
if (ispn.getLookupType() == IndexLookupType.GTE) {
assert(aggplan.isTableMin());
ispn.setSkipNullPredicate(numOfSearchKeys);
}
}
// for SELECT MIN(X) FROM T WHERE [...] X < / <= ?
// reset the IndexLookupType, remove "added" searchKey, add back to endExpression, and clear "added" predicate
if (sortDirection == SortDirectionType.ASC &&
(ispn.getLookupType() == IndexLookupType.LT || ispn.getLookupType() == IndexLookupType.LTE)){
ispn.setLookupType(IndexLookupType.GTE);
ispn.removeLastSearchKey();
ispn.addEndExpression(ExpressionUtil.uncombine(ispn.getInitialExpression()).get(numberOfExprs - 1));
ispn.resetPredicate();
}
// add an inline LIMIT plan node to this index scan plan node
LimitPlanNode lpn = new LimitPlanNode();
lpn.setLimit(1);
lpn.setOffset(0);
ispn.addInlinePlanNode(lpn);
// ENG-1565: For SELECT MAX(X) FROM T WHERE X > / >= ?, turn the pre-filter to post filter.
// There are two choices:
// AggregatePlanNode AggregatePlanNode
// |__ IndexScanPlanNode => |__FilterPlanNode
// |__IndexScanPlanNode with no filter
// |__LimitPlanNode
// OR
// AggregatePlanNode AggregatePlanNode with filter
// |__ IndexScanPlanNode => |__IndexScanPlanNode with no filter
// |__LimitPlanNode
// For now, we take the second approach.
if (sortDirection == SortDirectionType.DESC &&
!ispn.getSearchKeyExpressions().isEmpty() &&
exprs.isEmpty() &&
ExpressionUtil.uncombine(ispn.getInitialExpression()).isEmpty()) {
AbstractExpression newPredicate = new ComparisonExpression();
if (ispn.getLookupType() == IndexLookupType.GT)
newPredicate.setExpressionType(ExpressionType.COMPARE_GREATERTHAN);
if (ispn.getLookupType() == IndexLookupType.GTE)
newPredicate.setExpressionType(ExpressionType.COMPARE_GREATERTHANOREQUALTO);
newPredicate.setRight(ispn.getSearchKeyExpressions().get(0));
newPredicate.setLeft(aggExpr);
newPredicate.setValueType(aggExpr.getValueType());
ispn.clearSearchKeyExpression();
aggplan.setPrePredicate(newPredicate);
}
return plan;
}
}