Examples with OptimizerPlan org.apache.derby.iapi.sql.compile.OptimizerPlan used on opensource projects

Example 1 with OptimizerPlan

use of org.apache.derby.iapi.sql.compile.OptimizerPlan in project derby by apache.

the class XMLOptTrace method formatPlanSummary.

/**
 * <p>
 * Produce a string representation of the plan being considered now.
 * The string has the following grammar:
 * </p>
 *
 * <pre>
 * join :== factor OP factor
 *
 * OP :== "*" | "#"
 *
 * factor :== factor | conglomerateName
 * </pre>
 */
private String formatPlanSummary(int[] planOrder, int planType) {
    try {
        OptimizerPlan plan = null;
        StringBuilder buffer = new StringBuilder();
        boolean avoidSort = (planType == Optimizer.SORT_AVOIDANCE_PLAN);
        // a negative optimizable number indicates the end of the plan
        int planLength = 0;
        for (; planLength < planOrder.length; planLength++) {
            if (planOrder[planLength] < 0) {
                break;
            }
        }
        for (int i = 0; i < planLength; i++) {
            int listIndex = planOrder[i];
            if (listIndex >= _currentQueryBlock.optimizableList.size()) {
                // should never happen!
                buffer.append("{ UNKNOWN LIST INDEX " + listIndex + " } ");
                continue;
            }
            Optimizable optimizable = _currentQueryBlock.optimizableList.getOptimizable(listIndex);
            AccessPath ap = avoidSort ? optimizable.getBestSortAvoidancePath() : optimizable.getBestAccessPath();
            JoinStrategy js = ap.getJoinStrategy();
            UniqueTupleDescriptor utd = OptimizerImpl.isTableFunction(optimizable) ? ((StaticMethodCallNode) ((FromVTI) ((ProjectRestrictNode) optimizable).getChildResult()).getMethodCall()).ad : ap.getConglomerateDescriptor();
            OptimizerPlan current = (utd == null) ? new OptimizerPlan.DeadEnd(getOptimizableName(optimizable).toString()) : OptimizerPlan.makeRowSource(utd, _lcc.getDataDictionary());
            if (plan != null) {
                current = new OptimizerPlan.Join(js, plan, current);
            }
            plan = current;
        }
        return plan.toString();
    } catch (Exception e) {
        return e.getMessage();
    }
}

Example 2 with OptimizerPlan

use of org.apache.derby.iapi.sql.compile.OptimizerPlan in project derby by apache.

the class OptimizerImpl method getNextDecoratedPermutation.

/**
 * @see Optimizer#getNextDecoratedPermutation
 *
 * @exception StandardException		Thrown on error
 */
