diff options
Diffstat (limited to 'src/backend/optimizer/path/costsize.c')
| -rw-r--r-- | src/backend/optimizer/path/costsize.c | 97 |
1 files changed, 30 insertions, 67 deletions
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index f062c6b9f1e..ef25fab71bf 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -4318,7 +4318,6 @@ get_foreign_key_join_selectivity(PlannerInfo *root, { ForeignKeyOptInfo *fkinfo = (ForeignKeyOptInfo *) lfirst(lc); bool ref_is_outer; - bool use_smallest_selectivity = false; List *removedlist; ListCell *cell; ListCell *prev; @@ -4338,6 +4337,22 @@ get_foreign_key_join_selectivity(PlannerInfo *root, continue; /* + * If we're dealing with a semi/anti join, and the FK's referenced + * relation is on the outside, then knowledge of the FK doesn't help + * us figure out what we need to know (which is the fraction of outer + * rows that have matches). On the other hand, if the referenced rel + * is on the inside, then all outer rows must have matches in the + * referenced table (ignoring nulls). But any restriction or join + * clauses that filter that table will reduce the fraction of matches. + * We can account for restriction clauses, but it's too hard to guess + * how many table rows would get through a join that's inside the RHS. + * Hence, if either case applies, punt and ignore the FK. + */ + if ((jointype == JOIN_SEMI || jointype == JOIN_ANTI) && + (ref_is_outer || bms_membership(inner_relids) != BMS_SINGLETON)) + continue; + + /* * Modify the restrictlist by removing clauses that match the FK (and * putting them into removedlist instead). It seems unsafe to modify * the originally-passed List structure, so we make a shallow copy the @@ -4437,10 +4452,7 @@ get_foreign_key_join_selectivity(PlannerInfo *root, * However (1) if there are any strict restriction clauses for the * referencing column(s) elsewhere in the query, derating here would * be double-counting the null fraction, and (2) it's not very clear - * how to combine null fractions for multiple referencing columns. - * - * In the use_smallest_selectivity code below, null derating is done - * implicitly by relying on clause_selectivity(); in the other cases, + * how to combine null fractions for multiple referencing columns. So * we do nothing for now about correcting for nulls. * * XXX another point here is that if either side of an FK constraint @@ -4453,52 +4465,23 @@ get_foreign_key_join_selectivity(PlannerInfo *root, * work, it is uncommon in practice to have an FK referencing a parent * table. So, at least for now, disregard inheritance here. */ - if (ref_is_outer && jointype != JOIN_INNER) + if (jointype == JOIN_SEMI || jointype == JOIN_ANTI) { /* - * When the referenced table is on the outer side of a non-inner - * join, knowing that each inner row has exactly one match is not - * as useful as one could wish, since we really need to know the - * fraction of outer rows with a match. Still, we can avoid the - * folly of multiplying the per-column estimates together. Take - * the smallest per-column selectivity, instead. (This should - * correspond to the FK column with the most nulls.) + * For JOIN_SEMI and JOIN_ANTI, we only get here when the FK's + * referenced table is exactly the inside of the join. The join + * selectivity is defined as the fraction of LHS rows that have + * matches. The FK implies that every LHS row has a match *in the + * referenced table*; but any restriction clauses on it will + * reduce the number of matches. Hence we take the join + * selectivity as equal to the selectivity of the table's + * restriction clauses, which is rows / tuples; but we must guard + * against tuples == 0. */ - use_smallest_selectivity = true; - } - else if (jointype == JOIN_SEMI || jointype == JOIN_ANTI) - { - /* - * For JOIN_SEMI and JOIN_ANTI, the selectivity is defined as the - * fraction of LHS rows that have matches. The referenced table - * is on the inner side (we already handled the other case above), - * so the FK implies that every LHS row has a match *in the - * referenced table*. But any restriction or join clauses below - * here will reduce the number of matches. - */ - if (bms_membership(inner_relids) == BMS_SINGLETON) - { - /* - * When the inner side of the semi/anti join is just the - * referenced table, we may take the FK selectivity as equal - * to the selectivity of the table's restriction clauses. - */ - RelOptInfo *ref_rel = find_base_rel(root, fkinfo->ref_relid); - double ref_tuples = Max(ref_rel->tuples, 1.0); + RelOptInfo *ref_rel = find_base_rel(root, fkinfo->ref_relid); + double ref_tuples = Max(ref_rel->tuples, 1.0); - fkselec *= ref_rel->rows / ref_tuples; - } - else - { - /* - * When the inner side of the semi/anti join is itself a join, - * it's hard to guess what fraction of the referenced table - * will get through the join. But we still don't want to - * multiply per-column estimates together. Take the smallest - * per-column selectivity, instead. - */ - use_smallest_selectivity = true; - } + fkselec *= ref_rel->rows / ref_tuples; } else { @@ -4512,26 +4495,6 @@ get_foreign_key_join_selectivity(PlannerInfo *root, fkselec *= 1.0 / ref_tuples; } - - /* - * Common code for cases where we should use the smallest selectivity - * that would be computed for any one of the FK's clauses. - */ - if (use_smallest_selectivity) - { - Selectivity thisfksel = 1.0; - - foreach(cell, removedlist) - { - RestrictInfo *rinfo = (RestrictInfo *) lfirst(cell); - Selectivity csel; - - csel = clause_selectivity(root, (Node *) rinfo, - 0, jointype, sjinfo); - thisfksel = Min(thisfksel, csel); - } - fkselec *= thisfksel; - } } *restrictlist = worklist; |