Examples with Index org.voltdb.catalog.Index used on opensource projects

Example 1 with Index

use of org.voltdb.catalog.Index in project voltdb by VoltDB.

the class ReportMaker method genrateStatementRow.

static String genrateStatementRow(CatalogMap<Table> tables, Procedure procedure, Statement statement) {
    // get the proc annotation which should exist or be created just before this is called
    ProcedureAnnotation procAnnotation = (ProcedureAnnotation) procedure.getAnnotation();
    assert (procAnnotation != null);
    StringBuilder sb = new StringBuilder();
    sb.append("        <tr class='primaryrow2'>");
    // name column
    String anchor = (procedure.getTypeName() + "-" + statement.getTypeName()).toLowerCase();
    sb.append("<td style='white-space: nowrap'><i id='p-" + anchor + "--icon' class='icon-chevron-right'></i> <a href='#' id='p-");
    sb.append(anchor).append("' class='togglex'>");
    sb.append(statement.getTypeName());
    sb.append("</a></td>");
    // sql column
    sb.append("<td><tt>");
    sb.append(escapeHtml4(statement.getSqltext()));
    sb.append("</td></tt>");
    // params column
    sb.append("<td>");
    List<StmtParameter> params = CatalogUtil.getSortedCatalogItems(statement.getParameters(), "index");
    List<String> paramTypes = new ArrayList<String>();
    for (StmtParameter param : params) {
        paramTypes.add(VoltType.get((byte) param.getJavatype()).name());
    }
    if (paramTypes.size() == 0) {
        sb.append("<i>None</i>");
    }
    sb.append(StringUtils.join(paramTypes, ", "));
    sb.append("</td>");
    // r/w column
    sb.append("<td>");
    if (statement.getReadonly()) {
        tag(sb, "success", "Read");
    } else {
        tag(sb, "warning", "Write");
    }
    sb.append("</td>");
    // attributes
    sb.append("<td>");
    if (!statement.getIscontentdeterministic() || !statement.getIsorderdeterministic()) {
        tag(sb, "inverse", "Determinism");
    }
    if (statement.getSeqscancount() > 0) {
        tag(sb, "important", "Scans");
    }
    sb.append("</td>");
    sb.append("</tr>\n");
    // BUILD THE DROPDOWN FOR THE PLAN/DETAIL TABLE
    sb.append("<tr class='dropdown2'><td colspan='5' id='p-" + procedure.getTypeName().toLowerCase() + "-" + statement.getTypeName().toLowerCase() + "--dropdown'>\n");
    sb.append("<div class='well well-small'><h4>Explain Plan:</h4>\n");
    String plan = escapeHtml4(Encoder.hexDecodeToString(statement.getExplainplan()));
    plan = plan.replace("\n", "<br/>");
    plan = plan.replace(" ", "&nbsp;");
    for (String tableName : statement.getTablesread().split(",")) {
        if (tableName.length() == 0) {
            continue;
        }
        Table t = tables.get(tableName);
        assert (t != null);
        TableAnnotation ta = (TableAnnotation) t.getAnnotation();
        assert (ta != null);
        ta.statementsThatReadThis.add(statement);
        ta.proceduresThatReadThis.add(procedure);
        procAnnotation.tablesRead.add(t);
        String uname = tableName.toUpperCase();
        String link = "\"<a href='#s-" + tableName + "'>" + uname + "</a>\"";
        plan = plan.replace("&quot;" + uname + "&quot;", link);
    }
    for (String tableName : statement.getTablesupdated().split(",")) {
        if (tableName.length() == 0) {
            continue;
        }
        Table t = tables.get(tableName);
        assert (t != null);
        TableAnnotation ta = (TableAnnotation) t.getAnnotation();
        assert (ta != null);
        ta.statementsThatUpdateThis.add(statement);
        ta.proceduresThatUpdateThis.add(procedure);
        procAnnotation.tablesUpdated.add(t);
        String uname = tableName.toUpperCase();
        String link = "\"<a href='#s-" + tableName + "'>" + uname + "</a>\"";
        plan = plan.replace("&quot;" + uname + "&quot;", link);
    }
    for (String tableDotIndexPair : statement.getIndexesused().split(",")) {
        if (tableDotIndexPair.length() == 0) {
            continue;
        }
        String[] parts = tableDotIndexPair.split("\\.", 2);
        assert (parts.length == 2);
        if (parts.length != 2) {
            continue;
        }
        String tableName = parts[0];
        String indexName = parts[1];
        Table t = tables.get(tableName);
        assert (t != null);
        Index i = t.getIndexes().get(indexName);
        assert (i != null);
        IndexAnnotation ia = (IndexAnnotation) i.getAnnotation();
        if (ia == null) {
            ia = new IndexAnnotation();
            i.setAnnotation(ia);
        }
        ia.proceduresThatUseThis.add(procedure);
        procAnnotation.indexesUsed.add(i);
        String uindexName = indexName.toUpperCase();
        String link = "\"<a href='#s-" + tableName + "-" + indexName + "'>" + uindexName + "</a>\"";
        plan = plan.replace("&quot;" + uindexName + "&quot;", link);
    }
    sb.append("<tt>").append(plan).append("</tt>");
    sb.append("</div>\n");
    sb.append("</td></tr>\n");
    return sb.toString();
}

