ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL 源码解读(230)- 查询#123(NOT IN实现)

PostgreSQL 源码解读(230)- 查询#123(NOT IN实现)

原创 PostgreSQL 作者:husthxd 时间:2019-10-16 16:14:48 1 删除 编辑

本节介绍了PostgreSQL含有NOT IN查询语句实现中的ExecScanSubPlan函数.

一、数据结构

SubPlanState
子计划运行期状态


/* ----------------
 *        SubPlanState node
 * ----------------
 */
typedef struct SubPlanState
{
    NodeTag        type;
    SubPlan    *subplan;        /* expression plan node */
    struct PlanState *planstate;    /* subselect plan's state tree */
    struct PlanState *parent;    /* parent plan node's state tree */
    ExprState  *testexpr;        /* 组合表达式状态;state of combining expression */
    List       *args;            /* 参数表达式状态;states of argument expression(s) */
    HeapTuple    curTuple;        /* subplan最近的元组;copy of most recent tuple from subplan */
    Datum        curArray;        /* most recent array from ARRAY() subplan */
    /* these are used when hashing the subselect's output: */
    TupleDesc    descRight;        /* 投影后的子查询描述符;subselect desc after projection */
    ProjectionInfo *projLeft;    /* for projecting lefthand exprs */
    ProjectionInfo *projRight;    /* for projecting subselect output */
    TupleHashTable hashtable;    /* hash table for no-nulls subselect rows */
    TupleHashTable hashnulls;    /* hash table for rows with null(s) */
    bool        havehashrows;    /* true if hashtable is not empty */
    bool        havenullrows;    /* true if hashnulls is not empty */
    MemoryContext hashtablecxt; /* memory context containing hash tables */
    MemoryContext hashtempcxt;    /* temp memory context for hash tables */
    ExprContext *innerecontext; /* econtext for computing inner tuples */
    AttrNumber *keyColIdx;        /* control data for hash tables */
    Oid           *tab_eq_funcoids;    /* equality func oids for table
                                     * datatype(s) */
    Oid           *tab_collations; /* collations for hash and comparison */
    FmgrInfo   *tab_hash_funcs; /* hash functions for table datatype(s) */
    FmgrInfo   *tab_eq_funcs;    /* equality functions for table datatype(s) */
    FmgrInfo   *lhs_hash_funcs; /* hash functions for lefthand datatype(s) */
    FmgrInfo   *cur_eq_funcs;    /* equality functions for LHS vs. table */
    ExprState  *cur_eq_comp;    /* equality comparator for LHS vs. table */
} SubPlanState;

SubPlan
子查询计划


/*
 * SubPlan - executable expression node for a subplan (sub-SELECT)
 *
 * The planner replaces SubLink nodes in expression trees with SubPlan
 * nodes after it has finished planning the subquery.  SubPlan references
 * a sub-plantree stored in the subplans list of the toplevel PlannedStmt.
 * (We avoid a direct link to make it easier to copy expression trees
 * without causing multiple processing of the subplan.)
 * 查询规划器在完成子查询的规划后使用SubPlan节点替换表达式树中的SubLink节点。
 * SubPlan引用了存储在高层PlannedStmt中的subplans链表中的sub-plantree。
 * (避免使用直接链接,从而使得拷贝表达式树相对比较简单)
 *
 * In an ordinary subplan, testexpr points to an executable expression
 * (OpExpr, an AND/OR tree of OpExprs, or RowCompareExpr) for the combining
 * operator(s); the left-hand arguments are the original lefthand expressions,
 * and the right-hand arguments are PARAM_EXEC Param nodes representing the
 * outputs of the sub-select.  (NOTE: runtime coercion functions may be
 * inserted as well.)  This is just the same expression tree as testexpr in
 * the original SubLink node, but the PARAM_SUBLINK nodes are replaced by
 * suitably numbered PARAM_EXEC nodes.
 * 常规情况下,testexpr指向用于组合操作的可执行表达式(OpExpr、OpExprs的AND/OR树或者RowCompareExpr);
 * 左参数是原始的左表达式,右参数是PARAM_EXEC参数节点用以表示子查询的输出。
 * 与原始SubLink节点的testexpr具有相同的表达式树,但PARAM_SUBLINK节点则使用合适的已编号PARAM_EXEC节点替代。
 *
 * If the sub-select becomes an initplan rather than a subplan, the executable
 * expression is part of the outer plan's expression tree (and the SubPlan
 * node itself is not, but rather is found in the outer plan's initPlan
 * list).  In this case testexpr is NULL to avoid duplication.
 * 如果子查询成了initplan而不是subplan,可执行的表达式是外层plan表达式树的一部分。
 * 这种情况下,testexpr为NULL以避免重复。
 *
 * The planner also derives lists of the values that need to be passed into
 * and out of the subplan.  Input values are represented as a list "args" of
 * expressions to be evaluated in the outer-query context (currently these
 * args are always just Vars, but in principle they could be any expression).
 * The values are assigned to the global PARAM_EXEC params indexed by parParam
 * (the parParam and args lists must have the same ordering).  setParam is a
 * list of the PARAM_EXEC params that are computed by the sub-select, if it
 * is an initplan; they are listed in order by sub-select output column
 * position.  (parParam and setParam are integer Lists, not Bitmapsets,
 * because their ordering is significant.)
 * 规划器还派生了需要传入和传出子计划的值的链表。
 * 输入值标识位表达式的“args”链表,在外层查询上下文中进行解析。
 * (这些args通常是Vars,但原则上它们可以是任意表达式)
 * 这些值以parParam为索引给全局PARAM_EXEC参数赋值。
 * setParam是PARAM_EXEC参数链表,通过子查询(如为initplan)计算所得。
 * 它们按子查询输出列的位置进行排序组织为链表形式。
 * (parParam和setParam是整型链表,而不是Bitmapsets链表)
 *
 * Also, the planner computes startup and per-call costs for use of the
 * SubPlan.  Note that these include the cost of the subquery proper,
 * evaluation of the testexpr if any, and any hashtable management overhead.
 * 同时,规划器计算SubPlan启动和每次调用的成本。注意:包括子查询正常解析testexpr的成本以及哈希表管理成本。
 */
