Commit 137805f

committed

Use Foreign Key relationships to infer multi-column join selectivity

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

1 parent 6928484 commit 137805fCopy full SHA for 137805f

5 files changed

+392

-24

lines changed

src
- backend
  - optimizer/path
    - costsize.c
  - utils/misc
    - guc.c
- include/optimizer
  - cost.h
  - paths.h
- test/regress/expected
  - rangefuncs.out

5 files changed

+392

-24

lines changed

Comments

(0)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit 137805f

5 files changed

5 files changed

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier! Saves Data!

File tree

5 files changed

5 files changed

0 commit comments

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier! Saves Data!