在PostgreSQL的源代码中,有如下调用关系:
query_planner
-->generate_base_implied_equalities
-->generate_base_implied_qualities_const
-->process_implied_equality
-->distribute_qual_to_rels
distribute_qual_to_rels的函数定义如下:
static void distribute_qual_to_rels(PlannerInfo *root, Node *clause, bool is_deduced, bool below_outer_join, JoinType jointype, Relids qualscope, Relids ojscope, Relids outerjoin_nonnullable) { ... /*---------- * Check to see if clause application must be delayed by outer-join * considerations. * * A word about is_pushed_down: we mark the qual as "pushed down" if * it is (potentially) applicable at a level different from its original * syntactic level. This flag is used to distinguish OUTER JOIN ON quals * from other quals pushed down to the same joinrel. The rules are: * WHERE quals and INNER JOIN quals: is_pushed_down = true. * Non-degenerate OUTER JOIN quals: is_pushed_down = false. * Degenerate OUTER JOIN quals: is_pushed_down = true. * A "degenerate" OUTER JOIN qual is one that doesn't mention the * non-nullable side, and hence can be pushed down into the nullable side * without changing the join result. It is correct to treat it as a * regular filter condition at the level where it is evaluated. * * Note: it is not immediately obvious that a simple boolean is enough * for this: if for some reason we were to attach a degenerate qual to * its original join level, it would need to be treated as an outer join * qual there. However, this cannot happen, because all the rels the * clause mentions must be in the outer join's min_righthand, therefore * the join it needs must be formed before the outer join; and we always * attach quals to the lowest level where they can be evaluated. But * if we were ever to re-introduce a mechanism for delaying evaluation * of "expensive" quals, this area would need work. *---------- */ if (is_deduced) { /* * If the qual came from implied-equality deduction, it should not be * outerjoin-delayed, else deducer blew it. But we can't check this * because the join_info_list may now contain OJs above where the qual * belongs. */ Assert(!ojscope); is_pushed_down = true; ... } else if (bms_overlap(relids, outerjoin_nonnullable)) { /* * The qual is attached to an outer join and mentions (some of the) * rels on the nonnullable side, so it's not degenerate. * * We can't use such a clause to deduce equivalence (the left and * right sides might be unequal above the join because one of them has * gone to NULL) ... but we might be able to use it for more limited * deductions, if it is mergejoinable. So consider adding it to the * lists of set-aside outer-join clauses. */ is_pushed_down = false; ... } else { /* * Normal qual clause or degenerate outer-join clause. Either way, we * can mark it as pushed-down. */ is_pushed_down = true; ... } ... }
此时,通过上述调用关系来调用 distribute_qual_to_rels的时候:是这样的:is_deduced参数为真
void process_implied_equality(PlannerInfo *root, Oid opno, Oid collation, Expr *item1, Expr *item2, Relids qualscope, bool below_outer_join, bool both_const) {
...
/*
* Push the new clause into all the appropriate restrictinfo lists.
*/
distribute_qual_to_rels(root, (Node *) clause,
true, below_outer_join, JOIN_INNER,
qualscope, NULL, NULL);
...
}
此时,is_pushed_down会被设置为true。
那么,何时会触发此种调用关系呢(由于下面的 and c.cust_id=2 部分的存在而导致):
postgres=# select * from sales s ,customers c where s.cust_id = c.cust_id and c.cust_id=2; cust_id | item | cust_id | cust_name ---------+--------+---------+----------- 2 | camera | 2 | John Doe (1 row) postgres=#
继续追击,看看什么条件会触发 distribute_qual_to_rels 函数的
else if (bms_overlap(relids, outerjoin_nonnullable)) 分支 和 else 分支:
调查结果:
else if (bms_overlap(relids, outerjoin_nonnullable)) 分支
postgres=# select * from sales s left outer join customers c on s.cust_id = c.cust_id; cust_id | item | cust_id | cust_name ---------+----------+---------+----------- 2 | camera | 2 | John Doe 3 | computer | 3 | Jane Doe 3 | monitor | 3 | Jane Doe 4 | printer | | (4 rows) postgres=#
或者
postgres=# select * from sales s full outer join customers c on s.cust_id = c.cust_id; cust_id | item | cust_id | cust_name ---------+----------+---------+----------- | | 1 | craig 2 | camera | 2 | John Doe 3 | computer | 3 | Jane Doe 3 | monitor | 3 | Jane Doe 4 | printer | | (5 rows) postgres=#
else分支:
postgres=# select * from sales s inner join customers c on s.cust_id = c.cust_id; cust_id | item | cust_id | cust_name ---------+----------+---------+----------- 2 | camera | 2 | John Doe 3 | computer | 3 | Jane Doe 3 | monitor | 3 | Jane Doe (3 rows) postgres=#