typedef struct SubPlan
{
    Expr        xpr;//表达式
    /* Fields copied from original SubLink: */
    //从SubLink中拷贝而来
    SubLinkType subLinkType;    /* see above */
    /* The combining operators, transformed to an executable expression: */
    //组合操作符,转换为可执行的表达式
    Node       *testexpr;        /* OpExpr or RowCompareExpr expression tree */
    List       *paramIds;        /* 参数IDs;IDs of Params embedded in the above */
    /* Identification of the Plan tree to use: */
    //Plan tree标识
    int            plan_id;        /* Index (from 1) in PlannedStmt.subplans */
    /* Identification of the SubPlan for EXPLAIN and debugging purposes: */
    //EXPLAIN和debug目的的SubPlan标识
    char       *plan_name;        /* A name assigned during planning */
    /* Extra data useful for determining subplan's output type: */
    //用于确定subplan输出类型的额外信息
    Oid            firstColType;    /* subplan结果的第一个列类型;Type of first column of subplan result */
    int32        firstColTypmod; /* 第一列的Typmod;Typmod of first column of subplan result */
    Oid            firstColCollation;    /* 第一列的Collation;Collation of first column of subplan
                                     * result */
    /* Information about execution strategy: */
    //执行阶段的相关信息
    bool        useHashTable;    /* 是否使用哈希表存储子查询输出;true to store subselect output in a hash
                                 * table (implies we are doing "IN") */
    bool        unknownEqFalse; /* 如OK为T,如为未知则为F;快速处理null值;true if it's okay to return FALSE when the
                                 * spec result is UNKNOWN; this allows much
                                 * simpler handling of null values */
    bool        parallel_safe;    /* 是否并行安全?is the subplan parallel-safe? */
    /* Note: parallel_safe does not consider contents of testexpr or args */
    /* Information for passing params into and out of the subselect: */
    //用于给子查询传入和传出参数的信息
    /* setParam and parParam are lists of integers (param IDs) */
    //setParam和parParam是整型链表(param IDs)
    List       *setParam;        /* initplan subqueries have to set these
                                 * Params for parent plan */
    List       *parParam;        /* indices of input Params from parent plan */
    List       *args;            /* 以parParam值进行传递的表达式;exprs to pass as parParam values */
    /* Estimated execution costs: */
    //估算执行成本
    Cost        startup_cost;    /* one-time setup cost */
    Cost        per_call_cost;    /* cost for each subplan evaluation */
} SubPlan;

SubLinkType
SubLink类型


/*
 * SubLink
 *
 * A SubLink represents a subselect appearing in an expression, and in some
 * cases also the combining operator(s) just above it.  The subLinkType
 * indicates the form of the expression represented:
 *    EXISTS_SUBLINK        EXISTS(SELECT ...)
 *    ALL_SUBLINK            (lefthand) op ALL (SELECT ...)
 *    ANY_SUBLINK            (lefthand) op ANY (SELECT ...)
 *    ROWCOMPARE_SUBLINK    (lefthand) op (SELECT ...)
 *    EXPR_SUBLINK        (SELECT with single targetlist item ...)
 *    MULTIEXPR_SUBLINK    (SELECT with multiple targetlist items ...)
 *    ARRAY_SUBLINK        ARRAY(SELECT with single targetlist item ...)
 *    CTE_SUBLINK            WITH query (never actually part of an expression)
 * For ALL, ANY, and ROWCOMPARE, the lefthand is a list of expressions of the
 * same length as the subselect's targetlist.  ROWCOMPARE will *always* have
 * a list with more than one entry; if the subselect has just one target
 * then the parser will create an EXPR_SUBLINK instead (and any operator
 * above the subselect will be represented separately).
 * ROWCOMPARE, EXPR, and MULTIEXPR require the subselect to deliver at most
 * one row (if it returns no rows, the result is NULL).
 * ALL, ANY, and ROWCOMPARE require the combining operators to deliver boolean
 * results.  ALL and ANY combine the per-row results using AND and OR
 * semantics respectively.
 * ARRAY requires just one target column, and creates an array of the target
 * column's type using any number of rows resulting from the subselect.
 *
 * SubLink is classed as an Expr node, but it is not actually executable;
 * it must be replaced in the expression tree by a SubPlan node during
 * planning.
 *
 * NOTE: in the raw output of gram.y, testexpr contains just the raw form
 * of the lefthand expression (if any), and operName is the String name of
 * the combining operator.  Also, subselect is a raw parsetree.  During parse
 * analysis, the parser transforms testexpr into a complete boolean expression
 * that compares the lefthand value(s) to PARAM_SUBLINK nodes representing the
 * output columns of the subselect.  And subselect is transformed to a Query.
 * This is the representation seen in saved rules and in the rewriter.
 *
 * In EXISTS, EXPR, MULTIEXPR, and ARRAY SubLinks, testexpr and operName
 * are unused and are always null.
 *
 * subLinkId is currently used only for MULTIEXPR SubLinks, and is zero in
 * other SubLinks.  This number identifies different multiple-assignment
 * subqueries within an UPDATE statement's SET list.  It is unique only
 * within a particular targetlist.  The output column(s) of the MULTIEXPR
 * are referenced by PARAM_MULTIEXPR Params appearing elsewhere in the tlist.
 *
 * The CTE_SUBLINK case never occurs in actual SubLink nodes, but it is used
 * in SubPlans generated for WITH subqueries.
 */
