8000 Fix cost estimation for indexscan filter conditions. · postgrespro/postgres@732bfa2 · GitHub
[go: up one dir, main page]
Skip to content

    • Commit 732bfa2

    Browse files
    tglsfdctglsfdc
    committed
    Fix cost estimation for indexscan filter conditions.
    cost_index's method for estimating per-tuple costs of evaluating filter conditions (a/k/a qpquals) was completely wrong in the presence of derived indexable conditions, such as range conditions derived from a LIKE clause. This was largely masked in common cases as a result of all simple operator clauses having about the same costs, but it could show up in a big way when dealing with functional indexes containing expensive functions, as seen for example in bug #6579 from Istvan Endredy. Rejigger the calculation to give sane answers when the indexquals aren't a subset of the baserestrictinfo list. As a side benefit, we now do the calculation properly for cases involving join clauses (ie, parameterized indexscans), which we always overestimated before. There are still cases where this is an oversimplification, such as clauses that can be dropped because they are implied by a partial index's predicate. But we've never accounted for that in cost estimates before, and I'm not convinced it's worth the cycles to try to do so.
    1 parent 880bfc3 commit 732bfa2

    File tree

    1 file changed

    +21
    -19
    lines changed

    1 file changed

    +21
    -19
    lines changed

    ‎src/backend/optimizer/path/costsize.c

    Lines changed: 21 additions & 19 deletions
    Original file line numberDiff line numberDiff line change
    @@ -228,6 +228,7 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
    228228
    IndexOptInfo *index = path->indexinfo;
    229229
    RelOptInfo *baserel = index->rel;
    230230
    bool indexonly = (path->path.pathtype == T_IndexOnlyScan);
    231+
    List *allclauses;
    231232
    Cost startup_cost = 0;
    232233
    Cost run_cost = 0;
    233234
    Cost indexStartupCost;
    @@ -239,6 +240,7 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
    239240
    spc_random_page_cost;
    240241
    Cost min_IO_cost,
    241242
    max_IO_cost;
    243+
    QualCost qpqual_cost;
    242244
    Cost cpu_per_tuple;
    243245
    double tuples_fetched;
    244246
    double pages_fetched;
    @@ -267,8 +269,6 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
    267269
    * Note that we force the clauses to be treated as non-join clauses
    268270
    * during selectivity estimation.
    269271
    */
    270-
    List *allclauses;
    271-
    272272
    allclauses = list_union_ptr(baserel->baserestrictinfo,
    273273
    path->indexclauses);
    274274
    path->path.rows = baserel->tuples *
    @@ -283,6 +283,9 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
    283283
    }
    284284
    else
    285285
    {
    286+
    /* allclauses should just be the rel's restriction clauses */
    287+
    allclauses = baserel->baserestrictinfo;
    288+
    286289
    /*
    287290
    * The number of rows is the same as the parent rel's estimate, since
    288291
    * this isn't a parameterized path.
    @@ -442,24 +445,23 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
    442445
    /*
    443446
    * Estimate CPU costs per tuple.
    444447
    *
    445-
    * Normally the indexquals will be removed from the list of restriction
    446-
    * clauses that we have to evaluate as qpquals, so we should subtract
    447-
    * their costs from baserestrictcost. But if we are doing a join then
    448-
    * some of the indexquals are join clauses and shouldn't be subtracted.
    449-
    * Rather than work out exactly how much to subtract, we don't subtract
    450-
    * anything.
    451-
    */
    452-
    startup_cost += baserel->baserestrictcost.startup;
    453-
    cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
    454-
    455-
    if (path->path.required_outer == NULL)
    456-
    {
    457-
    QualCost index_qual_cost;
    448+
    * What we want here is cpu_tuple_cost plus the evaluation costs of any
    449+
    * qual clauses that we have to evaluate as qpquals. We approximate that
    450+
    * list as allclauses minus any clauses appearing in indexquals (as
    451+
    * before, assuming that pointer equality is enough to recognize duplicate
    452+
    * RestrictInfos). This method neglects some considerations such as
    453+
    * clauses that needn't be checked because they are implied by a partial
    454+
    * index's predicate. It does not seem worth the cycles to try to factor
    455+
    * those things in at this stage, even though createplan.c will take pains
    456+
    * to remove such unnecessary clauses from the qpquals list if this path
    457+
    * is selected for use.
    458+
    */
    459+
    cost_qual_eval(&qpqual_cost,
    460+
    list_difference_ptr(allclauses, path->indexquals),
    461+
    root);
    458462

    459-
    cost_qual_eval(&index_qual_cost, path->indexquals, root);
    460-
    /* any startup cost still has to be paid ... */
    461-
    cpu_per_tuple -= index_qual_cost.per_tuple;
    462-
    }
    463+
    startup_cost += qpqual_cost.startup;
    464+
    cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
    463465

    464466
    run_cost += cpu_per_tuple * tuples_fetched;
    465467

    0 commit comments

    Comments
     (0)
    0