本篇内容主要讲解“PostgreSQL源码中NOT IN的作用是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL源码中NOT IN的作用是什么”吧!

SubPlanState
子计划运行期状态

/*----------------*SubPlanStatenode*----------------*/typedefstructSubPlanState{NodeTagtype;SubPlan*subplan;/*expressionplannode*/structPlanState*planstate;/*subselectplan'sstatetree*/structPlanState*parent;/*parentplannode'sstatetree*/ExprState*testexpr;/*组合表达式状态；stateofcombiningexpression*/List*args;/*参数表达式状态；statesofargumentexpression(s)*/HeapTuplecurTuple;/*subplan最近的元组；copyofmostrecenttuplefromsubplan*/DatumcurArray;/*mostrecentarrayfromARRAY()subplan*//*theseareusedwhenhashingthesubselect'soutput:*/TupleDescdescRight;/*投影后的子查询描述符；subselectdescafterprojection*/ProjectionInfo*projLeft;/*forprojectinglefthandexprs*/ProjectionInfo*projRight;/*forprojectingsubselectoutput*/TupleHashTablehashtable;/*hashtableforno-nullssubselectrows*/TupleHashTablehashnulls;/*hashtableforrowswithnull(s)*/boolhavehashrows;/*trueifhashtableisnotempty*/boolhavenullrows;/*trueifhashnullsisnotempty*/MemoryContexthashtablecxt;/*memorycontextcontaininghashtables*/MemoryContexthashtempcxt;/*tempmemorycontextforhashtables*/ExprContext*innerecontext;/*econtextforcomputinginnertuples*/AttrNumber*keyColIdx;/*controldataforhashtables*/Oid*tab_eq_funcoids;/*equalityfuncoidsfortable*datatype(s)*/Oid*tab_collations;/*collationsforhashandcomparison*/FmgrInfo*tab_hash_funcs;/*hashfunctionsfortabledatatype(s)*/FmgrInfo*tab_eq_funcs;/*equalityfunctionsfortabledatatype(s)*/FmgrInfo*lhs_hash_funcs;/*hashfunctionsforlefthanddatatype(s)*/FmgrInfo*cur_eq_funcs;/*equalityfunctionsforLHSvs.table*/ExprState*cur_eq_comp;/*equalitycomparatorforLHSvs.table*/}SubPlanState;

SubPlan
子查询计划