typedef enum SubLinkType
{
    EXISTS_SUBLINK,
    ALL_SUBLINK,
    ANY_SUBLINK,
    ROWCOMPARE_SUBLINK,
    EXPR_SUBLINK,
    MULTIEXPR_SUBLINK,
    ARRAY_SUBLINK,
    CTE_SUBLINK                    /* for SubPlans only */
} SubLinkType;

SubLink
SubLink结构体


typedef struct SubLink
{
    Expr        xpr;
    SubLinkType subLinkType;    /* see above */
    int            subLinkId;        /* ID (1..n); 0 if not MULTIEXPR */
    Node       *testexpr;        /* outer-query test for ALL/ANY/ROWCOMPARE */
    List       *operName;        /* originally specified operator name */
    Node       *subselect;        /* subselect as Query* or raw parsetree */
    int            location;        /* token location, or -1 if unknown */
} SubLink;

二、源码解读

ExecScanSubPlan


/*
 * ExecScanSubPlan: default case where we have to rescan subplan each time
 * 默认情况下每次都不得不重新扫描subplan
 */
static Datum
ExecScanSubPlan(SubPlanState *node,
                ExprContext *econtext,
                bool *isNull)
{
    SubPlan    *subplan = node->subplan;//子计划
    PlanState  *planstate = node->planstate;//计划运行期状态
    SubLinkType subLinkType = subplan->subLinkType;//子链接类型
    MemoryContext oldcontext;//原内存上下文
    TupleTableSlot *slot;//元组slot
    Datum        result;//结果指针
    bool        found = false;    /* 如找到至少一个元组,则返回T;true if got at least one subplan tuple */
    ListCell   *pvar;//临时变量
    ListCell   *l;//临时变量
    ArrayBuildStateAny *astate = NULL;//
    /*
     * MULTIEXPR subplans, when "executed", just return NULL; but first we
     * mark the subplan's output parameters as needing recalculation.  (This
     * is a bit of a hack: it relies on the subplan appearing later in its
     * targetlist than any of the referencing Params, so that all the Params
     * have been evaluated before we re-mark them for the next evaluation
     * cycle.  But in general resjunk tlist items appear after non-resjunk
     * ones, so this should be safe.)  Unlike ExecReScanSetParamPlan, we do
     * *not* set bits in the parent plan node's chgParam, because we don't
     * want to cause a rescan of the parent.
     *
     * MULTIEXPR处理逻辑
     */
    if (subLinkType == MULTIEXPR_SUBLINK)
    {
        EState       *estate = node->parent->state;
        foreach(l, subplan->setParam)
        {
            int            paramid = lfirst_int(l);
            ParamExecData *prm = &(estate->es_param_exec_vals[paramid]);
            prm->execPlan = node;
        }
        *isNull = true;
        return (Datum) 0;
    }
    /* Initialize ArrayBuildStateAny in caller's context, if needed */
    //数组
    if (subLinkType == ARRAY_SUBLINK)
        astate = initArrayResultAny(subplan->firstColType,
                                    CurrentMemoryContext, true);
    /*
     * We are probably in a short-lived expression-evaluation context. Switch
     * to the per-query context for manipulating the child plan's chgParam,
     * calling ExecProcNode on it, etc.
     */
    //切换上下文
    oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
    /*
     * Set Params of this plan from parent plan correlation values. (Any
     * calculation we have to do is done in the parent econtext, since the
     * Param values don't need to have per-query lifetime.)
     */
    //通过父计划相关值中设置子计划参数
    Assert(list_length(subplan->parParam) == list_length(node->args));
    forboth(l, subplan->parParam, pvar, node->args)
    {
        int            paramid = lfirst_int(l);
        ParamExecData *prm = &(econtext->ecxt_param_exec_vals[paramid]);
        prm->value = ExecEvalExprSwitchContext((ExprState *) lfirst(pvar),
                                               econtext,
                                               &(prm->isnull));
        planstate->chgParam = bms_add_member(planstate->chgParam, paramid);
    }
    /*
     * Now that we've set up its parameters, we can reset the subplan.
     */
    //执行ReScan
    //Reset a plan node so that its output can be re-scanned.
    ExecReScan(planstate);
    /*
     * For all sublink types except EXPR_SUBLINK and ARRAY_SUBLINK, the result
     * is boolean as are the results of the combining operators. We combine
     * results across tuples (if the subplan produces more than one) using OR
     * semantics for ANY_SUBLINK or AND semantics for ALL_SUBLINK.
     * (ROWCOMPARE_SUBLINK doesn't allow multiple tuples from the subplan.)
     * NULL results from the combining operators are handled according to the
     * usual SQL semantics for OR and AND.  The result for no input tuples is
     * FALSE for ANY_SUBLINK, TRUE for ALL_SUBLINK, NULL for
     * ROWCOMPARE_SUBLINK.
     * 除EXPR_SUBLINK和ARRAY_SUBLINK外的所有sublink,结果是布尔值(组合运算符的结果).
     * PG通过跨元组(如子计划产生多个元组)合并结果,对于ANY_SUBLINK使用OR语义,ALL_SUBLINK则使用AND语义.
     * (ROWCOMPARE_SUBLINK不允许子计划返回多个元组)
     * 从组合操作符中返回的NULL遵循SQL中的OR和AND语义.
     * 如没有输入元组,ANY_SUBLINK为FALSE,ALL_SUBLINK为TRUE,ROWCOMPARE_SUBLINK为NULL.
     *
     * For EXPR_SUBLINK we require the subplan to produce no more than one
     * tuple, else an error is raised.  If zero tuples are produced, we return
     * NULL.  Assuming we get a tuple, we just use its first column (there can
     * be only one non-junk column in this case).
     * 对于EXPR_SUBLINK,需要subplan产生不超过一个元组,否则报错.如果没有元组产生,返回NULL.
     * 假定获取到一个元组,则使用第一个列(这种情况下只有一个non-junk列).
     *
     * For ARRAY_SUBLINK we allow the subplan to produce any number of tuples,
     * and form an array of the first column's values.  Note in particular
     * that we produce a zero-element array if no tuples are produced (this is
     * a change from pre-8.3 behavior of returning NULL).
     * 对于ARRAY_SUBLINK,允许subplan产生任意数目的元组,使用第一个列值组成数组.
     * 特别注意的是如没有元组产生则产生0个元素的数组(8.3以前是返回NULL).
     */
    result = BoolGetDatum(subLinkType == ALL_SUBLINK);//ALL为T,否则为F
    *isNull = false;
    for (slot = ExecProcNode(planstate);
         !TupIsNull(slot);
         slot = ExecProcNode(planstate))//循环获取元组,直至没有元组为NULL(即已完成)
    {
        //元组描述符
        TupleDesc    tdesc = slot->tts_tupleDescriptor;
        Datum        rowresult;//结果
        bool        rownull;//是否为空?
        int            col;//列计数器
        ListCell   *plst;//临时变量
        if (subLinkType == EXISTS_SUBLINK)//EXISTS
        {
            found = true;
            result = BoolGetDatum(true);
            break;
        }
        if (subLinkType == EXPR_SUBLINK)//EXPR表达式
        {
            /* cannot allow multiple input tuples for EXPR sublink */
            if (found)
                ereport(ERROR,
                        (errcode(ERRCODE_CARDINALITY_VIOLATION),
                         errmsg("more than one row returned by a subquery used as an expression")));
            found = true;
            /*
             * We need to copy the subplan's tuple in case the result is of
             * pass-by-ref type --- our return value will point into this
             * copied tuple!  Can't use the subplan's instance of the tuple
             * since it won't still be valid after next ExecProcNode() call.
             * node->curTuple keeps track of the copied tuple for eventual
             * freeing.
             */
            if (node->curTuple)
                heap_freetuple(node->curTuple);
            node->curTuple = ExecCopySlotHeapTuple(slot);
            result = heap_getattr(node->curTuple, 1, tdesc, isNull);
            /* keep scanning subplan to make sure there's only one tuple */
            continue;
        }
        if (subLinkType == ARRAY_SUBLINK)//数组
        {
            Datum        dvalue;
            bool        disnull;
            found = true;
            /* stash away current value */
            Assert(subplan->firstColType == TupleDescAttr(tdesc, 0)->atttypid);
            dvalue = slot_getattr(slot, 1, &disnull);
            astate = accumArrayResultAny(astate, dvalue, disnull,
                                         subplan->firstColType, oldcontext);
            /* keep scanning subplan to collect all values */
            continue;
        }
        /* cannot allow multiple input tuples for ROWCOMPARE sublink either */
        if (subLinkType == ROWCOMPARE_SUBLINK && found)//行比较
            ereport(ERROR,
                    (errcode(ERRCODE_CARDINALITY_VIOLATION),
                     errmsg("more than one row returned by a subquery used as an expression")));
        found = true;//初始为T
        /*
         * For ALL, ANY, and ROWCOMPARE sublinks, load up the Params
         * representing the columns of the sub-select, and then evaluate the
         * combining expression.
         * 对于ALL,ANY和ROWCOMPARE子链接,加载表示子查询列的Params,并解析组合表达式
         */
        col = 1;//列从1计数
        foreach(plst, subplan->paramIds)//循环遍历子查询参数
        {
            int            paramid = lfirst_int(plst);
            ParamExecData *prmdata;
            prmdata = &(econtext->ecxt_param_exec_vals[paramid]);
            Assert(prmdata->execPlan == NULL);
            //获取参数值
            prmdata->value = slot_getattr(slot, col, &(prmdata->isnull));
            //下一个列
            col++;
        }
        //解析表达式
        rowresult = ExecEvalExprSwitchContext(node->testexpr, econtext,
                                              &rownull);
        if (subLinkType == ANY_SUBLINK)
        {
            //ANY : 使用OR语义组合
            /* combine across rows per OR semantics */
            if (rownull)
                *isNull = true;
            else if (DatumGetBool(rowresult))
            {
                result = BoolGetDatum(true);
                *isNull = false;
                break;            /* needn't look at any more rows */
            }
        }
        else if (subLinkType == ALL_SUBLINK)
        {
            //ALL : 使用AND语义
            /* combine across rows per AND semantics */
            if (rownull)
                *isNull = true;
            else if (!DatumGetBool(rowresult))
            {
                result = BoolGetDatum(false);
                *isNull = false;
                break;            /* needn't look at any more rows */
            }
        }
        else
        {
            /* must be ROWCOMPARE_SUBLINK */
            //这里一定是ROWCOMPARE
            result = rowresult;
            *isNull = rownull;
        }
    }
    MemoryContextSwitchTo(oldcontext);
    if (subLinkType == ARRAY_SUBLINK)
    {
        /* We return the result in the caller's context */
        //在调用者上下文中返回结果
        result = makeArrayResultAny(astate, oldcontext, true);
    }
    else if (!found)
    {
        /*
         * deal with empty subplan result.  result/isNull were previously
         * initialized correctly for all sublink types except EXPR and
         * ROWCOMPARE; for those, return NULL.
         * subplan没有结果返回,设置result&isNull值
         */
        if (subLinkType == EXPR_SUBLINK ||
            subLinkType == ROWCOMPARE_SUBLINK)
        {
            result = (Datum) 0;
            *isNull = true;
        }
    }
    //返回结果
    return result;
}