Example 2 with Index

use of org.voltdb.catalog.Index in project voltdb by VoltDB.

the class ReportMaker method generateSchemaRow.

static String generateSchemaRow(Table table, boolean isExportTable) {
    StringBuilder sb = new StringBuilder();
    sb.append("<tr class='primaryrow'>");
    // column 1: table name
    String anchor = table.getTypeName().toLowerCase();
    sb.append("<td style='white-space: nowrap;'><i id='s-" + anchor + "--icon' class='icon-chevron-right'></i> <a href='#' id='s-");
    sb.append(anchor).append("' class='togglex'>");
    sb.append(table.getTypeName());
    sb.append("</a></td>");
    // column 2: type
    sb.append("<td>");
    if (table.getMaterializer() != null) {
        tag(sb, "info", "Materialized View");
    } else {
        if (isExportTable) {
            tag(sb, "inverse", "Export Streams");
        } else {
            tag(sb, null, "Table");
        }
    }
    sb.append("</td>");
    // column 3: partitioning
    sb.append("<td style='whitespace: nowrap;'>");
    if (table.getIsreplicated()) {
        tag(sb, "warning", "Replicated");
    } else {
        tag(sb, "success", "Partitioned");
        Column partitionCol = table.getPartitioncolumn();
        if (partitionCol != null) {
            sb.append("<small> on " + partitionCol.getName() + "</small>");
        } else {
            Table matSrc = table.getMaterializer();
            if (matSrc != null) {
                sb.append("<small> with " + matSrc.getTypeName() + "</small>");
            }
        }
    }
    sb.append("</td>");
    // column 4: column count
    sb.append("<td>");
    sb.append(table.getColumns().size());
    sb.append("</td>");
    // column 5: index count
    sb.append("<td>");
    sb.append(table.getIndexes().size());
    // computing unused indexes
    int unusedIndexes = 0;
    for (Index index : table.getIndexes()) {
        IndexAnnotation indexAnnotation = (IndexAnnotation) index.getAnnotation();
        if (indexAnnotation == null) {
            unusedIndexes++;
        }
    }
    if (unusedIndexes != 0) {
        sb.append(" (" + unusedIndexes + " unused)");
    }
    sb.append("</td>");
    // column 6: has pkey
    sb.append("<td>");
    boolean found = false;
    for (Constraint constraint : table.getConstraints()) {
        if (ConstraintType.get(constraint.getType()) == ConstraintType.PRIMARY_KEY) {
            found = true;
            break;
        }
    }
    if (found) {
        tag(sb, "info", "Has-PKey");
    } else {
        tag(sb, null, "No-PKey");
    }
    sb.append("</td>");
    // column 6: has tuple limit
    sb.append("<td>");
    if (table.getTuplelimit() != Integer.MAX_VALUE) {
        tag(sb, "info", String.valueOf(table.getTuplelimit()));
        if (CatalogUtil.getLimitPartitionRowsDeleteStmt(table) != null) {
            sb.append("<small>enforced by DELETE statement</small>");
        }
    } else {
        tag(sb, null, "No-limit");
    }
    sb.append("</td>");
    sb.append("</tr>\n");
    // BUILD THE DROPDOWN FOR THE DDL / INDEXES DETAIL
    sb.append("<tr class='tablesorter-childRow'><td class='invert' colspan='7' id='s-" + table.getTypeName().toLowerCase() + "--dropdown'>\n");
    TableAnnotation annotation = (TableAnnotation) table.getAnnotation();
    if (annotation != null) {
        // output the DDL
        if (annotation.ddl == null) {
            sb.append("<p>MISSING DDL</p>\n");
        } else {
            String ddl = escapeHtml4(annotation.ddl);
            sb.append("<p><pre>" + ddl + "</pre></p>\n");
        }
        // make sure procs appear in only one category
        annotation.proceduresThatReadThis.removeAll(annotation.proceduresThatUpdateThis);
        if (annotation.proceduresThatReadThis.size() > 0) {
            sb.append("<p>Read-only by procedures: ");
            List<String> procs = new ArrayList<String>();
            for (Procedure proc : annotation.proceduresThatReadThis) {
                procs.add("<a href='#p-" + proc.getTypeName() + "'>" + proc.getTypeName() + "</a>");
            }
            sb.append(StringUtils.join(procs, ", "));
            sb.append("</p>");
        }
        if (annotation.proceduresThatUpdateThis.size() > 0) {
            sb.append("<p>Read/Write by procedures: ");
            List<String> procs = new ArrayList<String>();
            for (Procedure proc : annotation.proceduresThatUpdateThis) {
                procs.add("<a href='#p-" + proc.getTypeName() + "'>" + proc.getTypeName() + "</a>");
            }
            sb.append(StringUtils.join(procs, ", "));
            sb.append("</p>");
        }
    }
    // LIMIT PARTITION ROW statement may also use the index in this table, prepare the information for report
    if (!table.getTuplelimitdeletestmt().isEmpty()) {
        assert (table.getTuplelimitdeletestmt().size() == 1);
        Statement stmt = table.getTuplelimitdeletestmt().iterator().next();
        for (String tableDotIndexPair : stmt.getIndexesused().split(",")) {
            if (tableDotIndexPair.length() == 0) {
                continue;
            }
            String[] parts = tableDotIndexPair.split("\\.", 2);
            assert (parts.length == 2);
            if (parts.length != 2) {
                continue;
            }
            String tableName = parts[0];
            String indexName = parts[1];
            if (!table.getTypeName().equals(tableName)) {
                continue;
            }
            Index i = table.getIndexes().get(indexName);
            assert (i != null);
            IndexAnnotation ia = (IndexAnnotation) i.getAnnotation();
            if (ia == null) {
                ia = new IndexAnnotation();
                i.setAnnotation(ia);
            }
            ia.statementsThatUseThis.add(stmt);
        }
    }
    if (table.getIndexes().size() > 0) {
        sb.append(generateIndexesTable(table));
    } else {
        sb.append("<p>No indexes defined on table.</p>\n");
    }
    // Generate explainview report.
    if (table.getMaterializer() != null) {
        sb.append(generateExplainViewTable(table));
    }
    sb.append("</td></tr>\n");
    return sb.toString();
}

