本節介紹了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</seqrecheck></seqnext></ttsopsminimaltuple></ttsopsminimaltuple></ttsopsminimaltuple></ttsopsminimaltuple></execmaterial></execmaterial></ttsopsminimaltuple></ttsopsminimaltuple></execmaterial></execprocnodefirst>

PostgreSQL 原始碼解讀（230）- 查詢#123(NOT IN實現)

相關文章