ExecProcNode
执行输入参数node的ExecProcNode方法。

/* ----------------------------------------------------------------
 *        ExecProcNode
 *
 *        Execute the given node to return a(nother) tuple.
 * ----------------------------------------------------------------
 */
#ifndef FRONTEND
static inline TupleTableSlot *
ExecProcNode(PlanState *node)
{
    if (node->chgParam != NULL) /* something changed? */
        ExecReScan(node);        /* let ReScan handle this */
    return node->ExecProcNode(node);
}
#endif

三、跟踪分析

执行SQL:

[pg12@localhost ~]$ psql -d testdb
Timing is on.
Expanded display is used automatically.
psql (12.0)
Type "help" for help.
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select * from tbl;
 id | value 
----+-------
  1 |     2
(1 row)
Time: 2.678 ms
[local]:5432 pg12@testdb=# select count(*) from t_big_null;
  count   
----------
 10000001
(1 row)
Time: 679.972 ms
[local]:5432 pg12@testdb=# analyze tbl;
ANALYZE
Time: 64.442 ms
[local]:5432 pg12@testdb=# analyze t_big_null;
ANALYZE
Time: 434.702 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select pg_backend_pid();
 pg_backend_pid 
----------------
          18758
(1 row)
Time: 1.990 ms
[local]:5432 pg12@testdb=# select * from tbl a where a.id not in (select b.id from t_big_null b);

