• Simon Riggs's avatar
    Use Foreign Key relationships to infer multi-column join selectivity · 137805f8
    Simon Riggs authored
    In cases where joins use multiple columns we currently assess each join
    separately causing gross mis-estimates for join cardinality.
    
    This patch adds use of FK information for the first time into the
    planner. When FKs are present and we have multi-column join information,
    plan estimates will be drastically improved. Cases with multiple FKs
    are handled, though partial matches are ignored currently.
    
    Net effect is substantial performance improvements for joins in many
    common cases. Additional planning time is isolated to cases that are
    currently performing poorly, measured at 0.08 - 0.15 ms.
    
    Please watch for planner performance regressions; circumstances seem
    unlikely but the law of unintended consequences may apply somewhen.
    Additional complex tests welcome to prove this before release.
    
    Tests can be performed using SET enable_fkey_estimates = on | off
    using scripts provided during Hackers discussions, message id:
    552335D9.3090707@2ndquadrant.com
    
    Authors: Tomas Vondra and David Rowley
    Reviewed and tested by Simon Riggs, adding comments only
    137805f8
guc.c 262 KB