/**SubPlan-executableexpressionnodeforasubplan(sub-SELECT)**TheplannerreplacesSubLinknodesinexpressiontreeswithSubPlan*nodesafterithasfinishedplanningthesubquery.SubPlanreferences*asub-plantreestoredinthesubplanslistofthetoplevelPlannedStmt.*(Weavoidadirectlinktomakeiteasiertocopyexpressiontrees*withoutcausingmultipleprocessingofthesubplan.)*查询规划器在完成子查询的规划后使用SubPlan节点替换表达式树中的SubLink节点。*SubPlan引用了存储在高层PlannedStmt中的subplans链表中的sub-plantree。*（避免使用直接链接，从而使得拷贝表达式树相对比较简单）**Inanordinarysubplan,testexprpointstoanexecutableexpression*(OpExpr,anAND/ORtreeofOpExprs,orRowCompareExpr)forthecombining*operator(s);theleft-handargumentsaretheoriginallefthandexpressions,*andtheright-handargumentsarePARAM_EXECParamnodesrepresentingthe*outputsofthesub-select.(NOTE:runtimecoercionfunctionsmaybe*insertedaswell.)Thisisjustthesameexpressiontreeastestexprin*theoriginalSubLinknode,butthePARAM_SUBLINKnodesarereplacedby*suitablynumberedPARAM_EXECnodes.*常规情况下，testexpr指向用于组合操作的可执行表达式（OpExpr、OpExprs的AND/OR树或者RowCompareExpr）；*左参数是原始的左表达式，右参数是PARAM_EXEC参数节点用以表示子查询的输出。*与原始SubLink节点的testexpr具有相同的表达式树，但PARAM_SUBLINK节点则使用合适的已编号PARAM_EXEC节点替代。**Ifthesub-selectbecomesaninitplanratherthanasubplan,theexecutable*expressionispartoftheouterplan'sexpressiontree(andtheSubPlan*nodeitselfisnot,butratherisfoundintheouterplan'sinitPlan*list).InthiscasetestexprisNULLtoavoidduplication.*如果子查询成了initplan而不是subplan，可执行的表达式是外层plan表达式树的一部分。*这种情况下，testexpr为NULL以避免重复。**Theplanneralsoderiveslistsofthevaluesthatneedtobepassedinto*andoutofthesubplan.Inputvaluesarerepresentedasalist"args"of*expressionstobeevaluatedintheouter-querycontext(currentlythese*argsarealwaysjustVars,butinprincipletheycouldbeanyexpression).*ThevaluesareassignedtotheglobalPARAM_EXECparamsindexedbyparParam*(theparParamandargslistsmusthavethesameordering).setParamisa*listofthePARAM_EXECparamsthatarecomputedbythesub-select,ifit*isaninitplan;theyarelistedinorderbysub-selectoutputcolumn*position.(parParamandsetParamareintegerLists,notBitmapsets,*becausetheirorderingissignificant.)*规划器还派生了需要传入和传出子计划的值的链表。*输入值标识位表达式的“args”链表，在外层查询上下文中进行解析。*（这些args通常是Vars，但原则上它们可以是任意表达式）*这些值以parParam为索引给全局PARAM_EXEC参数赋值。*setParam是PARAM_EXEC参数链表，通过子查询（如为initplan）计算所得。*它们按子查询输出列的位置进行排序组织为链表形式。*（parParam和setParam是整型链表，而不是Bitmapsets链表）**Also,theplannercomputesstartupandper-callcostsforuseofthe*SubPlan.Notethattheseincludethecostofthesubqueryproper,*evaluationofthetestexprifany,andanyhashtablemanagementoverhead.*同时，规划器计算SubPlan启动和每次调用的成本。注意：包括子查询正常解析testexpr的成本以及哈希表管理成本。*/typedefstructSubPlan{Exprxpr;//表达式/*FieldscopiedfromoriginalSubLink:*///从SubLink中拷贝而来SubLinkTypesubLinkType;/*seeabove*//*Thecombiningoperators,transformedtoanexecutableexpression:*///组合操作符,转换为可执行的表达式Node*testexpr;/*OpExprorRowCompareExprexpressiontree*/List*paramIds;/*参数IDs;IDsofParamsembeddedintheabove*//*IdentificationofthePlantreetouse:*///Plantree标识intplan_id;/*Index(from1)inPlannedStmt.subplans*//*IdentificationoftheSubPlanforEXPLAINanddebuggingpurposes:*///EXPLAIN和debug目的的SubPlan标识char*plan_name;/*Anameassignedduringplanning*//*Extradatausefulfordeterminingsubplan'soutputtype:*///用于确定subplan输出类型的额外信息OidfirstColType;/*subplan结果的第一个列类型;Typeoffirstcolumnofsubplanresult*/int32firstColTypmod;/*第一列的Typmod;Typmodoffirstcolumnofsubplanresult*/OidfirstColCollation;/*第一列的Collation;Collationoffirstcolumnofsubplan*result*//*Informationaboutexecutionstrategy:*///执行阶段的相关信息booluseHashTable;/*是否使用哈希表存储子查询输出;truetostoresubselectoutputinahash*table(implieswearedoing"IN")*/boolunknownEqFalse;/*如OK为T,如为未知则为F;快速处理null值;trueifit'sokaytoreturnFALSEwhenthe*specresultisUNKNOWN;thisallowsmuch*simplerhandlingofnullvalues*/boolparallel_safe;/*是否并行安全?isthesubplanparallel-safe?*//*Note:parallel_safedoesnotconsidercontentsoftestexprorargs*//*Informationforpassingparamsintoandoutofthesubselect:*///用于给子查询传入和传出参数的信息/*setParamandparParamarelistsofintegers(paramIDs)*///setParam和parParam是整型链表(paramIDs)List*setParam;/*initplansubquerieshavetosetthese*Paramsforparentplan*/List*parParam;/*indicesofinputParamsfromparentplan*/List*args;/*以parParam值进行传递的表达式；exprstopassasparParamvalues*//*Estimatedexecutioncosts:*///估算执行成本Coststartup_cost;/*one-timesetupcost*/Costper_call_cost;/*costforeachsubplanevaluation*/}SubPlan;

SubLinkType
SubLink类型

/**SubLink**ASubLinkrepresentsasubselectappearinginanexpression,andinsome*casesalsothecombiningoperator(s)justaboveit.ThesubLinkType*indicatestheformoftheexpressionrepresented:*EXISTS_SUBLINKEXISTS(SELECT...)*ALL_SUBLINK(lefthand)opALL(SELECT...)*ANY_SUBLINK(lefthand)opANY(SELECT...)*ROWCOMPARE_SUBLINK(lefthand)op(SELECT...)*EXPR_SUBLINK(SELECTwithsingletargetlistitem...)*MULTIEXPR_SUBLINK(SELECTwithmultipletargetlistitems...)*ARRAY_SUBLINKARRAY(SELECTwithsingletargetlistitem...)*CTE_SUBLINKWITHquery(neveractuallypartofanexpression)*ForALL,ANY,andROWCOMPARE,thelefthandisalistofexpressionsofthe*samelengthasthesubselect'stargetlist.ROWCOMPAREwill*always*have*alistwithmorethanoneentry;ifthesubselecthasjustonetarget*thentheparserwillcreateanEXPR_SUBLINKinstead(andanyoperator*abovethesubselectwillberepresentedseparately).*ROWCOMPARE,EXPR,andMULTIEXPRrequirethesubselecttodeliveratmost*onerow(ifitreturnsnorows,theresultisNULL).*ALL,ANY,andROWCOMPARErequirethecombiningoperatorstodeliverboolean*results.ALLandANYcombinetheper-rowresultsusingANDandOR*semanticsrespectively.*ARRAYrequiresjustonetargetcolumn,andcreatesanarrayofthetarget*column'stypeusinganynumberofrowsresultingfromthesubselect.**SubLinkisclassedasanExprnode,butitisnotactuallyexecutable;*itmustbereplacedintheexpressiontreebyaSubPlannodeduring*planning.**NOTE:intherawoutputofgram.y,testexprcontainsjusttherawform*ofthelefthandexpression(ifany),andoperNameistheStringnameof*thecombiningoperator.Also,subselectisarawparsetree.Duringparse*analysis,theparsertransformstestexprintoacompletebooleanexpression*thatcomparesthelefthandvalue(s)toPARAM_SUBLINKnodesrepresentingthe*outputcolumnsofthesubselect.AndsubselectistransformedtoaQuery.*Thisistherepresentationseeninsavedrulesandintherewriter.**InEXISTS,EXPR,MULTIEXPR,andARRAYSubLinks,testexprandoperName*areunusedandarealwaysnull.**subLinkIdiscurrentlyusedonlyforMULTIEXPRSubLinks,andiszeroin*otherSubLinks.Thisnumberidentifiesdifferentmultiple-assignment*subquerieswithinanUPDATEstatement'sSETlist.Itisuniqueonly*withinaparticulartargetlist.Theoutputcolumn(s)oftheMULTIEXPR*arereferencedbyPARAM_MULTIEXPRParamsappearingelsewhereinthetlist.**TheCTE_SUBLINKcaseneveroccursinactualSubLinknodes,butitisused*inSubPlansgeneratedforWITHsubqueries.*/typedefenumSubLinkType{EXISTS_SUBLINK,ALL_SUBLINK,ANY_SUBLINK,ROWCOMPARE_SUBLINK,EXPR_SUBLINK,MULTIEXPR_SUBLINK,ARRAY_SUBLINK,CTE_SUBLINK/*forSubPlansonly*/}SubLinkType;

SubLink
SubLink结构体

typedefstructSubLink{Exprxpr;SubLinkTypesubLinkType;/*seeabove*/intsubLinkId;/*ID(1..n);0ifnotMULTIEXPR*/Node*testexpr;/*outer-querytestforALL/ANY/ROWCOMPARE*/List*operName;/*originallyspecifiedoperatorname*/Node*subselect;/*subselectasQuery*orrawparsetree*/intlocation;/*tokenlocation,or-1ifunknown*/}SubLink;二、源码解读

ExecScanSubPlan

/**ExecScanSubPlan:defaultcasewherewehavetorescansubplaneachtime*默认情况下每次都不得不重新扫描subplan*/staticDatumExecScanSubPlan(SubPlanState*node,ExprContext*econtext,bool*isNull){SubPlan*subplan=node->subplan;//子计划PlanState*planstate=node->planstate;//计划运行期状态SubLinkTypesubLinkType=subplan->subLinkType;//子链接类型MemoryContextoldcontext;//原内存上下文TupleTableSlot*slot;//元组slotDatumresult;//结果指针boolfound=false;/*如找到至少一个元组,则返回T;trueifgotatleastonesubplantuple*/ListCell*pvar;//临时变量ListCell*l;//临时变量ArrayBuildStateAny*astate=NULL;///**MULTIEXPRsubplans,when"executed",justreturnNULL;butfirstwe*markthesubplan'soutputparametersasneedingrecalculation.(This*isabitofahack:itreliesonthesubplanappearinglaterinits*targetlistthananyofthereferencingParams,sothatalltheParams*havebeenevaluatedbeforewere-markthemforthenextevaluation*cycle.Butingeneralresjunktlistitemsappearafternon-resjunk*ones,sothisshouldbesafe.)UnlikeExecReScanSetParamPlan,wedo**not*setbitsintheparentplannode'schgParam,becausewedon't*wanttocausearescanoftheparent.**MULTIEXPR处理逻辑*/if(subLinkType==MULTIEXPR_SUBLINK){EState*estate=node->parent->state;foreach(l,subplan->setParam){intparamid=lfirst_int(l);ParamExecData*prm=&(estate->es_param_exec_vals[paramid]);prm->execPlan=node;}*isNull=true;return(Datum)0;}/*InitializeArrayBuildStateAnyincaller'scontext,ifneeded*///数组if(subLinkType==ARRAY_SUBLINK)astate=initArrayResultAny(subplan->firstColType,CurrentMemoryContext,true);/**Weareprobablyinashort-livedexpression-evaluationcontext.Switch*totheper-querycontextformanipulatingthechildplan'schgParam,*callingExecProcNodeonit,etc.*///切换上下文oldcontext=MemoryContextSwitchTo(econtext->ecxt_per_query_memory);/**SetParamsofthisplanfromparentplancorrelationvalues.(Any*calculationwehavetodoisdoneintheparentecontext,sincethe*Paramvaluesdon'tneedtohaveper-querylifetime.)*///通过父计划相关值中设置子计划参数Assert(list_length(subplan->parParam)==list_length(node->args));forboth(l,subplan->parParam,pvar,node->args){intparamid=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);}/**Nowthatwe'vesetupitsparameters,wecanresetthesubplan.*///执行ReScan//Resetaplannodesothatitsoutputcanbere-scanned.ExecReScan(planstate);/**ForallsublinktypesexceptEXPR_SUBLINKandARRAY_SUBLINK,theresult*isbooleanasaretheresultsofthecombiningoperators.Wecombine*resultsacrosstuples(ifthesubplanproducesmorethanone)usingOR*semanticsforANY_SUBLINKorANDsemanticsforALL_SUBLINK.*(ROWCOMPARE_SUBLINKdoesn'tallowmultipletuplesfromthesubplan.)*NULLresultsfromthecombiningoperatorsarehandledaccordingtothe*usualSQLsemanticsforORandAND.Theresultfornoinputtuplesis*FALSEforANY_SUBLINK,TRUEforALL_SUBLINK,NULLfor*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.**ForEXPR_SUBLINKwerequirethesubplantoproducenomorethanone*tuple,elseanerrorisraised.Ifzerotuplesareproduced,wereturn*NULL.Assumingwegetatuple,wejustuseitsfirstcolumn(therecan*beonlyonenon-junkcolumninthiscase).*对于EXPR_SUBLINK,需要subplan产生不超过一个元组,否则报错.如果没有元组产生,返回NULL.*假定获取到一个元组,则使用第一个列(这种情况下只有一个non-junk列).**ForARRAY_SUBLINKweallowthesubplantoproduceanynumberoftuples,*andformanarrayofthefirstcolumn'svalues.Noteinparticular*thatweproduceazero-elementarrayifnotuplesareproduced(thisis*achangefrompre-8.3behaviorofreturningNULL).*对于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(即已完成){//元组描述符TupleDesctdesc=slot->tts_tupleDescriptor;Datumrowresult;//结果boolrownull;//是否为空?intcol;//列计数器ListCell*plst;//临时变量if(subLinkType==EXISTS_SUBLINK)//EXISTS{found=true;result=BoolGetDatum(true);break;}if(subLinkType==EXPR_SUBLINK)//EXPR表达式{/*cannotallowmultipleinputtuplesforEXPRsublink*/if(found)ereport(ERROR,(errcode(ERRCODE_CARDINALITY_VIOLATION),errmsg("morethanonerowreturnedbyasubqueryusedasanexpression")));found=true;/**Weneedtocopythesubplan'stupleincasetheresultisof*pass-by-reftype---ourreturnvaluewillpointintothis*copiedtuple!Can'tusethesubplan'sinstanceofthetuple*sinceitwon'tstillbevalidafternextExecProcNode()call.*node->curTuplekeepstrackofthecopiedtupleforeventual*freeing.*/if(node->curTuple)heap_freetuple(node->curTuple);node->curTuple=ExecCopySlotHeapTuple(slot);result=heap_getattr(node->curTuple,1,tdesc,isNull);/*keepscanningsubplantomakesurethere'sonlyonetuple*/continue;}if(subLinkType==ARRAY_SUBLINK)//数组{Datumdvalue;booldisnull;found=true;/*stashawaycurrentvalue*/Assert(subplan->firstColType==TupleDescAttr(tdesc,0)->atttypid);dvalue=slot_getattr(slot,1,&disnull);astate=accumArrayResultAny(astate,dvalue,disnull,subplan->firstColType,oldcontext);/*keepscanningsubplantocollectallvalues*/continue;}/*cannotallowmultipleinputtuplesforROWCOMPAREsublinkeither*/if(subLinkType==ROWCOMPARE_SUBLINK&&found)//行比较ereport(ERROR,(errcode(ERRCODE_CARDINALITY_VIOLATION),errmsg("morethanonerowreturnedbyasubqueryusedasanexpression")));found=true;//初始为T/**ForALL,ANY,andROWCOMPAREsublinks,loaduptheParams*representingthecolumnsofthesub-select,andthenevaluatethe*combiningexpression.*对于ALL,ANY和ROWCOMPARE子链接,加载表示子查询列的Params,并解析组合表达式*/col=1;//列从1计数foreach(plst,subplan->paramIds)//循环遍历子查询参数{intparamid=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语义组合/*combineacrossrowsperORsemantics*/if(rownull)*isNull=true;elseif(DatumGetBool(rowresult)){result=BoolGetDatum(true);*isNull=false;break;/*needn'tlookatanymorerows*/}}elseif(subLinkType==ALL_SUBLINK){//ALL:使用AND语义/*combineacrossrowsperANDsemantics*/if(rownull)*isNull=true;elseif(!DatumGetBool(rowresult)){result=BoolGetDatum(false);*isNull=false;break;/*needn'tlookatanymorerows*/}}else{/*mustbeROWCOMPARE_SUBLINK*///这里一定是ROWCOMPAREresult=rowresult;*isNull=rownull;}}MemoryContextSwitchTo(oldcontext);if(subLinkType==ARRAY_SUBLINK){/*Wereturntheresultinthecaller'scontext*///在调用者上下文中返回结果result=makeArrayResultAny(astate,oldcontext,true);}elseif(!found){/**dealwithemptysubplanresult.result/isNullwerepreviously*initializedcorrectlyforallsublinktypesexceptEXPRand*ROWCOMPARE;forthose,returnNULL.*subplan没有结果返回,设置result&isNull值*/if(subLinkType==EXPR_SUBLINK||subLinkType==ROWCOMPARE_SUBLINK){result=(Datum)0;*isNull=true;}}//返回结果returnresult;}

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

/*----------------------------------------------------------------*ExecProcNode**Executethegivennodetoreturna(nother)tuple.*----------------------------------------------------------------*/#ifndefFRONTENDstaticinlineTupleTableSlot*ExecProcNode(PlanState*node){if(node->chgParam!=NULL)/*somethingchanged?*/ExecReScan(node);/*letReScanhandlethis*/returnnode->ExecProcNode(node);}#endif三、跟踪分析

执行SQL：

[pg12@localhost~]$psql-dtestdbTimingison.Expandeddisplayisusedautomatically.psql(12.0)Type"help"forhelp.[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#select*fromtbl;id|value----+-------1|2(1row)Time:2.678ms[local]:5432pg12@testdb=#selectcount(*)fromt_big_null;count----------10000001(1row)Time:679.972ms[local]:5432pg12@testdb=#analyzetbl;ANALYZETime:64.442ms[local]:5432pg12@testdb=#analyzet_big_null;ANALYZETime:434.702ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#selectpg_backend_pid();pg_backend_pid----------------18758(1row)Time:1.990ms[local]:5432pg12@testdb=#select*fromtblawherea.idnotin(selectb.idfromt_big_nullb);

启动gdb跟踪

(gdb)bExecScanSubPlanBreakpoint1at0x73014b:filenodeSubplan.c,line228.(gdb)cContinuing.Breakpoint1,ExecScanSubPlan(node=0x1c89158,econtext=0x1c88990,isNull=0x1c88cad)atnodeSubplan.c:228228SubPlan*subplan=node->subplan;(gdb)(gdb)n229PlanState*planstate=node->planstate;(gdb)230SubLinkTypesubLinkType=subplan->subLinkType;(gdb)234boolfound=false;/*trueifgotatleastonesubplantuple*/

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"SubPlan1",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]:5432pg12@testdb=#select*frompg_procwhereoid=65;-->opfuncid=65，函数-[RECORD1]---+-------oid|65proname|int4eqpronamespace|11proowner|10prolang|12procost|1prorows|0provariadic|0prosupport|-prokind|fprosecdef|fproleakproof|tproisstrict|tproretset|fprovolatile|iproparallel|spronargs|2pronargdefaults|0prorettype|16proargtypes|2323proallargtypes|proargmodes|proargnames|proargdefaults|protrftypes|prosrc|int4eqprobin|proconfig|proacl|Time:6.253ms###(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)psubLinkType$16=ANY_SUBLINK

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

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

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

(gdb)n298ExecReScan(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)n321result=BoolGetDatum(subLinkType==ALL_SUBLINK);(gdb)322*isNull=false;(gdb)presult$18=0

执行循环，slot从planstate->ExecProcNode（即ExecMaterial）中获取

(gdb)n324for(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)pslot->tts_values[0]$23=0(gdb)

获取slot的描述符

(gdb)328TupleDesctdesc=slot->tts_tupleDescriptor;(gdb)ptdesc$21=(TupleDesc)0x206e6f6900000000

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

(gdb)n334if(subLinkType==EXISTS_SUBLINK)(gdb)341if(subLinkType==EXPR_SUBLINK)(gdb)367if(subLinkType==ARRAY_SUBLINK)(gdb)383if(subLinkType==ROWCOMPARE_SUBLINK&&found)(gdb)388found=true;(gdb)395col=1;(gdb)396foreach(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]Cannotaccessmemoryataddress0x0(gdb)pslot->tts_values[0]$23=0(gdb)n398intparamid=lfirst_int(plst);(gdb)401prmdata=&(econtext->ecxt_param_exec_vals[paramid]);(gdb)pparamid$24=0(gdb)n402Assert(prmdata->execPlan==NULL);(gdb)p*prmdata$25={execPlan=0x0,value=0,isnull=false}(gdb)n403prmdata->value=slot_getattr(slot,col,&(prmdata->isnull));(gdb)n404col++;(gdb)p*prmdata$26={execPlan=0x0,value=2,isnull=false}(gdb)n396foreach(plst,subplan->paramIds)(gdb)

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

(gdb)407rowresult=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)pecontext->ecxt_param_exec_vals[0]$30={execPlan=0x0,value=2,isnull=false}

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

(gdb)n410if(subLinkType==ANY_SUBLINK)(gdb)prowresult$31=0(gdb)

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

(gdb)n410if(subLinkType==ANY_SUBLINK)(gdb)prowresult$31=0(gdb)n413if(rownull)(gdb)prownull$32=false(gdb)n415elseif(DatumGetBool(rowresult))(gdb)

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

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

完成执行

(gdb)cContinuing.###[local]:5432pg12@testdb=#select*fromtblawherea.idnotin(selectb.idfromt_big_nullb);id|value----+-------(0rows)Time:3089703.031ms(51:29.703)[local]:5432pg12@testdb=#[local]:5432pg12@testdb=####

调用栈

Breakpoint1,ExecScanSubPlan(node=0x1c89158,econtext=0x1c88990,isNull=0x1c88cad)atnodeSubplan.c:228228SubPlan*subplan=node->subplan;(gdb)bt#0ExecScanSubPlan(node=0x1c89158,econtext=0x1c88990,isNull=0x1c88cad)atnodeSubplan.c:228#10x000000000072fe5einExecSubPlan(node=0x1c89158,econtext=0x1c88990,isNull=0x1c88cad)atnodeSubplan.c:90#20x00000000006e90a8inExecEvalSubPlan(state=0x1c88ca8,op=0x1c88d80,econtext=0x1c88990)atexecExprInterp.c:3783#30x00000000006e48b2inExecInterpExpr(state=0x1c88ca8,econtext=0x1c88990,isnull=0x7fff7b9514f7)atexecExprInterp.c:1484#40x00000000006e50b8inExecInterpExprStillValid(state=0x1c88ca8,econtext=0x1c88990,isNull=0x7fff7b9514f7)atexecExprInterp.c:1769#50x00000000006f9ec0inExecEvalExprSwitchContext(state=0x1c88ca8,econtext=0x1c88990,isNull=0x7fff7b9514f7)at../../../src/include/executor/executor.h:307#60x00000000006f9fb8inExecQual(state=0x1c88ca8,econtext=0x1c88990)at../../../src/include/executor/executor.h:376#70x00000000006fa37dinExecScan(node=0x1c88878,accessMtd=0x72b84c<SeqNext>,recheckMtd=0x72b8f1<SeqRecheck>)atexecScan.c:228#80x000000000072b93binExecSeqScan(pstate=0x1c88878)atnodeSeqscan.c:112#90x00000000006f8077inExecProcNodeFirst(node=0x1c88878)atexecProcnode.c:445#100x00000000006ed3cdinExecProcNode(node=0x1c88878)at../../../src/include/executor/executor.h:239#110x00000000006efbb3inExecutePlan(estate=0x1c87da8,planstate=0x1c88878,use_parallel_mode=false,operation=CMD_SELECT,sendTuples=true,numberTuples=0,direction=ForwardScanDirection,dest=0x1cbc7c0,execute_once=true)atexecMain.c:1646#120x00000000006ed9dfinstandard_ExecutorRun(queryDesc=0x1be78b8,direction=ForwardScanDirection,count=0,execute_once=true)atexecMain.c:364#130x00000000006ed815inExecutorRun(queryDesc=0x1be78b8,direction=ForwardScanDirection,count=0,execute_once=true)atexecMain.c:308#140x00000000008f1010inPortalRunSelect(portal=0x1c27d78,forward=true,count=0,dest=0x1cbc7c0)atpquery.c:929#150x00000000008f0caeinPortalRun(portal=0x1c27d78,count=9223372036854775807,isTopLevel=true,run_once=true,dest=0x1cbc7c0,altdest=0x1cbc7c0,completionTag=0x7fff7b951890"")atpquery.c:770#160x00000000008ead35inexec_simple_query(query_string=0x1bc1d88"select*fromtblawherea.idnotin(selectb.idfromt_big_nullb);")atpostgres.c:1215#170x00000000008eefa5inPostgresMain(argc=1,argv=0x1bedf18,dbname=0x1bedd60"testdb",username=0x1bbeaa8"pg12")atpostgres.c:4236#180x0000000000845915inBackendRun(port=0x1be3d30)atpostmaster.c:4431#190x00000000008450f3inBackendStartup(port=0x1be3d30)atpostmaster.c:4122#200x000000000084132finServerLoop()atpostmaster.c:1704#210x0000000000840be5inPostmasterMain(argc=1,argv=0x1bbca60)atpostmaster.c:1377#220x0000000000761469inmain(argc=1,argv=0x1bbca60)atmain.c:228(gdb)

到此，相信大家对“PostgreSQL源码中NOT IN的作用是什么”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！