启动gdb跟踪

(gdb) b ExecScanSubPlan
Breakpoint 1 at 0x73014b: file nodeSubplan.c, line 228.
(gdb) c
Continuing.
Breakpoint 1, ExecScanSubPlan (node=0x1c89158, econtext=0x1c88990, isNull=0x1c88cad)
    at nodeSubplan.c:228
228        SubPlan    *subplan = node->subplan;
(gdb) 
(gdb) n
229        PlanState  *planstate = node->planstate;
(gdb) 
230        SubLinkType subLinkType = subplan->subLinkType;
(gdb) 
234        bool        found = false;    /* true if got at least one subplan tuple */

subplan变量值(结构体SubPlan)
其中testexpr是测试表达式,操作符是整型的等值比较,
左操作符是Var,数据表的第一个列,类型为23-int4(select * from pg_type where oid=23;)
有操作符是Param,执行期设置,对应参数索引编号为0,类型为23-int4
paramIds是链表,第一项ID值为0(int_value = 0)
子查询结果的第一个列类型为23-int4

(gdb) p *subplan
$1 = {xpr = {type = T_SubPlan}, subLinkType = ANY_SUBLINK, testexpr = 0x1cb8790, 
  paramIds = 0x1cb8758, plan_id = 1, plan_name = 0x1cb8a80 "SubPlan 1", 
  firstColType = 23, firstColTypmod = -1, firstColCollation = 0, useHashTable = false, 
  unknownEqFalse = false, parallel_safe = true, setParam = 0x0, parParam = 0x0, 
  args = 0x0, startup_cost = 0, per_call_cost = 129155.31875000001}
