summaryrefslogtreecommitdiff
path: root/src/include
diff options
context:
space:
mode:
authorSimon Riggs2016-04-08 01:51:09 +0000
committerSimon Riggs2016-04-08 01:51:09 +0000
commit137805f89acb361144ec98d9847e26d2848aa57e (patch)
treea3b988d7f3e22c2fb9324afbb9e01e7c5f58955f /src/include
parent6928484bda454f9ab2456d385b2d317f18b6bf1a (diff)
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
Diffstat (limited to 'src/include')
-rw-r--r--src/include/optimizer/cost.h1
-rw-r--r--src/include/optimizer/paths.h2
2 files changed, 3 insertions, 0 deletions
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index d4adca6836a..58ac1638ec1 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -66,6 +66,7 @@ extern bool enable_nestloop;
extern bool enable_material;
extern bool enable_mergejoin;
extern bool enable_hashjoin;
+extern bool enable_fkey_estimates;
extern int constraint_exclusion;
extern double clamp_row_est(double nrows);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7ad4f026a36..d9a3c1f2326 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -76,6 +76,8 @@ extern Expr *adjust_rowcompare_for_index(RowCompareExpr *clause,
int indexcol,
List **indexcolnos,
bool *var_on_left_p);
+extern bool has_matching_fkey(RelOptInfo *rel, RelOptInfo *frel, List *clauses,
+ bool reverse);
/*
* tidpath.h