Hi, On 03/14/2016 02:12 PM, David Steele wrote:
Hi Thomas,
...
I don't think it would be clear to any reviewer which patch to apply even if they were working. I'm marking this "waiting for author".
Yeah. Rebasing the patches to current master was simple enough (there was just a simple #include conflict), but figuring out which of the patches is review-worthy was definitely difficult.
I do believe David's last patch is the best step forward, so I've rebased it, and made some basic aesthetic fixes (adding or rewording comments on a few places, etc.)
The one important code change is that I've removed the piece of code from find_best_foreign_key_quals that tried to be a bit too smart about equivalence classes.
My understanding is that it tried to handle cases like this example:
CREATE TABLE f (id1 INT, id2 INT, PRIMARY KEY (id1, id2));
CREATE TABLE d1 (id1 INT, id2 INT, FOREIGN KEY (id1, id2)
REFERENCES f(id1, id2));
CREATE TABLE d2 (id1 INT, id2 INT, FOREIGN KEY (id1, id2)
REFERENCES f(id1, id2));
SELECT * FROM f JOIN d1 ON (f.id1 = d1.id1 AND f.id2 = d1.id2)
JOIN d2 ON (f.id1 = d2.id1 AND f.id2 = d2.id2);
But it did so by also deriving foreign keys between d1 and d2, which I
believe is wrong as there really is no foreign key, and thus no
guarantee of existence of a matching row.
FWIW as I explained in a message from 24/2/2015, while this is definitely an issue worth fixing, I believe it needs to be done in some other way, not by foreign keys.
Attached is v3 of the patch, and also three SQL scripts demonstrating the impact of the patch on simple examples.
regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index eb0fc1e..3d38384 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2153,6 +2153,16 @@ _outIndexOptInfo(StringInfo str, const IndexOptInfo *node)
}
static void
+_outForeignKeyOptInfo(StringInfo str, const ForeignKeyOptInfo *node)
+{
+ WRITE_NODE_TYPE("FOREIGNKEYOPTINFO");
+
+ WRITE_OID_FIELD(conrelid);
+ WRITE_OID_FIELD(confrelid);
+ WRITE_INT_FIELD(nkeys);
+}
+
+static void
_outEquivalenceClass(StringInfo str, const EquivalenceClass *node)
{
/*
@@ -3603,6 +3613,9 @@ _outNode(StringInfo str, const void *obj)
case T_IndexOptInfo:
_outIndexOptInfo(str, obj);
break;
+ case T_ForeignKeyOptInfo:
+ _outForeignKeyOptInfo(str, obj);
+ break;
case T_EquivalenceClass:
_outEquivalenceClass(str, obj);
break;
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 5350329..4399a9f 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -3870,6 +3870,347 @@ get_parameterized_joinrel_size(PlannerInfo *root, RelOptInfo *rel,
}
/*
+ * quals_match_foreign_key
+ * Determines if the foreign key is matched by joinquals.
+ *
+ * Checks that there are conditions on all columns of the foreign key, matching
+ * the operator used by the foreign key etc. If such complete match is found,
+ * the function returns bitmap identifying the matching quals (0-based).
+ *
+ * Otherwise (no match at all or incomplete match), NULL is returned.
+ */
+static Bitmapset *
+quals_match_foreign_key(PlannerInfo *root, ForeignKeyOptInfo *fkinfo,
+ RelOptInfo *fkrel, RelOptInfo *foreignrel,
+ List *joinquals)
+{
+ int i;
+ int nkeys = fkinfo->nkeys;
+ Bitmapset *qualmatches = NULL;
+ Bitmapset *fkmatches = NULL;
+
+ /*
+ * Loop over each column of the foreign key and build a bitmap index
+ * of each joinqual which matches. Note that we don't stop when we find
+ * the first match, as the expression could be duplicated in the
+ * joinquals, and we want to generate a bitmap index which has bits set for
+ * every matching join qual.
+ */
+ for (i = 0; i < nkeys; i++)
+ {
+ ListCell *lc;
+ int quallstidx = -1;
+
+ foreach(lc, joinquals)
+ {
+ RestrictInfo *rinfo;
+ OpExpr *clause;
+ Var *leftvar;
+ Var *rightvar;
+
+ quallstidx++;
+
+ /*
+ * Technically we don't need to, but here we skip this qual if
+ * we've matched it to part of the foreign key already. This
+ * should prove to be a useful optimization when the quals appear
+ * in the same order as the foreign key's keys. We need only bother
+ * doing this when the foreign key is made up of more than 1 set
+ * of columns, and we're not testing the first column.
+ */
+ if (i > 0 && bms_is_member(quallstidx, qualmatches))
+ continue;
+
+ /*
+ * Here since 'usefulquals' only contains bitmap indexes for quals
+ * of type "var op var" we can safely skip checking this.
+ */
+ rinfo = (RestrictInfo *) lfirst(lc);
+ clause = (OpExpr *) rinfo->clause;
+
+ /*
+ * If the operator does not match then there's little point in
+ * checking the operands
+ */
+ if (clause->opno != fkinfo->conpfeqop[i])
+ continue;
+
+ leftvar = (Var *) get_leftop((Expr *) clause);
+ rightvar = (Var *) get_rightop((Expr *) clause);
+
+ /* Foreign keys only support Vars, so ignore anything more complex */
+ if (!IsA(leftvar, Var) || !IsA(rightvar, Var))
+ continue;
+
+ /*
+ * For RestrictInfos built from an eclass we must consider each
+ * member of the eclass as rinfo's operands may not belong to the
+ * foreign key. For efficient tracking of which Vars we've found,
+ * since we're only tracking 2 Vars, we use a bitmask. We can
+ * safely finish searching when both of the least significant bits
+ * are set.
+ */
+ if (rinfo->parent_ec)
+ {
+ EquivalenceClass *ec = rinfo->parent_ec;
+ ListCell *lc2;
+ int foundvarmask = 0;
+
+ foreach(lc2, ec->ec_members)
+ {
+ EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
+ Var *var = (Var *) em->em_expr;
+
+ if (!IsA(var, Var))
+ continue;
+
+ if (foreignrel->relid == var->varno &&
+ fkinfo->confkeys[i] == var->varattno)
+ foundvarmask |= 1;
+
+ else if (fkrel->relid == var->varno &&
+ fkinfo->conkeys[i] == var->varattno)
+ foundvarmask |= 2;
+
+ /*
+ * Check if we've found both matches. If found we add
+ * this qual to the matched list and mark this key as
+ * matched too.
+ */
+ if (foundvarmask == 3)
+ {
+ qualmatches = bms_add_member(qualmatches, quallstidx);
+ fkmatches = bms_add_member(fkmatches, i);
+ break;
+ }
+ }
+ }
+ else
+ {
+ /*
+ * In this non eclass RestrictInfo case we'll check if the left
+ * and right Vars match to this part of the foreign key.
+ * Remember that this could be written with the Vars in either
+ * order, so we test both permutations of the expression.
+ */
+ if ((foreignrel->relid == leftvar->varno) &&
+ (fkrel->relid == rightvar->varno) &&
+ (fkinfo->confkeys[i] == leftvar->varattno) &&
+ (fkinfo->conkeys[i] == rightvar->varattno))
+ {
+ qualmatches = bms_add_member(qualmatches, quallstidx);
+ fkmatches = bms_add_member(fkmatches, i);
+ }
+ else if ((foreignrel->relid == rightvar->varno) &&
+ (fkrel->relid == leftvar->varno) &&
+ (fkinfo->confkeys[i] == rightvar->varattno) &&
+ (fkinfo->conkeys[i] == leftvar->varattno))
+ {
+ qualmatches = bms_add_member(qualmatches, quallstidx);
+ fkmatches = bms_add_member(fkmatches, i);
+ }
+ }
+ }
+ }
+
+ /* can't find more matches than columns in the foreign key */
+ Assert(bms_num_members(fkmatches) <= nkeys);
+
+ /* Only return the matches if we the foreign key is matched fully. */
+ if (bms_num_members(fkmatches) == nkeys)
+ {
+ bms_free(fkmatches);
+ return qualmatches;
+ }
+
+ bms_free(fkmatches);
+ bms_free(qualmatches);
+
+ return NULL;
+}
+
+/*
+ * find_best_foreign_key_quals
+ * Finds the foreign key best matching the joinquals.
+ *
+ * Analyzes joinquals to determine if any quals match foreign keys defined the
+ * two relations (fkrel referencing foreignrel). When multiple foreign keys
+ * match, we do choose the one with the most keys as the best one. We might
+ * also choose the one matching the most quals, however we assume the quals
+ * may be duplicated.
+ *
+ * We also track which joinquals match the current foreign key, so that we can
+ * easily skip then when computing the selectivity.
+ *
+ * When no matching foreign key is found we return 0, otherwise we return the
+ * number of keys in the foreign key.
+ *
+ * Foreign keys matched only partially ( are currently ignored.
+ */
+static int
+find_best_foreign_key_quals(PlannerInfo *root, RelOptInfo *fkrel,
+ RelOptInfo *foreignrel, List *joinquals,
+ Bitmapset **joinqualsbitmap)
+{
+ Bitmapset *qualbestmatch;
+ ListCell *lc;
+ int bestmatchnkeys;
+
+ /* fast path out when there's no foreign keys on fkrel */
+ if (fkrel->fkeylist == NIL)
+ {
+ *joinqualsbitmap = NULL;
+ return 0;
+ }
+
+ qualbestmatch = NULL;
+ bestmatchnkeys = 0;
+
+ /* now check the matches for each foreign key defined on the fkrel */
+ foreach(lc, fkrel->fkeylist)
+ {
+ ForeignKeyOptInfo *fkinfo = (ForeignKeyOptInfo *) lfirst(lc);
+ Bitmapset *qualsmatched;
+
+ /*
+ * We make no attempt in checking that this foreign key actually
+ * references 'foreignrel', the reasoning here is that we may be able
+ * to match the foreign key to an eclass member Var of a RestrictInfo
+ * that's in qualslist, this Var may belong to some other relation.
+ */
+ qualsmatched = quals_match_foreign_key(root, fkinfo, fkrel, foreignrel,
+ joinquals);
+
+ /* Did we get a match? And is that match better than a previous one? */
+ if (qualsmatched != NULL && fkinfo->nkeys > bestmatchnkeys)
+ {
+ /* save the new best match */
+ bms_free(qualbestmatch);
+ qualbestmatch = qualsmatched;
+ bestmatchnkeys = fkinfo->nkeys;
+ }
+ }
+
+ *joinqualsbitmap = qualbestmatch;
+ return bestmatchnkeys;
+}
+
+/*
+ * clauselist_join_selectivity
+ * Estimate selectivity of join clauses either by using foreign key info
+ * or by using the regular clauselist_selectivity().
+ *
+ * Since selectivity estimates for each joinqual are multiplied together, this
+ * can cause significant underestimates on the number of join tuples in cases
+ * where there's more than 1 clause in the join condition. To help ease the
+ * pain here we make use of foreign keys, and we assume that 1 row will match
+ * when *all* of the foreign key columns are present in the join condition, any
+ * additional clauses are estimated using clauselist_selectivity().
+ */
+static Selectivity
+clauselist_join_selectivity(PlannerInfo *root, List *joinquals,
+ JoinType jointype, SpecialJoinInfo *sjinfo)
+{
+ int outerid;
+ int innerid;
+ Selectivity sel = 1.0;
+ Bitmapset *foundfkquals = NULL;
+
+ innerid = -1;
+ while ((innerid = bms_next_member(sjinfo->min_righthand, innerid)) >= 0)
+ {
+ RelOptInfo *innerrel = find_base_rel(root, innerid);
+
+ outerid = -1;
+ while ((outerid = bms_next_member(sjinfo->min_lefthand, outerid)) >= 0)
+ {
+ RelOptInfo *outerrel = find_base_rel(root, outerid);
+ Bitmapset *outer2inner;
+ Bitmapset *inner2outer;
+ int innermatches;
+ int outermatches;
+
+ /*
+ * check which quals are matched by a foreign key referencing the
+ * innerrel.
+ */
+ outermatches = find_best_foreign_key_quals(root, outerrel,
+ innerrel, joinquals, &outer2inner);
+
+ /* do the same, but with relations swapped */
+ innermatches = find_best_foreign_key_quals(root, innerrel,
+ outerrel, joinquals, &inner2outer);
+
+ /*
+ * did we find any matches at all? If so we need to see which one is
+ * the best/longest match
+ */
+ if (outermatches != 0 || innermatches != 0)
+ {
+ double referenced_tuples;
+ bool overlap;
+
+ /* either could be zero, but not both. */
+ if (outermatches < innermatches)
+ {
+ overlap = bms_overlap(foundfkquals, inner2outer);
+
+ foundfkquals = bms_add_members(foundfkquals, inner2outer);
+ referenced_tuples = Max(outerrel->tuples, 1.0);
+ }
+ else
+ {
+ overlap = bms_overlap(foundfkquals, outer2inner);
+
+ foundfkquals = bms_add_members(foundfkquals, outer2inner);
+ referenced_tuples = Max(innerrel->tuples, 1.0);
+ }
+
+ /*
+ * XXX should we ignore these overlapping matches?
+ * Or perhaps take the Max() or Min()?
+ */
+ if (overlap)
+ {
+ if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
+ sel = Min(sel,Min(1.0 / (outerrel->tuples / Max(innerrel->tuples, 1.0)), 1.0));
+ else
+ sel = Min(sel, 1.0 / referenced_tuples);
+ }
+ else
+ {
+ if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
+ sel *= Min(1.0 / (outerrel->tuples / Max(innerrel->tuples, 1.0)), 1.0);
+ else
+ sel *= 1.0 / referenced_tuples;
+ }
+ }
+ }
+ }
+
+ /*
+ * If any non matched quals exist then we build a list of the non-matches
+ * and use clauselist_selectivity() to estimate the selectivity of these.
+ */
+ if (bms_num_members(foundfkquals) < list_length(joinquals))
+ {
+ ListCell *lc;
+ int lstidx = 0;
+ List *nonfkeyclauses = NIL;
+
+ foreach (lc, joinquals)
+ {
+ if (!bms_is_member(lstidx, foundfkquals))
+ nonfkeyclauses = lappend(nonfkeyclauses, lfirst(lc));
+ lstidx++;
+ }
+ sel *= clauselist_selectivity(root, nonfkeyclauses, 0, jointype, sjinfo);
+ }
+
+ return sel;
+}
+
+/*
* calc_joinrel_size_estimate
* Workhorse for set_joinrel_size_estimates and
* get_parameterized_joinrel_size.
@@ -3915,11 +4256,11 @@ calc_joinrel_size_estimate(PlannerInfo *root,
}
/* Get the separate selectivities */
- jselec = clauselist_selectivity(root,
- joinquals,
- 0,
- jointype,
- sjinfo);
+ jselec = clauselist_join_selectivity(root,
+ joinquals,
+ jointype,
+ sjinfo);
+
pselec = clauselist_selectivity(root,
pushedquals,
0,
@@ -3932,11 +4273,10 @@ calc_joinrel_size_estimate(PlannerInfo *root,
}
else
{
- jselec = clauselist_selectivity(root,
- restrictlist,
- 0,
- jointype,
- sjinfo);
+ jselec = clauselist_join_selectivity(root,
+ restrictlist,
+ jointype,
+ sjinfo);
pselec = 0.0; /* not used, keep compiler quiet */
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index ad715bb..481911e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -28,6 +28,7 @@
#include "catalog/dependency.h"
#include "catalog/heap.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_constraint.h"
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
@@ -41,6 +42,7 @@
#include "rewrite/rewriteManip.h"
#include "storage/bufmgr.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
@@ -94,6 +96,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
Relation relation;
bool hasindex;
List *indexinfos = NIL;
+ List *fkinfos = NIL;
+ List *fkoidlist;
+ ListCell *l;
/*
* We need not lock the relation since it was already locked, either by
@@ -141,7 +146,6 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
if (hasindex)
{
List *indexoidlist;
- ListCell *l;
LOCKMODE lmode;
indexoidlist = RelationGetIndexList(relation);
@@ -387,6 +391,77 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
rel->indexlist = indexinfos;
+ /* load foreign keys */
+ fkoidlist = RelationGetFKeyList(relation);
+
+ foreach(l, fkoidlist)
+ {
+ int i;
+ ArrayType *arr;
+ Datum adatum;
+ bool isnull;
+ int numkeys;
+ Oid fkoid = lfirst_oid(l);
+
+ HeapTuple htup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fkoid));
+ Form_pg_constraint constraint = (Form_pg_constraint) GETSTRUCT(htup);
+
+ ForeignKeyOptInfo *info;
+
+ Assert(constraint->contype == CONSTRAINT_FOREIGN);
+
+ info = makeNode(ForeignKeyOptInfo);
+
+ info->conrelid = constraint->conrelid;
+ info->confrelid = constraint->confrelid;
+
+ /* conkey */
+ adatum = SysCacheGetAttr(CONSTROID, htup,
+ Anum_pg_constraint_conkey, &isnull);
+ Assert(!isnull);
+
+ arr = DatumGetArrayTypeP(adatum);
+ numkeys = ARR_DIMS(arr)[0];
+ info->conkeys = (int*)palloc0(numkeys * sizeof(int));
+
+ for (i = 0; i < numkeys; i++)
+ info->conkeys[i] = ((int16 *) ARR_DATA_PTR(arr))[i];
+
+ /* confkey */
+ adatum = SysCacheGetAttr(CONSTROID, htup,
+ Anum_pg_constraint_confkey, &isnull);
+ Assert(!isnull);
+
+ arr = DatumGetArrayTypeP(adatum);
+ numkeys = ARR_DIMS(arr)[0];
+ info->confkeys = (int*)palloc0(numkeys * sizeof(int));
+
+ for (i = 0; i < numkeys; i++)
+ info->confkeys[i] = ((int16 *) ARR_DATA_PTR(arr))[i];
+
+ /* conpfeqop */
+ adatum = SysCacheGetAttr(CONSTROID, htup,
+ Anum_pg_constraint_conpfeqop, &isnull);
+ Assert(!isnull);
+
+ arr = DatumGetArrayTypeP(adatum);
+ numkeys = ARR_DIMS(arr)[0];
+ info->conpfeqop = (Oid*)palloc0(numkeys * sizeof(Oid));
+
+ for (i = 0; i < numkeys; i++)
+ info->conpfeqop[i] = ((Oid *) ARR_DATA_PTR(arr))[i];
+
+ info->nkeys = numkeys;
+
+ ReleaseSysCache(htup);
+
+ fkinfos = lcons(info, fkinfos);
+ }
+
+ list_free(fkoidlist);
+
+ rel->fkeylist = fkinfos;
+
/* Grab foreign-table info using the relcache, while we have it */
if (relation->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
{
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 130c06d..9390235 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3957,6 +3957,73 @@ RelationGetIndexList(Relation relation)
}
/*
+ * RelationGetFKeyList -- get a list of foreign key oids
+ *
+ * TODO blah blah blah
+ */
+List *
+RelationGetFKeyList(Relation relation)
+{
+ Relation conrel;
+ SysScanDesc conscan;
+ ScanKeyData skey;
+ HeapTuple htup;
+ List *result;
+ List *oldlist;
+ MemoryContext oldcxt;
+
+ /* Quick exit if we already computed the list. */
+ if (relation->rd_fkeyvalid)
+ return list_copy(relation->rd_fkeylist);
+
+ /*
+ * We build the list we intend to return (in the caller's context) while
+ * doing the scan. After successfully completing the scan, we copy that
+ * list into the relcache entry. This avoids cache-context memory leakage
+ * if we get some sort of error partway through.
+ */
+ result = NIL;
+
+ /* Prepare to scan pg_constraint for entries having conrelid = this rel. */
+ ScanKeyInit(&skey,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+
+ conrel = heap_open(ConstraintRelationId, AccessShareLock);
+ conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true,
+ NULL, 1, &skey);
+
+ while (HeapTupleIsValid(htup = systable_getnext(conscan)))
+ {
+ Form_pg_constraint constraint = (Form_pg_constraint) GETSTRUCT(htup);
+
+ /* return only foreign keys */
+ if (constraint->contype != CONSTRAINT_FOREIGN)
+ continue;
+
+ /* Add index's OID to result list in the proper order */
+ result = insert_ordered_oid(result, HeapTupleGetOid(htup));
+ }
+
+ systable_endscan(conscan);
+
+ heap_close(conrel, AccessShareLock);
+
+ /* Now save a copy of the completed list in the relcache entry. */
+ oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
+ oldlist = relation->rd_fkeylist;
+ relation->rd_fkeylist = list_copy(result);
+ relation->rd_fkeyvalid = true;
+ MemoryContextSwitchTo(oldcxt);
+
+ /* Don't leak the old list, if there is one */
+ list_free(oldlist);
+
+ return result;
+}
+
+/*
* insert_ordered_oid
* Insert a new Oid into a sorted list of Oids, preserving ordering
*
@@ -4920,6 +4987,8 @@ load_relcache_init_file(bool shared)
rel->rd_indexattr = NULL;
rel->rd_keyattr = NULL;
rel->rd_idattr = NULL;
+ rel->rd_fkeylist = NIL;
+ rel->rd_fkeyvalid = false;
rel->rd_createSubid = InvalidSubTransactionId;
rel->rd_newRelfilenodeSubid = InvalidSubTransactionId;
rel->rd_amcache = NULL;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index fad9988..18f42b3 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -223,6 +223,7 @@ typedef enum NodeTag
T_PlannerGlobal,
T_RelOptInfo,
T_IndexOptInfo,
+ T_ForeignKeyOptInfo,
T_ParamPathInfo,
T_Path,
T_IndexPath,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 641728b..e88e9a2 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -539,6 +539,7 @@ typedef struct RelOptInfo
List *lateral_vars; /* LATERAL Vars and PHVs referenced by rel */
Relids lateral_referencers; /* rels that reference me laterally */
List *indexlist; /* list of IndexOptInfo */
+ List *fkeylist; /* list of ForeignKeyOptInfo */
BlockNumber pages; /* size estimates derived from pg_class */
double tuples;
double allvisfrac;
@@ -634,6 +635,27 @@ typedef struct IndexOptInfo
void (*amcostestimate) (); /* AM's cost estimator */
} IndexOptInfo;
+/*
+ * ForeignKeyOptInfo
+ * Per-foreign-key information for planning/optimization
+ *
+ * Only includes columns from pg_constraint related to foreign keys.
+ *
+ * conkeys[], confkeys[] and conpfeqop[] each have nkeys entries.
+ */
+typedef struct ForeignKeyOptInfo
+{
+ NodeTag type;
+
+ Oid conrelid; /* relation constrained by the foreign key */
+ Oid confrelid; /* relation referenced by the foreign key */
+
+ int nkeys; /* number of columns in the foreign key */
+ int *conkeys; /* attnums of columns in the constrained table */
+ int *confkeys; /* attnums of columns in the referenced table */
+ Oid *conpfeqop; /* OIDs of equality operators used by the FK */
+
+} ForeignKeyOptInfo;
/*
* EquivalenceClasses
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 2fccc3a..f0840e5 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
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index f2bebf2..f1fe4dc 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -61,6 +61,7 @@ typedef struct RelationData
bool rd_isvalid; /* relcache entry is valid */
char rd_indexvalid; /* state of rd_indexlist: 0 = not valid, 1 =
* valid, 2 = temporarily forced */
+ bool rd_fkeyvalid; /* state of rd_fkeylist: 0 = not valid, 1 = valid */
/*
* rd_createSubid is the ID of the highest subtransaction the rel has
@@ -94,6 +95,9 @@ typedef struct RelationData
Oid rd_oidindex; /* OID of unique index on OID, if any */
Oid rd_replidindex; /* OID of replica identity index, if any */
+ /* data managed by RelationGetFKList: */
+ List *rd_fkeylist; /* OIDs of foreign keys */
+
/* data managed by RelationGetIndexAttrBitmap: */
Bitmapset *rd_indexattr; /* identifies columns used in indexes */
Bitmapset *rd_keyattr; /* cols that can be ref'd by foreign keys */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 1b48304..7f07c26 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -38,6 +38,7 @@ extern void RelationClose(Relation relation);
* Routines to compute/retrieve additional cached information
*/
extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetFKeyList(Relation relation);
extern Oid RelationGetOidIndex(Relation relation);
extern Oid RelationGetReplicaIndex(Relation relation);
extern List *RelationGetIndexExpressions(Relation relation);
fkey3.sql
Description: application/sql
fkey2.sql
Description: application/sql
fkey1.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