Example 3 with Index

use of org.voltdb.catalog.Index in project voltdb by VoltDB.

the class ReplaceWithIndexCounter method recursivelyApply.

@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode plan) {
    assert (plan != null);
    // depth first:
    //     find AggregatePlanNode with exactly one child
    //     where that child is an AbstractScanPlanNode.
    //     Replace any qualifying AggregatePlanNode / AbstractScanPlanNode pair
    //     with an IndexCountPlanNode or TableCountPlanNode
    ArrayList<AbstractPlanNode> children = new ArrayList<AbstractPlanNode>();
    for (int i = 0; i < plan.getChildCount(); i++) children.add(plan.getChild(i));
    for (AbstractPlanNode child : children) {
        // TODO this will break when children feed multiple parents
        AbstractPlanNode newChild = recursivelyApply(child);
        // Do a graft into the (parent) plan only if a replacement for a child was found.
        if (newChild == child) {
            continue;
        }
        boolean replaced = plan.replaceChild(child, newChild);
        assert (true == replaced);
    }
    // check for an aggregation of the right form
    if ((plan instanceof AggregatePlanNode) == false)
        return plan;
    assert (plan.getChildCount() == 1);
    AggregatePlanNode aggplan = (AggregatePlanNode) plan;
    // ENG-6131 fixed here.
    if (!(aggplan.isTableCountStar() || aggplan.isTableNonDistinctCountConstant() || aggplan.isTableCountNonDistinctNullableColumn())) {
        return plan;
    }
    AbstractPlanNode child = plan.getChild(0);
    // A table count can replace a seq scan only if it has no predicates.
    if (child instanceof SeqScanPlanNode) {
        if (((SeqScanPlanNode) child).getPredicate() != null) {
            return plan;
        }
        AbstractExpression postPredicate = aggplan.getPostPredicate();
        if (postPredicate != null) {
            List<AbstractExpression> aggList = postPredicate.findAllAggregateSubexpressions();
            boolean allCountStar = true;
            for (AbstractExpression expr : aggList) {
                if (expr.getExpressionType() != ExpressionType.AGGREGATE_COUNT_STAR) {
                    allCountStar = false;
                    break;
                }
            }
            if (allCountStar) {
                return plan;
            }
        }
        if (hasInlineLimit(aggplan)) {
            // table count EE executor does not handle inline limit stuff
            return plan;
        }
        return new TableCountPlanNode((AbstractScanPlanNode) child, aggplan);
    }
    // Otherwise, optimized counts only replace particular cases of index scan.
    if ((child instanceof IndexScanPlanNode) == false)
        return plan;
    IndexScanPlanNode isp = (IndexScanPlanNode) child;
    // Guard against (possible future?) cases of indexable subquery.
    if (((IndexScanPlanNode) child).isSubQuery()) {
        return plan;
    }
    // except those (post-)predicates are artifact predicates we added for reverse scan purpose only
    if (isp.getPredicate() != null && !isp.isPredicatesOptimizableForAggregate()) {
        return plan;
    }
    // With no start or end keys, there's not much a counting index can do.
    if (isp.getEndExpression() == null && isp.getSearchKeyExpressions().size() == 0) {
        if (hasInlineLimit(aggplan)) {
            return plan;
        }
        return new TableCountPlanNode(isp, aggplan);
    }
    // check for the index's support for counting
    Index idx = isp.getCatalogIndex();
    if (!idx.getCountable()) {
        return plan;
    }
    // The core idea is that counting index needs to know the start key and end key to
    // jump to to get counts instead of actually doing any scanning.
    // Options to be determined are:
    // - whether each of the start/end keys is missing, partial (a prefix of a compund key), or complete,
    // - whether the count should include or exclude entries exactly matching each of the start/end keys.
    // Not all combinations of these options are supported;
    // unsupportable cases cause the factory method to return null.
    IndexCountPlanNode countingPlan = IndexCountPlanNode.createOrNull(isp, aggplan);
    if (countingPlan == null) {
        return plan;
    }
    return countingPlan;
}