(gdb) p *subplan->testexpr
$2 = {type = T_OpExpr}
(gdb) p *(OpExpr *)subplan->testexpr
$3 = {xpr = {type = T_OpExpr}, opno = 96, opfuncid = 65, opresulttype = 16, 
  opretset = false, opcollid = 0, inputcollid = 0, args = 0x1cb8868, location = 31}
(gdb) set $expr=(OpExpr *)subplan->testexpr
(gdb) p *$expr->args
$4 = {type = T_List, length = 2, head = 0x1cb8840, tail = 0x1cb88d8}
(gdb) p *$expr->args->head
$5 = {data = {ptr_value = 0x1cb87e8, int_value = 30115816, oid_value = 30115816}, 
  next = 0x1cb88d8}
(gdb) p *(Node *)$expr->args->head->data.ptr_value
$6 = {type = T_Var}
(gdb) p *(Var *)$expr->args->head->data.ptr_value
$7 = {xpr = {type = T_Var}, varno = 1, varattno = 1, vartype = 23, vartypmod = -1, 
  varcollid = 0, varlevelsup = 0, varnoold = 1, varoattno = 1, location = 26}
(gdb) p *(Var *)$expr->args->head->next->data.ptr_value
$8 = {xpr = {type = T_Param}, varno = 1, varattno = 0, vartype = 23, vartypmod = -1, 
  varcollid = 0, varlevelsup = 4294967295, varnoold = 2139062142, varoattno = 16, 
  location = 0}
(gdb) p *(Param *)$expr->args->head->next->data.ptr_value
$9 = {xpr = {type = T_Param}, paramkind = PARAM_EXEC, paramid = 0, paramtype = 23, 
  paramtypmod = -1, paramcollid = 0, location = -1}
(gdb) 
###
[local]:5432 pg12@testdb=# select * from pg_proc where oid = 65; --> opfuncid = 65,函数
-[ RECORD 1 ]---+-------
oid             | 65
proname         | int4eq
pronamespace    | 11
proowner        | 10
prolang         | 12
procost         | 1
prorows         | 0
provariadic     | 0
prosupport      | -
prokind         | f
prosecdef       | f
proleakproof    | t
proisstrict     | t
proretset       | f
provolatile     | i
proparallel     | s
pronargs        | 2
pronargdefaults | 0
prorettype      | 16
proargtypes     | 23 23
proallargtypes  | 
proargmodes     | 
proargnames     | 
proargdefaults  | 
protrftypes     | 
prosrc          | int4eq
probin          | 
proconfig       | 
proacl          | 
Time: 6.253 ms
###
(gdb) p *subplan->paramIds
$11 = {type = T_IntList, length = 1, head = 0x1cb8730, tail = 0x1cb8730}
(gdb) p *subplan->paramIds->head
$12 = {data = {ptr_value = 0x7f7f7f7f00000000, int_value = 0, oid_value = 0}, next = 0x0}
(gdb)

planstate变量值,实际的执行节点是ExecMaterial

(gdb) p *planstate
$15 = {type = T_MaterialState, plan = 0x1cb8900, state = 0x1c87da8, 
  ExecProcNode = 0x6f802a <ExecProcNodeFirst>, 
  ExecProcNodeReal = 0x720ecf <ExecMaterial>, instrument = 0x0, worker_instrument = 0x0, 
  worker_jit_instrument = 0x0, qual = 0x0, lefttree = 0x1c88160, righttree = 0x0, 
  initPlan = 0x0, subPlan = 0x0, chgParam = 0x0, ps_ResultTupleDesc = 0x1c88580, 
  ps_ResultTupleSlot = 0x1c88698, ps_ExprContext = 0x0, ps_ProjInfo = 0x0, 
  scandesc = 0x1c88468, scanops = 0xc3e720 <TTSOpsMinimalTuple>, outerops = 0x0, 
  innerops = 0x0, resultops = 0xc3e720 <TTSOpsMinimalTuple>, scanopsfixed = true, 
  outeropsfixed = false, inneropsfixed = false, resultopsfixed = true, scanopsset = true, 
  outeropsset = false, inneropsset = false, resultopsset = true}
(gdb)

subLinkType变量,类型为ANY_SUBLINK,把NOT IN转为ANY_SUBLINK

(gdb) p subLinkType
$16 = ANY_SUBLINK

设置变量,执行相关检查等

(gdb) n
237        ArrayBuildStateAny *astate = NULL;
(gdb) 
250        if (subLinkType == MULTIEXPR_SUBLINK)
(gdb) 
266        if (subLinkType == ARRAY_SUBLINK)
(gdb) 
275        oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
(gdb) 
282        Assert(list_length(subplan->parParam) == list_length(node->args));
(gdb) 
284        forboth(l, subplan->parParam, pvar, node->args)
(gdb) p subplan->parParam
$17 = (List *) 0x0

执行ExecReScan,planstate->ExecProcNode实际为ExecMaterial

