• Tom Lane's avatar
    Redesign the planner's handling of index-descent cost estimation. · 31f38f28
    Tom Lane authored
    Historically we've used a couple of very ad-hoc fudge factors to try to
    get the right results when indexes of different sizes would satisfy a
    query with the same number of index leaf tuples being visited.  In
    commit 21a39de5 I tweaked one of these
    fudge factors, with results that proved disastrous for larger indexes.
    Commit bf01e34b fudged it some more,
    but still with not a lot of principle behind it.
    
    What seems like a better way to address these issues is to explicitly model
    index-descent costs, since that's what's really at stake when considering
    diferent indexes with similar leaf-page-level costs.  We tried that once
    long ago, and found that charging random_page_cost per page descended
    through was way too much, because upper btree levels tend to stay in cache
    in real-world workloads.  However, there's still CPU costs to think about,
    and the previous fudge factors can be seen as a crude attempt to account
    for those costs.  So this patch replaces those fudge factors with explicit
    charges for the number of tuple comparisons needed to descend the index
    tree, plus a small charge per page touched in the descent.  The cost
    multipliers are chosen so that the resulting charges are in the vicinity of
    the historical (pre-9.2) fudge factors for indexes of up to about a million
    tuples, while not ballooning unreasonably beyond that, as the old fudge
    factor did (even more so in 9.2).
    
    To make this work accurately for btree indexes, add some code that allows
    extraction of the known root-page height from a btree.  There's no
    equivalent number readily available for other index types, but we can use
    the log of the number of index pages as an approximate substitute.
    
    This seems like too much of a behavioral change to risk back-patching,
    but it should improve matters going forward.  In 9.2 I'll just revert
    the fudge-factor change.
    31f38f28
outfuncs.c 72.3 KB