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

Example 21 with CostEstimate

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

the class OptimizerImpl method considerCost.

/**
 * This is the version of costOptimizable for non-base-tables.
 *
 * @see Optimizer#considerCost
 *
 * @exception StandardException		Thrown on error
 */
public void considerCost(Optimizable optimizable, OptimizablePredicateList predList, CostEstimate estimatedCost, CostEstimate outerCost) throws StandardException {
    /*
		** Don't consider non-feasible join strategies.
		*/
    if (!optimizable.feasibleJoinStrategy(predList, this)) {
        return;
    }
    // Before considering the cost, make sure we set the optimizable's
    // "current" cost to be the one that we received.  Doing this allows
    // us to compare "current" with "best" later on to find out if
    // the "current" plan is also the "best" one this round--if it's
    // not then we'll have to revert back to whatever the best plan is.
    // That check is performed in getNextDecoratedPermutation() of
    // this class.
    optimizable.getCurrentAccessPath().setCostEstimate(estimatedCost);
    // DERBY-1259.
    if (!optimizable.memoryUsageOK(estimatedCost.rowCount() / outerCost.rowCount(), maxMemoryPerTable)) {
        if (tracingIsOn()) {
            tracer().traceSkippingBecauseTooMuchMemory(maxMemoryPerTable);
        }
        return;
    }
    /* Pick the cheapest cost for this particular optimizable. 
		 * NOTE: Originally, the code only chose the new access path if 
		 * it was cheaper than the old access path.  However, I (Jerry)
		 * found that the new and old costs were the same for a derived
		 * table and the old access path did not have a join strategy
		 * associated with it in that case.  So, we now choose the new
		 * access path if it is the same cost or cheaper than the current
		 * access path.
		 */
    AccessPath ap = optimizable.getBestAccessPath();
    CostEstimate bestCostEstimate = ap.getCostEstimate();
    if ((bestCostEstimate == null) || bestCostEstimate.isUninitialized() || (estimatedCost.compare(bestCostEstimate) <= 0)) {
        ap.setCostEstimate(estimatedCost);
        optimizable.rememberJoinStrategyAsBest(ap);
    }
    /*
		** Keep track of the best sort-avoidance path if there is a
		** required row ordering.
		*/
    if (requiredRowOrdering != null) {
        /*
			** The current optimizable can avoid a sort only if the
			** outer one does, also (if there is an outer one).
			*/
        if (joinPosition == 0 || optimizableList.getOptimizable(proposedJoinOrder[joinPosition - 1]).considerSortAvoidancePath()) {
            /*
				** There is a required row ordering - does the proposed access
				** path avoid a sort?
				*/
            if (requiredRowOrdering.sortRequired(currentRowOrdering, assignedTableMap, optimizableList, proposedJoinOrder) == RequiredRowOrdering.NOTHING_REQUIRED) {
                ap = optimizable.getBestSortAvoidancePath();
                bestCostEstimate = ap.getCostEstimate();
                /* Is this the cheapest sort-avoidance path? */
                if ((bestCostEstimate == null) || bestCostEstimate.isUninitialized() || (estimatedCost.compare(bestCostEstimate) < 0)) {
                    ap.setCostEstimate(estimatedCost);
                    optimizable.rememberJoinStrategyAsBest(ap);
                    optimizable.rememberSortAvoidancePath();
                    /*
						** Remember the current row ordering as best
						*/
                    currentRowOrdering.copy(bestRowOrdering);
                }
            }
        }
    }
}

Example 22 with CostEstimate

use of org.apache.derby.iapi.sql.compile.CostEstimate 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;
}

Example 23 with CostEstimate

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

the class OptimizerImpl method getFinalCost.

/**
 * @see Optimizer#getFinalCost
 *
 * Sum up the cost of all of the trulyTheBestAccessPaths
 * for the Optimizables in our list.  Assumption is that
 * we only get here after optimization has completed--i.e.
 * while modifying access paths.
 */
public CostEstimate getFinalCost() {
    // If we already did this once, just return the result.
    if (finalCostEstimate != null)
        return finalCostEstimate;
    // 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.
    finalCostEstimate = getNewCostEstimate(0.0d, 0.0d, 0.0d);
    for (int i = 0; i < bestJoinOrder.length; i++) {
        CostEstimate ce = optimizableList.getOptimizable(bestJoinOrder[i]).getTrulyTheBestAccessPath().getCostEstimate();
        finalCostEstimate.setCost(finalCostEstimate.getEstimatedCost() + ce.getEstimatedCost(), ce.rowCount(), ce.singleScanRowCount());
    }
    return finalCostEstimate;
}

Example 24 with CostEstimate

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

the class IntersectOrExceptNode method getFinalCostEstimate.

// end of generate
/**
 * @see ResultSetNode#getFinalCostEstimate
 *
 * Get the final CostEstimate for this IntersectOrExceptNode.
 *
 * @return	The final CostEstimate for this IntersectOrExceptNode,
 *  which is the sum of the two child costs.  The final number of
 *  rows depends on whether this is an INTERSECT or EXCEPT (see
 *  getRowCountEstimate() in this class for more).
 */
@Override
CostEstimate getFinalCostEstimate() throws StandardException {
    if (getCandidateFinalCostEstimate() != null) {
        return getCandidateFinalCostEstimate();
    }
    CostEstimate leftCE = leftResultSet.getFinalCostEstimate();
    CostEstimate rightCE = rightResultSet.getFinalCostEstimate();
    setCandidateFinalCostEstimate(getNewCostEstimate());
    getCandidateFinalCostEstimate().setCost(leftCE.getEstimatedCost() + rightCE.getEstimatedCost(), getRowCountEstimate(leftCE.rowCount(), rightCE.rowCount()), getSingleScanRowCountEstimate(leftCE.singleScanRowCount(), rightCE.singleScanRowCount()));
    return getCandidateFinalCostEstimate();
}

Example 25 with CostEstimate

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

the class GroupByNode method estimateCost.

/**
 * @see Optimizable#estimateCost
 *
 * @exception StandardException		Thrown on error
 */
@Override
public CostEstimate estimateCost(OptimizablePredicateList predList, ConglomerateDescriptor cd, CostEstimate outerCost, Optimizer optimizer, RowOrdering rowOrdering) throws StandardException {
    // RESOLVE: NEED TO FACTOR IN THE COST OF GROUPING (SORTING) HERE
    // 
    CostEstimate childCost = ((Optimizable) childResult).estimateCost(predList, cd, outerCost, optimizer, rowOrdering);
    CostEstimate costEst = getCostEstimate(optimizer);
    costEst.setCost(childCost.getEstimatedCost(), childCost.rowCount(), childCost.singleScanRowCount());
    return costEst;
}