(gdb) n
298        ExecReScan(planstate);
(gdb) p *planstate
$20 = {type = T_MaterialState, plan = 0x1cb8900, state = 0x1c87da8, 
  ExecProcNode = 0x720ecf <ExecMaterial>, ExecProcNodeReal = 0x720ecf <ExecMaterial>, 
  instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0, qual = 0x0, 
  lefttree = 0x1c88160, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, chgParam = 0x0, 
  ps_ResultTupleDesc = 0x1c88580, ps_ResultTupleSlot = 0x1c88698, ps_ExprContext = 0x0, 
  ps_ProjInfo = 0x0, scandesc = 0x1c88468, scanops = 0xc3e720 <TTSOpsMinimalTuple>, 
  outerops = 0x0, innerops = 0x0, resultops = 0xc3e720 <TTSOpsMinimalTuple>, 
  scanopsfixed = true, outeropsfixed = false, inneropsfixed = false, 
  resultopsfixed = true, scanopsset = true, outeropsset = false, inneropsset = false, 
  resultopsset = true}
(gdb)

初始化result&isNull变量

(gdb) n
321        result = BoolGetDatum(subLinkType == ALL_SUBLINK);
(gdb) 
322        *isNull = false;
(gdb) p result
$18 = 0

执行循环,slot从planstate->ExecProcNode(即ExecMaterial)中获取

(gdb) n
324        for (slot = ExecProcNode(planstate);
(gdb) 
325             !TupIsNull(slot);
(gdb) 
(gdb) p *slot
$22 = {type = T_TupleTableSlot, tts_flags = 20, tts_nvalid = 0, 
  tts_ops = 0xc3e720 <TTSOpsMinimalTuple>, tts_tupleDescriptor = 0x1c88580, 
  tts_values = 0x1c88708, tts_isnull = 0x1c88710, tts_mcxt = 0x1c87c90, tts_tid = {
    ip_blkid = {bi_hi = 65535, bi_lo = 65535}, ip_posid = 0}, tts_tableOid = 0}
(gdb) p slot->tts_values[0]
$23 = 0
(gdb)

获取slot的描述符

(gdb) 
328            TupleDesc    tdesc = slot->tts_tupleDescriptor;
(gdb) p tdesc
$21 = (TupleDesc) 0x206e6f6900000000

判断subLink类型执行相关逻辑,循环paramIds,填充参数值

(gdb) n
334            if (subLinkType == EXISTS_SUBLINK)
(gdb) 
341            if (subLinkType == EXPR_SUBLINK)
(gdb) 
367            if (subLinkType == ARRAY_SUBLINK)
(gdb) 
383            if (subLinkType == ROWCOMPARE_SUBLINK && found)
(gdb) 
388            found = true;
(gdb) 
395            col = 1;
(gdb) 
396            foreach(plst, subplan->paramIds)
(gdb) p *slot
$22 = {type = T_TupleTableSlot, tts_flags = 20, tts_nvalid = 0, 
  tts_ops = 0xc3e720 <TTSOpsMinimalTuple>, tts_tupleDescriptor = 0x1c88580, 
  tts_values = 0x1c88708, tts_isnull = 0x1c88710, tts_mcxt = 0x1c87c90, tts_tid = {
    ip_blkid = {bi_hi = 65535, bi_lo = 65535}, ip_posid = 0}, tts_tableOid = 0}
(gdb) p *slot->tts_values[0]
Cannot access memory at address 0x0
(gdb) p slot->tts_values[0]
$23 = 0
(gdb) n
398                int            paramid = lfirst_int(plst);
(gdb) 
401                prmdata = &(econtext->ecxt_param_exec_vals[paramid]);
(gdb) p paramid
$24 = 0
(gdb) n
402                Assert(prmdata->execPlan == NULL);
(gdb) p *prmdata
$25 = {execPlan = 0x0, value = 0, isnull = false}
(gdb) n
403                prmdata->value = slot_getattr(slot, col, &(prmdata->isnull));
(gdb) n
404                col++;
(gdb) p *prmdata
$26 = {execPlan = 0x0, value = 2, isnull = false}
(gdb) n
396            foreach(plst, subplan->paramIds)
(gdb)

解析表达式,亦即解析表达式tbl.id = 2,获取结果

(gdb) 
407            rowresult = ExecEvalExprSwitchContext(node->testexpr, econtext,
(gdb) p *(Var *)$expr->args->head->data.ptr_value
$28 = {xpr = {type = T_Var}, varno = 1, varattno = 1, vartype = 23, vartypmod = -1, 
  varcollid = 0, varlevelsup = 0, varnoold = 1, varoattno = 1, location = 26}
(gdb)  p *(Param *)$expr->args->head->next->data.ptr_value
$29 = {xpr = {type = T_Param}, paramkind = PARAM_EXEC, paramid = 0, paramtype = 23, 
  paramtypmod = -1, paramcollid = 0, location = -1}
(gdb)  p econtext->ecxt_param_exec_vals[0]
$30 = {execPlan = 0x0, value = 2, isnull = false}

由于tbl.id = 1,因此表达式解析结果为F

(gdb) n
410            if (subLinkType == ANY_SUBLINK)
(gdb) p rowresult
$31 = 0
(gdb)

判断subLink类型,如为ANY则进入相应逻辑

(gdb) n
410            if (subLinkType == ANY_SUBLINK)
(gdb) p rowresult
$31 = 0
(gdb) n
413                if (rownull)
(gdb) p rownull
$32 = false
(gdb) n
415                else if (DatumGetBool(rowresult))
(gdb)

结果不为T,因此再次循环执行,获取slot,这次的值应为3,因为3 <> 1,因此再次循环直至t_big_null中出现值1或完成扫描

326             slot = ExecProcNode(planstate))
(gdb) 
...
(gdb) 
403                prmdata->value = slot_getattr(slot, col, &(prmdata->isnull));
(gdb) 
404                col++;
(gdb) p prmdata->value
$36 = 3
(gdb) p slot->tts_values[0]
$37 = 3
(gdb) 
...

完成执行

(gdb) c
Continuing.
###
[local]:5432 pg12@testdb=# select * from tbl a where a.id not in (select b.id from t_big_null b);
 id | value 
----+-------
(0 rows)
Time: 3089703.031 ms (51:29.703)
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# 
###

调用栈

Breakpoint 1, ExecScanSubPlan (node=0x1c89158, econtext=0x1c88990, isNull=0x1c88cad)
    at nodeSubplan.c:228
228        SubPlan    *subplan = node->subplan;
(gdb) bt
#0  ExecScanSubPlan (node=0x1c89158, econtext=0x1c88990, isNull=0x1c88cad)
    at nodeSubplan.c:228
#1  0x000000000072fe5e in ExecSubPlan (node=0x1c89158, econtext=0x1c88990, 
    isNull=0x1c88cad) at nodeSubplan.c:90