Example 4 with Index

use of org.voltdb.catalog.Index in project voltdb by VoltDB.

the class ReplaceWithIndexLimit method recursivelyApply.

@Override
protected AbstractPlanNode recursivelyApply(AbstractPlanNode plan) {
    assert (plan != null);
    // depth first:
    //     Find AggregatePlanNode with exactly one child
    //     where that child is an AbstractScanPlanNode.
    //     Replace qualifying SeqScanPlanNode with an
    //     IndexScanPlanNode with an inlined LimitPlanNode;
    //     or appending the LimitPlanNode to the existing
    //     qualified IndexScanPlanNode.
    ArrayList<AbstractPlanNode> children = new ArrayList<AbstractPlanNode>();
    for (int i = 0; i < plan.getChildCount(); i++) children.add(plan.getChild(i));
    for (AbstractPlanNode child : children) {
        // TODO this will break when children feed multiple parents
        AbstractPlanNode newChild = recursivelyApply(child);
        // Do a graft into the (parent) plan only if a replacement for a child was found.
        if (newChild == child) {
            continue;
        }
        child.removeFromGraph();
        plan.addAndLinkChild(newChild);
    }
    // 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 a SEQSCAN, replace it with a INDEXSCAN node with an inline LIMIT plan node
    if (child instanceof SeqScanPlanNode) {
        // 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;
    // 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;
    }
    // 2. Handle equality filters and one other comparison operator (<, <=, >, >=), see comments below
    if (ispn.getLookupType() != IndexLookupType.EQ && Math.abs(ispn.getSearchKeyExpressions().size() - ExpressionUtil.uncombinePredicate(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.uncombinePredicate(ispn.getInitialExpression());
        numOfSearchKeys -= 1;
    } else {
        exprs = ExpressionUtil.uncombinePredicate(ispn.getEndExpression());
    }
    int numberOfExprs = exprs.size();
    /* Retrieve the index expressions from the target index. (ENG-8819, Ethan)
         * This is because we found that for the following two queries:
         *     #1: explain select max(c2/2) from t where c1=1 and c2/2<=3;
         *     #2: explain select max(c2/2) from t where c1=1 and c2/2<=?;
         * We can get an inline limit 1 for #2 but not for #1. This is because all constants in #1 got parameterized.
         * The result is that the query cannot pass the bindingToIndexedExpression() tests below
         * because we lost all the constant value expressions (cannot attempt to bind a pve to a pve!).
         * Those constant values expressions can only be accessed from the idnex.
         * We will not add those bindings to the ispn.getBindings() here because they will be added anyway in checkIndex().
         * PS: For this case (i.e. index on expressions), checkIndex() will call checkExpressionIndex(),
         * where bindings will be added.
         */
    Index indexToUse = ispn.getCatalogIndex();
    String tableAlias = ispn.getTargetTableAlias();
    List<AbstractExpression> indexedExprs = null;
    if (!indexToUse.getExpressionsjson().isEmpty()) {
        StmtTableScan tableScan = m_parsedStmt.getStmtTableScanByAlias(tableAlias);
        try {
            indexedExprs = AbstractExpression.fromJSONArrayString(indexToUse.getExpressionsjson(), tableScan);
        } catch (JSONException e) {
            e.printStackTrace();
            assert (false);
            return plan;
        }
    }
    /* 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 = ?

         * This is for queries having MAX() but no ORDER BY. (ENG-8819, Ethan)
         * sortDirection == DESC if max, ASC if min. ispn.getSortDirection() == INVALID if no ORDER BY. */
    if (sortDirection == SortDirectionType.DESC && ispn.getSortDirection() == SortDirectionType.INVALID) {
        /* numberOfExprs = exprs.size(), exprs are initial expressions for reversed index scans (lookupType LT, LTE),
             * are end expressions for forward index scans (lookupType GT, GTE, EQ).
             * Note, lookupType doesn't decide the scan direction for sure. MIN(X) where X < ? is still a forward scan.
             * X < ? will be a post filter for the scan rather than an initial expression. */
        if (numberOfExprs == 1) {
            // e.g.: explain select max(c2/2) from t where c2/2<=3;
            // In this case, as long as the where condition (exprs.get(0)) matches the aggregation argument, continue.
            AbstractExpression exprToBind = indexedExprs == null ? exprs.get(0).getLeft() : indexedExprs.get(0);
            if (aggExpr.bindingToIndexedExpression(exprToBind) == null) {
                return plan;
            }
        } else if (numberOfExprs > 1) {
            // ENG-4016: Optimization for query SELECT MAX(X) FROM T WHERE [other prefix filters] X < / <= ?
            // Just keep trying, don't return early.
            boolean earlyReturn = true;
            for (int i = 0; i < numberOfExprs; ++i) {
                AbstractExpression expr = exprs.get(i);
                AbstractExpression indexedExpr = indexedExprs == null ? expr.getLeft() : indexedExprs.get(i);
                if (aggExpr.bindingToIndexedExpression(indexedExpr) != null && (expr.getExpressionType() == ExpressionType.COMPARE_LESSTHANOREQUALTO || expr.getExpressionType() == ExpressionType.COMPARE_LESSTHAN || expr.getExpressionType() == ExpressionType.COMPARE_EQUAL)) {
                    earlyReturn = false;
                    break;
                }
            }
            if (earlyReturn) {
                return plan;
            }
        }
    }
    // have an upper bound: # of endingExpr is more than # of searchExpr
    if (numberOfExprs > numOfSearchKeys) {
        AbstractExpression lastEndExpr = exprs.get(numberOfExprs - 1);
        // 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 exprToBind = indexedExprs == null ? lastEndExpr.getLeft() : indexedExprs.get(numberOfExprs - 1);
        if ((lastEndExpr.getExpressionType() == ExpressionType.COMPARE_LESSTHAN || lastEndExpr.getExpressionType() == ExpressionType.COMPARE_LESSTHANOREQUALTO) && aggExpr.bindingToIndexedExpression(exprToBind) != null) {
            exprs.remove(lastEndExpr);
        }
    }
    // and we can take advantage of that
    if (checkIndex(ispn.getCatalogIndex(), aggExpr, exprs, ispn.getBindings(), tableAlias)) {
        // 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);
            }
        }
        // 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.uncombinePredicate(ispn.getInitialExpression()).get(numberOfExprs - 1));
            ispn.setSkipNullPredicate(numOfSearchKeys);
            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);
        //                                              |__LimitPlanNode
        if (sortDirection == SortDirectionType.DESC && !ispn.getSearchKeyExpressions().isEmpty() && exprs.isEmpty() && ExpressionUtil.uncombinePredicate(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;
}

Example 5 with Index

use of org.voltdb.catalog.Index in project voltdb by VoltDB.

the class SubPlanAssembler method getRelevantAccessPathsForTable.

/**
     * Generate all possible access paths for given sets of join and filter
     * expressions for a table.
     * The list includes the naive (scan) pass and possible index scans
     *
     * @param table Table to generate access path for
     * @param joinExprs join expressions this table is part of
     * @param filterExprs filter expressions this table is part of
     * @param postExprs post expressions this table is part of
     * @return List of valid access paths
     */
protected ArrayList<AccessPath> getRelevantAccessPathsForTable(StmtTableScan tableScan, List<AbstractExpression> joinExprs, List<AbstractExpression> filterExprs, List<AbstractExpression> postExprs) {
    ArrayList<AccessPath> paths = new ArrayList<>();
    List<AbstractExpression> allJoinExprs = new ArrayList<>();
    List<AbstractExpression> allExprs = new ArrayList<>();
    // add the empty seq-scan access path
    if (joinExprs != null) {
        allExprs.addAll(joinExprs);
        allJoinExprs.addAll(joinExprs);
    }
    if (postExprs != null) {
        allJoinExprs.addAll(postExprs);
    }
    if (filterExprs != null) {
        allExprs.addAll(filterExprs);
    }
    AccessPath naivePath = getRelevantNaivePath(allJoinExprs, filterExprs);
    paths.add(naivePath);
    Collection<Index> indexes = tableScan.getIndexes();
    for (Index index : indexes) {
        AccessPath path = getRelevantAccessPathForIndex(tableScan, allExprs, index);
        // Process the index WHERE clause into a list of anded
        // sub-expressions and process each sub-expression, searching the
        // query (or matview) WHERE clause for an expression to cover each
        // of them. Coverage can be in the form of an identical filter or
        // a more restrictive filter. Specifically, comparison filters like
        // "X > 1" cover the index predicate filter "X is not null" but are
        // not an exact match. As such, they are not completely optimized
        // out by use of the partial index.
        // The partial index's WHERE sub-expressions must be covered to
        // allow ANY use of the index.
        // Of the covering sub-expressions (from the query or view),
        // those that also EXACTLY match their covered index sub-expression
        // are tracked in exactMatchCoveringExprs so that they
        // can be eliminated from the post-filter expressions.
        List<AbstractExpression> exactMatchCoveringExprs = null;
        boolean hasCoveredPredicate = false;
        String predicatejson = index.getPredicatejson();
        if (path == null) {
            if (predicatejson.isEmpty()) {
                // Skip the uselessly irrelevant whole-table index.
                continue;
            }
            exactMatchCoveringExprs = new ArrayList<>();
            hasCoveredPredicate = isPartialIndexPredicateCovered(tableScan, allExprs, predicatejson, exactMatchCoveringExprs);
            if (!hasCoveredPredicate) {
                // Skip the index with the inapplicable predicate.
                continue;
            }
            // The partial index with a covered predicate can be used
            // solely to eliminate a post-filter or even just to reduce the
            // number of post-filtered tuples,
            // even though its indexed columns are irrelevant -- so
            // getRelevantAccessPathForIndex did not return a valid path.
            // Override the path for a forward scan of the entire partial
            // index.  The irrelevant keys of the index are ignored.
            path = getRelevantNaivePath(allJoinExprs, filterExprs);
            path.index = index;
            path.lookupType = IndexLookupType.GTE;
        } else {
            assert (path.index != null);
            assert (path.index == index);
            // its predicate is not applicable.
            if (!predicatejson.isEmpty()) {
                exactMatchCoveringExprs = new ArrayList<>();
                hasCoveredPredicate = isPartialIndexPredicateCovered(tableScan, allExprs, predicatejson, exactMatchCoveringExprs);
                if (!hasCoveredPredicate) {
                    // Skip the index with the inapplicable predicate.
                    continue;
                }
            }
        }
        assert (path != null);
        if (hasCoveredPredicate) {
            assert (exactMatchCoveringExprs != null);
            filterPostPredicateForPartialIndex(path, exactMatchCoveringExprs);
        }
        if (postExprs != null) {
            path.joinExprs.addAll(postExprs);
        }
        paths.add(path);
    }
    return paths;
}