对PostgreSQL源代码中的is_pushed_down的理解

在PostgreSQL的源代码中,有如下调用关系:

query_planner
  -->generate_base_implied_equalities
        -->generate_base_implied_qualities_const
             -->process_implied_equality
                   -->distribute_qual_to_rels

distribute_qual_to_rels的函数定义如下:

对PostgreSQL源代码中的is_pushed_down的理解
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;

        ...
    }
    ...
}
对PostgreSQL源代码中的is_pushed_down的理解

此时,通过上述调用关系来调用 distribute_qual_to_rels的时候:是这样的:is_deduced参数为真

对PostgreSQL源代码中的is_pushed_down的理解
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);
    ...
}

对PostgreSQL源代码中的is_pushed_down的理解

此时,is_pushed_down会被设置为true。

那么,何时会触发此种调用关系呢(由于下面的 and c.cust_id=2 部分的存在而导致):

对PostgreSQL源代码中的is_pushed_down的理解
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=# 
对PostgreSQL源代码中的is_pushed_down的理解

 继续追击,看看什么条件会触发 distribute_qual_to_rels 函数的 

else if (bms_overlap(relids, outerjoin_nonnullable)) 分支 和  else 分支:

调查结果:

else if (bms_overlap(relids, outerjoin_nonnullable)) 分支

对PostgreSQL源代码中的is_pushed_down的理解
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=# 
对PostgreSQL源代码中的is_pushed_down的理解

或者

对PostgreSQL源代码中的is_pushed_down的理解
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=# 
对PostgreSQL源代码中的is_pushed_down的理解

else分支:

对PostgreSQL源代码中的is_pushed_down的理解
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=# 
对PostgreSQL源代码中的is_pushed_down的理解





上一篇:Microsoft 365 DevDays 开发者大会内容速预览// 5th


下一篇:Linux远程给win7发弹窗告警通知