#2  0x00000000006e90a8 in ExecEvalSubPlan (state=0x1c88ca8, op=0x1c88d80, 
    econtext=0x1c88990) at execExprInterp.c:3783
#3  0x00000000006e48b2 in ExecInterpExpr (state=0x1c88ca8, econtext=0x1c88990, 
    isnull=0x7fff7b9514f7) at execExprInterp.c:1484
#4  0x00000000006e50b8 in ExecInterpExprStillValid (state=0x1c88ca8, econtext=0x1c88990, 
    isNull=0x7fff7b9514f7) at execExprInterp.c:1769
#5  0x00000000006f9ec0 in ExecEvalExprSwitchContext (state=0x1c88ca8, econtext=0x1c88990, 
    isNull=0x7fff7b9514f7) at ../../../src/include/executor/executor.h:307
#6  0x00000000006f9fb8 in ExecQual (state=0x1c88ca8, econtext=0x1c88990)
    at ../../../src/include/executor/executor.h:376
#7  0x00000000006fa37d in ExecScan (node=0x1c88878, accessMtd=0x72b84c <SeqNext>, 
    recheckMtd=0x72b8f1 <SeqRecheck>) at execScan.c:228
#8  0x000000000072b93b in ExecSeqScan (pstate=0x1c88878) at nodeSeqscan.c:112
#9  0x00000000006f8077 in ExecProcNodeFirst (node=0x1c88878) at execProcnode.c:445
#10 0x00000000006ed3cd in ExecProcNode (node=0x1c88878)
    at ../../../src/include/executor/executor.h:239
#11 0x00000000006efbb3 in ExecutePlan (estate=0x1c87da8, planstate=0x1c88878, 
    use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, numberTuples=0, 
    direction=ForwardScanDirection, dest=0x1cbc7c0, execute_once=true) at execMain.c:1646
#12 0x00000000006ed9df in standard_ExecutorRun (queryDesc=0x1be78b8, 
    direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:364
#13 0x00000000006ed815 in ExecutorRun (queryDesc=0x1be78b8, 
    direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:308
#14 0x00000000008f1010 in PortalRunSelect (portal=0x1c27d78, forward=true, count=0, 
    dest=0x1cbc7c0) at pquery.c:929
#15 0x00000000008f0cae in PortalRun (portal=0x1c27d78, count=9223372036854775807, 
    isTopLevel=true, run_once=true, dest=0x1cbc7c0, altdest=0x1cbc7c0, 
    completionTag=0x7fff7b951890 "") at pquery.c:770
#16 0x00000000008ead35 in exec_simple_query (
    query_string=0x1bc1d88 "select * from tbl a where a.id not in (select b.id from t_big_null b);") at postgres.c:1215
#17 0x00000000008eefa5 in PostgresMain (argc=1, argv=0x1bedf18, 
    dbname=0x1bedd60 "testdb", username=0x1bbeaa8 "pg12") at postgres.c:4236
#18 0x0000000000845915 in BackendRun (port=0x1be3d30) at postmaster.c:4431
#19 0x00000000008450f3 in BackendStartup (port=0x1be3d30) at postmaster.c:4122
#20 0x000000000084132f in ServerLoop () at postmaster.c:1704
#21 0x0000000000840be5 in PostmasterMain (argc=1, argv=0x1bbca60) at postmaster.c:1377
#22 0x0000000000761469 in main (argc=1, argv=0x1bbca60) at main.c:228
(gdb)

DONE

四、参考资料

N/A

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2660216/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1416
  • 访问量
    3855849