• Tom Lane's avatar
    Fix foreign-key selectivity estimation in the presence of constants. · ad1c36b0
    Tom Lane authored
    get_foreign_key_join_selectivity() looks for join clauses that equate
    the two sides of the FK constraint.  However, if we have a query like
    "WHERE fktab.a = pktab.a and fktab.a = 1", it won't find any such join
    clause, because equivclass.c replaces the given clauses with "fktab.a
    = 1 and pktab.a = 1", which can be enforced at the scan level, leaving
    nothing to be done for column "a" at the join level.
    
    We can fix that expectation without much trouble, but then a new problem
    arises: applying the foreign-key-based selectivity rule produces a
    rowcount underestimate, because we're effectively double-counting the
    selectivity of the "fktab.a = 1" clause.  So we have to cancel that
    selectivity out of the estimate.
    
    To fix, refactor process_implied_equality() so that it can pass back the
    new RestrictInfo to its callers in equivclass.c, allowing the generated
    "fktab.a = 1" clause to be saved in the EquivalenceClass's ec_derives
    list.  Then it's not much trouble to dig out the relevant RestrictInfo
    when we need to adjust an FK selectivity estimate.  (While at it, we
    can also remove the expensive use of initialize_mergeclause_eclasses()
    to set up the new RestrictInfo's left_ec and right_ec pointers.
    The equivclass.c code can set those basically for free.)
    
    This seems like clearly a bug fix, but I'm hesitant to back-patch it,
    first because there's some API/ABI risk for extensions and second because
    we're usually loath to destabilize plan choices in stable branches.
    
    Per report from Sigrid Ehrenreich.
    
    Discussion: https://postgr.es/m/1019549.1603770457@sss.pgh.pa.us
    Discussion: https://postgr.es/m/AM6PR02MB5287A0ADD936C1FA80973E72AB190@AM6PR02MB5287.eurprd02.prod.outlook.com
    ad1c36b0
plancat.c 69.6 KB