这篇文章主要介绍“PostgreSQL中ExecutePlan函数与ExecSeqScan函数的作用是什么”，在日常操作中，相信很多人在PostgreSQL中ExecutePlan函数与ExecSeqScan函数的作用是什么问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中ExecutePlan函数与ExecSeqScan函数的作用是什么”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
ExecutePlan函数处理查询计划，直到检索到指定数量(参数numbertuple)的元组，并沿着指定的方向扫描。ExecSeqScan函数顺序扫描relation,返回下一个符合条件的元组。
一、数据结构
Plan
所有计划节点通过将Plan结构作为第一个字段从Plan结构“派生”。这确保了在将节点转换为计划节点时，一切都能正常工作。(在执行器中以通用方式传递时，节点指针经常被转换为Plan *)
typedef struct Plan
{
NodeTag type;//节点类型
Cost startup_cost;
Cost total_cost;
double plan_rows;
int plan_width;
bool parallel_aware;
bool parallel_safe;
int plan_node_id;
List *targetlist;
List *qual;
struct Plan *lefttree;
struct Plan *righttree;
List *initPlan;
Bitmapset *extParam;
Bitmapset *allParam;
} Plan;二、源码解读
ExecutePlan
PortalRunSelect->ExecutorRun->ExecutePlan函数处理查询计划，直到检索到指定数量(参数numbertuple)的元组，并沿着指定的方向扫描.
static void
ExecutePlan(EState *estate,//执行状态
PlanState *planstate,//计划状态
bool use_parallel_mode,//是否使用并行模式
CmdType operation,//操作类型
bool sendTuples,//是否需要传输元组
uint64 numberTuples,//元组数量
ScanDirection direction,//扫描方向
DestReceiver *dest,//接收的目标端
bool execute_once)//是否只执行一次
{
TupleTableSlot *slot;//元组表Slot
uint64 current_tuple_count;//当前的元组计数
current_tuple_count = 0;
estate->es_direction = direction;
if (!execute_once)
use_parallel_mode = false;//如需多次执行,则不允许并行执行
estate->es_use_parallel_mode = use_parallel_mode;
if (use_parallel_mode)
EnterParallelMode();//如并行,则进入并行模式
for (;;)
{
//重置Expr上下文
ResetPerTupleExprContext(estate);
slot = ExecProcNode(planstate);
if (TupIsNull(slot))
{
if (!(estate->es_top_eflags & EXEC_FLAG_BACKWARD))
(void) ExecShutdownNode(planstate);
break;
}
if (estate->es_junkFilter != NULL)
slot = ExecFilterJunk(estate->es_junkFilter, slot);
if (sendTuples)
{
if (!dest->receiveSlot(slot, dest))
break;//跳出循环
}
if (operation == CMD_SELECT)
(estate->es_processed)++;
current_tuple_count++;
if (numberTuples && numberTuples == current_tuple_count)
{
if (!(estate->es_top_eflags & EXEC_FLAG_BACKWARD))
(void) ExecShutdownNode(planstate);
break;
}
}
if (use_parallel_mode)
ExitParallelMode();//退出并行模式
}
#ifndef FRONTEND
static inline TupleTableSlot *
ExecProcNode(PlanState *node)
{
if (node->chgParam != NULL)
ExecReScan(node);
return node->ExecProcNode(node);//执行ExecProcNode
}
#endifExecSeqScan
ExecSeqScan函数顺序扫描relation,返回下一个符合条件的元组。
static TupleTableSlot *
ExecSeqScan(PlanState *pstate)
{
SeqScanState *node = castNode(SeqScanState, pstate);//获取SeqScanState
return ExecScan(&node->ss,
(ExecScanAccessMtd) SeqNext,
(ExecScanRecheckMtd) SeqRecheck);//执行Scan
}
TupleTableSlot *
ExecScan(ScanState *node,
ExecScanAccessMtd accessMtd,
ExecScanRecheckMtd recheckMtd) //recheck方法
{
ExprContext *econtext;//表达式上下文
ExprState *qual;//表达式状态
ProjectionInfo *projInfo;//投影信息
qual = node->ps.qual;
projInfo = node->ps.ps_ProjInfo;
econtext = node->ps.ps_ExprContext;
//在ExecScanFetch中有中断检查
if (!qual && !projInfo)
{
ResetExprContext(econtext);
return ExecScanFetch(node, accessMtd, recheckMtd);
}
ResetExprContext(econtext);
for (;;)
{
TupleTableSlot *slot;//slot变量
slot = ExecScanFetch(node, accessMtd, recheckMtd);//获取slot
if (TupIsNull(slot))
{
if (projInfo)
return ExecClearTuple(projInfo->pi_state.resultslot);
else
return slot;
}
econtext->ecxt_scantuple = slot;
if (qual == NULL || ExecQual(qual, econtext))
{
if (projInfo)
{
return ExecProject(projInfo);//执行投影操作并返回
}
else
{
return slot;//直接返回
}
}
else
InstrCountFiltered1(node, 1);//instrument计数
ResetExprContext(econtext);
}
}
static inline TupleTableSlot *
ExecScanFetch(ScanState *node,
ExecScanAccessMtd accessMtd,
ExecScanRecheckMtd recheckMtd)
{
EState *estate = node->ps.state;
CHECK_FOR_INTERRUPTS();//检查中断
if (estate->es_epqTuple != NULL)//如es_epqTuple不为NULL()
{
//es_epqTuple字段用于在READ COMMITTED模式中替换更新后的元组后,重新评估是否满足执行计划的条件quals
Index scanrelid = ((Scan *) node->ps.plan)->scanrelid;//访问的relid
if (scanrelid == 0)//relid==0
{
TupleTableSlot *slot = node->ss_ScanTupleSlot;
if (!(*recheckMtd) (node, slot))
ExecClearTuple(slot);
return slot;
}
else if (estate->es_epqTupleSet[scanrelid - 1])//从estate->es_epqTupleSet数组中获取标志
{
TupleTableSlot *slot = node->ss_ScanTupleSlot;//获取slot
//如已返回元组,则清空slot
if (estate->es_epqScanDone[scanrelid - 1])
return ExecClearTuple(slot);
//否则,标记没有返回
estate->es_epqScanDone[scanrelid - 1] = true;
//如test tuple为NULL,则清空slot
if (estate->es_epqTuple[scanrelid - 1] == NULL)
return ExecClearTuple(slot);
//在计划节点的scan slot中存储test tuple
ExecStoreHeapTuple(estate->es_epqTuple[scanrelid - 1],
slot, false);
//检查是否满足访问方法条件
if (!(*recheckMtd) (node, slot))
ExecClearTuple(slot);
return slot;
}
}
return (*accessMtd) (node);
}
#ifndef FRONTEND
static inline TupleTableSlot *
ExecProject(ProjectionInfo *projInfo)
{
ExprContext *econtext = projInfo->pi_exprContext;
ExprState *state = &projInfo->pi_state;
TupleTableSlot *slot = state->resultslot;
bool isnull;
ExecClearTuple(slot);
//运行表达式，从最后一列丢弃scalar结果。
(void) ExecEvalExprSwitchContext(state, econtext, &isnull);
slot->tts_flags &= ~TTS_FLAG_EMPTY;
slot->tts_nvalid = slot->tts_tupleDescriptor->natts;
return slot;
}
#endif
#ifndef FRONTEND
static inline bool
ExecQual(ExprState *state, ExprContext *econtext)
{
Datum ret;
bool isnull;
//如state为NULL,直接返回
if (state == NULL)
return true;
//使用函数ExecInitQual验证表达式是否可以编译
Assert(state->flags & EEO_FLAG_IS_QUAL);
ret = ExecEvalExprSwitchContext(state, econtext, &isnull);
Assert(!isnull);
return DatumGetBool(ret);
}
#endif
TupleTableSlot *
ExecClearTuple(TupleTableSlot *slot)
{
Assert(slot != NULL);
if (TTS_SHOULDFREE(slot))
{
heap_freetuple(slot->tts_tuple);//释放元组
slot->tts_flags &= ~TTS_FLAG_SHOULDFREE;
}
if (TTS_SHOULDFREEMIN(slot))
{
heap_free_minimal_tuple(slot->tts_mintuple);
slot->tts_flags &= ~TTS_FLAG_SHOULDFREEMIN;
}
slot->tts_tuple = NULL;//设置NULL值
slot->tts_mintuple = NULL;
if (BufferIsValid(slot->tts_buffer))
ReleaseBuffer(slot->tts_buffer);//释放缓冲区
slot->tts_buffer = InvalidBuffer;
slot->tts_flags |= TTS_FLAG_EMPTY;
slot->tts_nvalid = 0;
return slot;
}三、跟踪分析
测试脚本如下
testdb=# explain select dw.*,grjf.grbh,grjf.xm,grjf.ny,grjf.je
testdb-# from t_dwxx dw,lateral (select gr.grbh,gr.xm,jf.ny,jf.je
testdb(# from t_grxx gr inner join t_jfxx jf
testdb(# on gr.dwbh = dw.dwbh
testdb(# and gr.grbh = jf.grbh) grjf
testdb-# order by dw.dwbh;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=20070.93..20320.93 rows=100000 width=47)
Sort Key: dw.dwbh
-> Hash Join (cost=3754.00..8689.61 rows=100000 width=47)
Hash Cond: ((gr.dwbh)::text = (dw.dwbh)::text)
-> Hash Join (cost=3465.00..8138.00 rows=100000 width=31)
Hash Cond: ((jf.grbh)::text = (gr.grbh)::text)
-> Seq Scan on t_jfxx jf (cost=0.00..1637.00 rows=100000 width=20)
-> Hash (cost=1726.00..1726.00 rows=100000 width=16)
-> Seq Scan on t_grxx gr (cost=0.00..1726.00 rows=100000 width=16)
-> Hash (cost=164.00..164.00 rows=10000 width=20)
-> Seq Scan on t_dwxx dw (cost=0.00..164.00 rows=10000 width=20)
(11 rows)启动gdb,设置断点,进入ExecutePlan
(gdb) b ExecutePlan
Breakpoint 1 at 0x6db79d: file execMain.c, line 1694.
(gdb) c
Continuing.
Breakpoint 1, ExecutePlan (estate=0x14daf48, planstate=0x14db160, use_parallel_mode=false, operation=CMD_SELECT,
sendTuples=true, numberTuples=0, direction=ForwardScanDirection, dest=0x14d9ed0, execute_once=true) at execMain.c:1694
warning: Source file is more recent than executable.
1694 current_tuple_count = 0;查看输入参数
planstate->type:T_SortState->排序Plan
planstate->ExecProcNode:ExecProcNodeFirst,封装器
planstate->ExecProcNodeReal:ExecSort,实际的函数
use_parallel_mode:false,非并行模式
operation:CMD_SELECT,查询操作
sendTuples:T,需要发送元组给客户端
numberTuples:0,所有元组
direction:ForwardScanDirection
dest:printtup(console客户端)
execute_once:T,只执行一次
(gdb) p *estate
$1 = {type = T_EState, es_direction = ForwardScanDirection, es_snapshot = 0x1493e10, es_crosscheck_snapshot = 0x0,
es_range_table = 0x14d7c00, es_plannedstmt = 0x14d9d58,
es_sourceText = 0x13eeeb8 "select dw.*,grjf.grbh,grjf.xm,grjf.ny,grjf.je \nfrom t_dwxx dw,lateral (select gr.grbh,gr.xm,jf.ny,jf.je \n", ' ' <repeats 24 times>, "from t_grxx gr inner join t_jfxx jf \n", ' ' <repeats 34 times>...,
es_junkFilter = 0x0, es_output_cid = 0, es_result_relations = 0x0, es_num_result_relations = 0,
es_result_relation_info = 0x0, es_root_result_relations = 0x0, es_num_root_result_relations = 0,
es_tuple_routing_result_relations = 0x0, es_trig_target_relations = 0x0, es_trig_tuple_slot = 0x0,
es_trig_oldtup_slot = 0x0, es_trig_newtup_slot = 0x0, es_param_list_info = 0x0, es_param_exec_vals = 0x0,
es_queryEnv = 0x0, es_query_cxt = 0x14dae30, es_tupleTable = 0x14dbaf8, es_rowMarks = 0x0, es_processed = 0,
es_lastoid = 0, es_top_eflags = 16, es_instrument = 0, es_finished = false, es_exprcontexts = 0x14db550,
es_subplanstates = 0x0, es_auxmodifytables = 0x0, es_per_tuple_exprcontext = 0x0, es_epqTuple = 0x0,
es_epqTupleSet = 0x0, es_epqScanDone = 0x0, es_use_parallel_mode = false, es_query_dsa = 0x0, es_jit_flags = 0,
es_jit = 0x0, es_jit_worker_instr = 0x0}
(gdb) p *planstate
$2 = {type = T_SortState, plan = 0x14d3f90, state = 0x14daf48, ExecProcNode = 0x6e41bb <ExecProcNodeFirst>,
ExecProcNodeReal = 0x716144 <ExecSort>, instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0,
qual = 0x0, lefttree = 0x14db278, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, chgParam = 0x0,
ps_ResultTupleSlot = 0x14ec470, ps_ExprContext = 0x0, ps_ProjInfo = 0x0, scandesc = 0x14e9fd0}
(gdb) p *dest
$4 = {receiveSlot = 0x48cc00 <printtup>, rStartup = 0x48c5c1 <printtup_startup>, rShutdown = 0x48d02e <printtup_shutdown>,
rDestroy = 0x48d0a7 <printtup_destroy>, mydest = DestRemote}赋值,准备执行ExecProcNode(ExecSort)
(gdb) n
1699 estate->es_direction = direction;
(gdb)
1705 if (!execute_once)
(gdb)
1708 estate->es_use_parallel_mode = use_parallel_mode;
(gdb)
1709 if (use_parallel_mode)
(gdb)
1718 ResetPerTupleExprContext(estate);
(gdb)
1723 slot = ExecProcNode(planstate);
(gdb)执行ExecProcNode(ExecSort),返回slot
(gdb)
1729 if (TupIsNull(slot))
(gdb) p *slot
$5 = {type = T_TupleTableSlot, tts_isempty = false, tts_shouldFree = false, tts_shouldFreeMin = false, tts_slow = false,
tts_tuple = 0x14ec4b0, tts_tupleDescriptor = 0x14ec058, tts_mcxt = 0x14dae30, tts_buffer = 0, tts_nvalid = 0,
tts_values = 0x14ec4d0, tts_isnull = 0x14ec508, tts_mintuple = 0x1a4b078, tts_minhdr = {t_len = 64, t_self = {ip_blkid = {
bi_hi = 0, bi_lo = 0}, ip_posid = 0}, t_tableOid = 0, t_data = 0x1a4b070}, tts_off = 0,
tts_fixedTupleDescriptor = true}查看slot中的数据
注意:slot中的t_data不是实际的tuple data,而是缓冲区信息,在返回时根据这些信息从缓冲区获取数据返回
(gdb) p *slot
$5 = {type = T_TupleTableSlot, tts_isempty = false, tts_shouldFree = false, tts_shouldFreeMin = false, tts_slow = false,
tts_tuple = 0x14ec4b0, tts_tupleDescriptor = 0x14ec058, tts_mcxt = 0x14dae30, tts_buffer = 0, tts_nvalid = 0,
tts_values = 0x14ec4d0, tts_isnull = 0x14ec508, tts_mintuple = 0x1a4b078, tts_minhdr = {t_len = 64, t_self = {ip_blkid = {
bi_hi = 0, bi_lo = 0}, ip_posid = 0}, t_tableOid = 0, t_data = 0x1a4b070}, tts_off = 0,
tts_fixedTupleDescriptor = true}
(gdb) p *slot->tts_tuple
$6 = {t_len = 64, t_self = {ip_blkid = {bi_hi = 0, bi_lo = 0}, ip_posid = 0}, t_tableOid = 0, t_data = 0x1a4b070}
(gdb) p *slot->tts_tuple->t_data
$7 = {t_choice = {t_heap = {t_xmin = 21967600, t_xmax = 0, t_field3 = {t_cid = 56, t_xvac = 56}}, t_datum = {
datum_len_ = 21967600, datum_typmod = 0, datum_typeid = 56}}, t_ctid = {ip_blkid = {bi_hi = 0, bi_lo = 0},
ip_posid = 32639}, t_infomask2 = 7, t_infomask = 2, t_hoff = 24 '\030', t_bits = 0x1a4b087 ""}判断是否需要过滤属性(不需要)
(gdb) n
1748 if (estate->es_junkFilter != NULL)
(gdb)
(gdb) p estate->es_junkFilter
$12 = (JunkFilter *) 0x0修改计数器等信息
(gdb)
1755 if (sendTuples)
(gdb)
1762 if (!dest->receiveSlot(slot, dest))
(gdb)
1771 if (operation == CMD_SELECT)
(gdb)
1772 (estate->es_processed)++;
(gdb) p estate->es_processed
$9 = 0
(gdb) n
1779 current_tuple_count++;
(gdb) p current_tuple_count
$10 = 0
(gdb) n
1780 if (numberTuples && numberTuples == current_tuple_count)
(gdb) p numberTuples
$11 = 0
(gdb) n
1790 }继续循环,直接满足条件(全部扫描完毕)未知
(gdb) n
1718 ResetPerTupleExprContext(estate);
(gdb)
1723 slot = ExecProcNode(planstate);
(gdb)
1729 if (TupIsNull(slot))
...ExecutePlan的主体逻辑已介绍完毕,下面简单跟踪分析ExecSeqScan函数
设置断点,进入ExecSeqScan
(gdb) del 1
(gdb) c
Continuing.
Breakpoint 2, ExecSeqScan (pstate=0x14e99a0) at nodeSeqscan.c:127
warning: Source file is more recent than executable.
127 SeqScanState *node = castNode(SeqScanState, pstate);查看输入参数
plan为SeqScan
ExecProcNode=ExecProcNodeReal,均为函数ExecSeqScan
targetlist为投影列信息
(gdb) p *pstate
$13 = {type = T_SeqScanState, plan = 0x14d5570, state = 0x14daf48, ExecProcNode = 0x714d59 <ExecSeqScan>,
ExecProcNodeReal = 0x714d59 <ExecSeqScan>, instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0,
qual = 0x0, lefttree = 0x0, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, chgParam = 0x0,
ps_ResultTupleSlot = 0x14e9c38, ps_ExprContext = 0x14e9ab8, ps_ProjInfo = 0x0, scandesc = 0x7fa45b442ab8}
(gdb) p *pstate->plan
$14 = {type = T_SeqScan, startup_cost = 0, total_cost = 164, plan_rows = 10000, plan_width = 20, parallel_aware = false,
parallel_safe = true, plan_node_id = 7, targetlist = 0x14d5438, qual = 0x0, lefttree = 0x0, righttree = 0x0,
initPlan = 0x0, extParam = 0x0, allParam = 0x0}进入ExecScan函数
accessMtd方法为SeqNext
recheckMtd方法为SeqRecheck
(gdb) n
129 return ExecScan(&node->ss,
(gdb) step
ExecScan (node=0x14e99a0, accessMtd=0x714c6d <SeqNext>, recheckMtd=0x714d3d <SeqRecheck>) at execScan.c:132
warning: Source file is more recent than executable.
132 qual = node->ps.qual;ExecScan->投影信息,为NULL
(gdb) p *projInfo
Cannot access memory at address 0x0ExecScan->约束条件为NULL
(gdb) p *qual
Cannot access memory at address 0x0ExecScan->如果既没有要检查的条件qual,也没有要做的投影操作，那么就跳过所有的操作并返回raw scan元组
(gdb) n
142 if (!qual && !projInfo)
(gdb)
144 ResetExprContext(econtext);
(gdb) n
145 return ExecScanFetch(node, accessMtd, recheckMtd);ExecScan->进入ExecScanFetch
(gdb) step
ExecScanFetch (node=0x14e99a0, accessMtd=0x714c6d <SeqNext>, recheckMtd=0x714d3d <SeqRecheck>) at execScan.c:39
39 EState *estate = node->ps.state;ExecScan->检查中断,判断是否处于EvalPlanQual recheck状态(为NULL,实际不是)
39 EState *estate = node->ps.state;
(gdb) n
41 CHECK_FOR_INTERRUPTS();
(gdb)
43 if (estate->es_epqTuple != NULL)
(gdb) p *estate->es_epqTuple
Cannot access memory at address 0x0ExecScan->调用访问方法SeqNext,返回slot
(gdb) n
95 return (*accessMtd) (node);
(gdb) n
96 }ExecScan->回到ExecScan&ExecSeqScan,结束调用
(gdb) n
ExecScan (node=0x14e99a0, accessMtd=0x714c6d <SeqNext>, recheckMtd=0x714d3d <SeqRecheck>) at execScan.c:219
219 }
(gdb)
ExecSeqScan (pstate=0x14e99a0) at nodeSeqscan.c:132
132 }
(gdb)到此，关于“PostgreSQL中ExecutePlan函数与ExecSeqScan函数的作用是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