public boolean getNextDecoratedPermutation() throws StandardException {
    boolean retval;
    Optimizable curOpt = optimizableList.getOptimizable(proposedJoinOrder[joinPosition]);
    double originalRowCount = 0.0;
    while (true) {
        /* Returns true until all access paths are exhausted */
        retval = curOpt.nextAccessPath(this, (OptimizablePredicateList) null, currentRowOrdering);
        // if the user didn't specify an explicit plan, we're ok
        if (overridingPlan == null) {
            break;
        }
        if (!retval) {
            break;
        }
        // the join order, then we move on to the next position
        if (currentPlan != null) {
            if (currentPlan.countLeafNodes() == (joinPosition + 1)) {
                retval = false;
                break;
            }
        }
        // at this point, figure out if the plan so far is a prefix of the desired plan
        OptimizerPlan candidate = OptimizerPlan.makeRowSource(getTupleDescriptor(curOpt), dDictionary);
        if (candidate == null) {
            retval = false;
            break;
        }
        if (currentPlan != null) {
            candidate = new OptimizerPlan.Join(curOpt.getCurrentAccessPath().getJoinStrategy(), currentPlan, candidate);
        }
        if (candidate.isLeftPrefixOf(overridingPlan)) {
            currentPlan = candidate;
            break;
        }
    // well, that decoration didn't match up with the user-specified plan.
    // try again
    }
    // checked was the only one possible for this round.
    if ((curOpt.getBestAccessPath().getCostEstimate() != null) && (curOpt.getCurrentAccessPath().getCostEstimate() != null)) {
        // by "current" access path) was not the best.
        if (curOpt.getBestAccessPath().getCostEstimate().compare(curOpt.getCurrentAccessPath().getCostEstimate()) != 0) {
            curOpt.updateBestPlanMap(FromTable.LOAD_PLAN, curOpt);
        } else if (curOpt.getBestAccessPath().getCostEstimate().rowCount() < curOpt.getCurrentAccessPath().getCostEstimate().rowCount()) {
            // If currentCost and bestCost have the same cost estimate
            // but currentCost has been rejected because of memory, we
            // still need to revert the plans.  In this case the row
            // count for currentCost will be greater than the row count
            // for bestCost, so that's what we just checked.
            curOpt.updateBestPlanMap(FromTable.LOAD_PLAN, curOpt);
        }
    }
    /* If we needed to revert plans for curOpt, we just did it above.
		 * So we no longer need to keep the previous best plan--and in fact,
		 * keeping it can lead to extreme memory usage for very large
		 * queries.  So delete the stored plan for curOpt. DERBY-1315.
		 */
    curOpt.updateBestPlanMap(FromTable.REMOVE_PLAN, curOpt);
    /*
		** When all the access paths have been looked at, we know what the
		** cheapest one is, so remember it.  Only do this if a cost estimate
		** has been set for the best access path - one will not have been
		** set if no feasible plan has been found.
		*/
    CostEstimate ce = curOpt.getBestAccessPath().getCostEstimate();
    if ((!retval) && (ce != null)) {
        /*
			** Add the cost of the current optimizable to the total cost.
			** The total cost is the sum of all the costs, but the total
			** number of rows is the number of rows returned by the innermost
			** optimizable.
			*/
        currentCost.setCost(currentCost.getEstimatedCost() + ce.getEstimatedCost(), ce.rowCount(), ce.singleScanRowCount());
        if (curOpt.considerSortAvoidancePath() && requiredRowOrdering != null) {
            /* Add the cost for the sort avoidance path, if there is one */
            ce = curOpt.getBestSortAvoidancePath().getCostEstimate();
            currentSortAvoidanceCost.setCost(currentSortAvoidanceCost.getEstimatedCost() + ce.getEstimatedCost(), ce.rowCount(), ce.singleScanRowCount());
        }
        if (tracingIsOn()) {
            tracer().traceCostWithoutSortAvoidance(currentCost);
            if (curOpt.considerSortAvoidancePath()) {
                tracer().traceCostWithSortAvoidance(currentSortAvoidanceCost);
            }
        }
        /* Do we have a complete join order? */
        if (joinPosition == (numOptimizables - 1)) {
            if (tracingIsOn()) {
                tracer().traceCompleteJoinOrder();
            }
            /* Add cost of sorting to non-sort-avoidance cost */
            if (requiredRowOrdering != null) {
                boolean gotSortCost = false;
                /* Only get the sort cost once */
                if (sortCost == null) {
                    sortCost = newCostEstimate();
                } else /* requiredRowOrdering records if the bestCost so far is
					 * sort-needed or not, as done in rememberBestCost.  If
					 * the bestCost so far is sort-needed, and assume
					 * currentCost is also sort-needed, we want this comparison
					 * to be as accurate as possible.  Different plans may
					 * produce different estimated row count (eg., heap scan
					 * vs. index scan during a join), sometimes the difference
					 * could be very big.  However the actual row count should
					 * be only one value.  So when comparing these two plans,
					 * we want them to have the same sort cost.  We want to
					 * take the smaller row count, because a better estimation
					 * (eg. through index) would yield a smaller number.  We
					 * adjust the bestCost here if it had a bigger rowCount
					 * estimate.  The performance improvement of doing this
					 * sometimes is quite dramatic, eg. from 17 sec to 0.5 sec,
					 * see beetle 4353.
					 */
                if (requiredRowOrdering.getSortNeeded()) {
                    if (bestCost.rowCount() > currentCost.rowCount()) {
                        // adjust bestCost
                        requiredRowOrdering.estimateCost(bestCost.rowCount(), bestRowOrdering, sortCost);
                        double oldSortCost = sortCost.getEstimatedCost();
                        requiredRowOrdering.estimateCost(currentCost.rowCount(), bestRowOrdering, sortCost);
                        gotSortCost = true;
                        bestCost.setCost(bestCost.getEstimatedCost() - oldSortCost + sortCost.getEstimatedCost(), sortCost.rowCount(), currentCost.singleScanRowCount());
                    } else if (bestCost.rowCount() < currentCost.rowCount()) {
                        // adjust currentCost's rowCount
                        currentCost.setCost(currentCost.getEstimatedCost(), bestCost.rowCount(), currentCost.singleScanRowCount());
                    }
                }
                /* This does not figure out if sorting is necessary, just
					 * an asumption that sort is needed; if the assumption is
					 * wrong, we'll look at sort-avoidance cost as well, later
					 */
                if (!gotSortCost) {
                    requiredRowOrdering.estimateCost(currentCost.rowCount(), bestRowOrdering, sortCost);
                }
                originalRowCount = currentCost.rowCount();
                currentCost.setCost(currentCost.getEstimatedCost() + sortCost.getEstimatedCost(), sortCost.rowCount(), currentCost.singleScanRowCount());
                if (tracingIsOn()) {
                    tracer().traceSortCost(sortCost, currentCost);
                }
            }
            /*
				** Is the cost of this join order lower than the best one we've
				** found so far?
				**
				** NOTE: If the user has specified a join order, it will be the
				** only join order the optimizer considers, so it is OK to use
				** costing to decide that it is the "best" join order.
				**
				** For very deeply nested queries, it's possible that the optimizer
				** will return an estimated cost of Double.INFINITY, which is
				** greater than our uninitialized cost of Double.MAX_VALUE and
				** thus the "compare" check below will return false.   So we have
				** to check to see if bestCost is uninitialized and, if so, we
				** save currentCost regardless of what value it is--because we
				** haven't found anything better yet.
				**
				** That said, it's also possible for bestCost to be infinity
				** AND for current cost to be infinity, as well.  In that case
				** we can't really tell much by comparing the two, so for lack
				** of better alternative we look at the row counts.  See
				** CostEstimateImpl.compare() for more.
				*/
            if ((!foundABestPlan) || (currentCost.compare(bestCost) < 0) || bestCost.isUninitialized()) {
                rememberBestCost(currentCost, Optimizer.NORMAL_PLAN);
                // Since we just remembered all of the best plans,
                // no need to reload them when pulling Optimizables
                // from this join order.
                reloadBestPlan = false;
            } else
                reloadBestPlan = true;
            /* Subtract cost of sorting from non-sort-avoidance cost */
            if (requiredRowOrdering != null) {
                /*
					** The cost could go negative due to loss of precision.
					*/
                double newCost = currentCost.getEstimatedCost() - sortCost.getEstimatedCost();
                if (newCost < 0.0)
                    newCost = 0.0;
                currentCost.setCost(newCost, originalRowCount, currentCost.singleScanRowCount());
            }
            /*
				** This may be the best sort-avoidance plan if there is a
				** required row ordering, and we are to consider a sort
				** avoidance path on the last Optimizable in the join order.
				*/
            if (requiredRowOrdering != null && curOpt.considerSortAvoidancePath()) {
                if (requiredRowOrdering.sortRequired(bestRowOrdering, optimizableList, proposedJoinOrder) == RequiredRowOrdering.NOTHING_REQUIRED) {
                    if (tracingIsOn()) {
                        tracer().traceCurrentPlanAvoidsSort(bestCost, currentSortAvoidanceCost);
                    }
                    if ((currentSortAvoidanceCost.compare(bestCost) <= 0) || bestCost.isUninitialized()) {
                        rememberBestCost(currentSortAvoidanceCost, Optimizer.SORT_AVOIDANCE_PLAN);
                    }
                }
            }
        }
    }
    return retval;
}