首页 > 资讯 > 数据库 >PostgreSQL 源码解读（232）- 查询#125(NOT IN实现#3)

663

分享到

PostgreSQL 源码解读（232）- 查询#125(NOT IN实现#3)

2024-04-02 19:04:59 663人浏览安东尼

摘要

本节介绍了postgresql含有

本节介绍了postgresql含有NOT IN查询语句实现ExecMaterial函数中部分依赖的函数。

一、数据结构

SubPlanState
子计划运行期状态




typedef struct SubPlanState
{
    nodeTag        type;
    SubPlan    *subplan;        
    struct PlanState *planstate;    
    struct PlanState *parent;    
    ExprState  *testexpr;        
    List       *args;            
    HeapTuple    curTuple;        
    Datum        curArray;        
    
    TupleDesc    descRight;        
    ProjectionInfo *projLeft;    
    ProjectionInfo *projRight;    
    TupleHashTable hashtable;    
    TupleHashTable hashnulls;    
    bool        havehashrows;    
    bool        havenullrows;    
    MemoryContext hashtablecxt; 
    MemoryContext hashtempcxt;    
    ExprContext *innerecontext; 
    AttrNumber *keyColIdx;        
    Oid           *tab_eq_funcoids;    
    Oid           *tab_collations; 
    FmgrInfo   *tab_hash_funcs; 
    FmgrInfo   *tab_eq_funcs;    
    FmgrInfo   *lhs_hash_funcs; 
    FmgrInfo   *cur_eq_funcs;    
    ExprState  *cur_eq_comp;    
} SubPlanState;

SubPlan
子查询计划




typedef struct SubPlan
{
    Expr        xpr;//表达式
    
    //从SubLink中拷贝而来
    SubLinkType subLinkType;    
    
    //组合操作符,转换为可执行的表达式
    Node       *testexpr;        
    List       *paramIds;        
    
    //Plan tree标识
    int            plan_id;        
    
    //EXPLaiN和debug目的的SubPlan标识
    char       *plan_name;        
    
    //用于确定subplan输出类型的额外信息
    Oid            firstColType;    
    int32        firstColTypmod; 
    Oid            firstColCollation;    
    
    //执行阶段的相关信息
    bool        useHashTable;    
    bool        unknownEqFalse; 
    bool        parallel_safe;    
    
    
    //用于给子查询传入和传出参数的信息
    
    //setParam和parParam是整型链表(param IDs)
    List       *setParam;        
    List       *parParam;        
    List       *args;            
    
    //估算执行成本
    Cost        startup_cost;    
    Cost        per_call_cost;    
} SubPlan;

SubLinkType
SubLink类型




typedef enum SubLinkType
{
    EXISTS_SUBLINK,
    ALL_SUBLINK,
    ANY_SUBLINK,
    ROWCOMPARE_SUBLINK,
    EXPR_SUBLINK,
    MULTIEXPR_SUBLINK,
    ARRAY_SUBLINK,
    CTE_SUBLINK                    
} SubLinkType;

SubLink
SubLink结构体



typedef struct SubLink
{
    Expr        xpr;
    SubLinkType subLinkType;    
    int            subLinkId;        
    Node       *testexpr;        
    List       *operName;        
    Node       *subselect;        
    int            location;        
} SubLink;

MaterialState
Material状态



typedef struct MaterialState
{
    ScanState    ss;                
    int            eflags;            
    bool        eof_underlying; 
    Tuplestorestate *tuplestorestate;
} MaterialState;

二、源码解读

ExecMaterial
执行物化操作。




static TupleTableSlot *            
ExecMaterial(PlanState *pstate)
{
    MaterialState *node = castNode(MaterialState, pstate);//物化节点
    EState       *estate;//运行期状态
    ScanDirection dir;//扫描方向
    bool        forward;//是否往前扫描
    Tuplestorestate *tuplestorestate;//Tuplestorestate结构体指针
    bool        eof_tuplestore;//是否完成?
    TupleTableSlot *slot;//存储元组的slot
    CHECK_FOR_INTERRUPTS();
    
    estate = node->ss.ps.state;
    dir = estate->es_direction;//方向
    forward = ScanDirectionIsForward(dir);//是否往前扫描
    tuplestorestate = node->tuplestorestate;
    
    if (tuplestorestate == NULL && node->eflags != 0)
    {
        tuplestorestate = tuplestore_begin_heap(true, false, work_mem);
        tuplestore_set_eflags(tuplestorestate, node->eflags);
        if (node->eflags & EXEC_FLAG_MARK)
        {
            
            int            ptrno PG_USED_FOR_ASSERTS_ONLY;
            ptrno = tuplestore_alloc_read_pointer(tuplestorestate,
                                                  node->eflags);
            Assert(ptrno == 1);
        }
        node->tuplestorestate = tuplestorestate;
    }
    
    eof_tuplestore = (tuplestorestate == NULL) ||
        tuplestore_ateof(tuplestorestate);
    if (!forward && eof_tuplestore)
    {
        if (!node->eof_underlying)
        {
            
            if (!tuplestore_advance(tuplestorestate, forward))
                return NULL;    
        }
        eof_tuplestore = false;
    }
    
    slot = node->ss.ps.ps_ResultTupleSlot;
    if (!eof_tuplestore)
    {
        if (tuplestore_gettupleslot(tuplestorestate, forward, false, slot))
            return slot;
        if (forward)
            eof_tuplestore = true;
    }
    
    if (eof_tuplestore && !node->eof_underlying)
    {
        PlanState  *outerNode;
        TupleTableSlot *outerslot;
        
        outerNode = outerPlanState(node);
        outerslot = ExecProcNode(outerNode);
        if (TupIsNull(outerslot))
        {
            node->eof_underlying = true;
            return NULL;
        }
        
        if (tuplestorestate)
            tuplestore_puttupleslot(tuplestorestate, outerslot);
        ExecCopySlot(slot, outerslot);
        return slot;
    }
    
    return ExecClearTuple(slot);
}

tuplestore_begin_heap
初始化tuplestore




Tuplestorestate *
tuplestore_begin_heap(bool randoMaccess, bool interXact, int maxKBytes)
{
    Tuplestorestate *state;
    int            eflags;
    
    eflags = randomAccess ?
        (EXEC_FLAG_BACKWARD | EXEC_FLAG_REWIND) :
        (EXEC_FLAG_REWIND);
    state = tuplestore_begin_common(eflags, interXact, maxKBytes);
    state->copytup = copytup_heap;
    state->writetup = writetup_heap;
    state->readtup = readtup_heap;
    return state;
}

static Tuplestorestate *
tuplestore_begin_common(int eflags, bool interXact, int maxKBytes)
{
    Tuplestorestate *state;
    state = (Tuplestorestate *) palloc0(sizeof(Tuplestorestate));
    state->status = TSS_INMEM;
    state->eflags = eflags;
    state->interXact = interXact;
    state->truncated = false;
    state->allowedMem = maxKBytes * 1024L;
    state->availMem = state->allowedMem;
    state->myfile = NULL;
    state->context = CurrentMemoryContext;
    state->resowner = CurrentResourceOwner;
    state->memtupdeleted = 0;
    state->memtupcount = 0;
    state->tuples = 0;
    
    state->memtupsize = Max(16384 / sizeof(void *),
                            ALLOCSET_SEPARATE_THRESHOLD / sizeof(void *) + 1);
    state->growmemtuples = true;
    state->memtuples = (void **) palloc(state->memtupsize * sizeof(void *));
    USEMEM(state, GetMemoryChunkSpace(state->memtuples));
    state->activeptr = 0;
    state->readptrcount = 1;
    state->readptrsize = 8;        
    state->readptrs = (TSReadPointer *)
        palloc(state->readptrsize * sizeof(TSReadPointer));
    state->readptrs[0].eflags = eflags;
    state->readptrs[0].eof_reached = false;
    state->readptrs[0].current = 0;
    return state;
}

tuplestore_advance
从tuplestore前进一行




bool
tuplestore_advance(Tuplestorestate *state, bool forward)
{
    void       *tuple;
    bool        should_free;
    tuple = tuplestore_gettuple(state, forward, &should_free);
    if (tuple)
    {
        if (should_free)
            pfree(tuple);
        return true;
    }
    else
    {
        return false;
    }
}

tuplestore_gettupleslot
获取slot




bool
tuplestore_gettupleslot(Tuplestorestate *state, bool forward,
                        bool copy, TupleTableSlot *slot)
{
    MinimalTuple tuple;
    bool        should_free;
    tuple = (MinimalTuple) tuplestore_gettuple(state, forward, &should_free);
    if (tuple)
    {
        if (copy && !should_free)
        {
            tuple = heap_copy_minimal_tuple(tuple);
            should_free = true;
        }
        ExecStoreMinimalTuple(tuple, slot, should_free);
        return true;
    }
    else
    {
        ExecClearTuple(slot);
        return false;
    }
}

tuplestore_gettuple
返回下一个元组




static void *
tuplestore_gettuple(Tuplestorestate *state, bool forward,
                    bool *should_free)
{
    TSReadPointer *readptr = &state->readptrs[state->activeptr];//读取指针
    unsigned int tuplen;
    void       *tup;
    Assert(forward || (readptr->eflags & EXEC_FLAG_BACKWARD));
    switch (state->status)
    {
        case TSS_INMEM://内存中
            *should_free = false;
            if (forward)
            {
                if (readptr->eof_reached)
                    return NULL;
                if (readptr->current < state->memtupcount)
                {
                    
                    return state->memtuples[readptr->current++];
                }
                readptr->eof_reached = true;
                return NULL;
            }
            else
            {
                
                if (readptr->eof_reached)
                {
                    readptr->current = state->memtupcount;
                    readptr->eof_reached = false;
                }
                else
                {
                    if (readptr->current <= state->memtupdeleted)
                    {
                        Assert(!state->truncated);
                        return NULL;
                    }
                    readptr->current--; 
                }
                if (readptr->current <= state->memtupdeleted)
                {
                    Assert(!state->truncated);
                    return NULL;
                }
                return state->memtuples[readptr->current - 1];
            }
            break;
        case TSS_WRITEFILE://写文件
            
            //如只需要返回NULL则跳过状态变换
            if (readptr->eof_reached && forward)
                return NULL;
            
            BufFileTell(state->myfile,
                        &state->writepos_file, &state->writepos_offset);
            if (!readptr->eof_reached)
                if (BufFileSeek(state->myfile,
                                readptr->file, readptr->offset,
                                SEEK_SET) != 0)
                    ereport(ERROR,
                            (errcode_for_file_access(),
                             errmsg("could not seek in tuplestore temporary file: %m")));
            state->status = TSS_READFILE;
            
            //进入读文件状态的处理逻辑
        case TSS_READFILE:
            *should_free = true;
            if (forward)
            {
                //往前读
                if ((tuplen = getlen(state, true)) != 0)
                {
                    tup = READTUP(state, tuplen);
                    return tup;
                }
                else
                {
                    readptr->eof_reached = true;
                    return NULL;
                }
            }
            
            if (BufFileSeek(state->myfile, 0, -(long) sizeof(unsigned int),
                            SEEK_CUR) != 0)
            {
                
                readptr->eof_reached = false;
                Assert(!state->truncated);
                return NULL;
            }
            tuplen = getlen(state, false);
            if (readptr->eof_reached)
            {
                readptr->eof_reached = false;
                
                //在返回NULL前返回先前已返回的元组
            }
            else
            {
                
                if (BufFileSeek(state->myfile, 0,
                                -(long) (tuplen + 2 * sizeof(unsigned int)),
                                SEEK_CUR) != 0)
                {
                    
                    if (BufFileSeek(state->myfile, 0,
                                    -(long) (tuplen + sizeof(unsigned int)),
                                    SEEK_CUR) != 0)
                        ereport(ERROR,
                                (errcode_for_file_access(),
                                 errmsg("could not seek in tuplestore temporary file: %m")));
                    Assert(!state->truncated);
                    return NULL;
                }
                tuplen = getlen(state, false);
            }
            
            if (BufFileSeek(state->myfile, 0,
                            -(long) tuplen,
                            SEEK_CUR) != 0)
                ereport(ERROR,
                        (errcode_for_file_access(),
                         errmsg("could not seek in tuplestore temporary file: %m")));
            tup = READTUP(state, tuplen);
            return tup;
        default:
            elog(ERROR, "invalid tuplestore state");
            return NULL;        
    }
}

三、跟踪分析

执行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 ExecMaterial
Breakpoint 1 at 0x720edb: file nodeMaterial.c, line 41.
(gdb) c
Continuing.
Breakpoint 1, ExecMaterial (pstate=0x1230128) at nodeMaterial.c:41
41        MaterialState *node = castNode(MaterialState, pstate);
(gdb)

单步调试


(gdb) n
49        CHECK_FOR_INTERRUPTS();
(gdb) 
54        estate = node->ss.ps.state;
(gdb) 
55        dir = estate->es_direction;
(gdb) 
56        forward = ScanDirectionIsForward(dir);
(gdb) 
57        tuplestorestate = node->tuplestorestate;
(gdb) 
62        if (tuplestorestate == NULL && node->eflags != 0)
(gdb) 
64            tuplestorestate = tuplestore_begin_heap(true, false, work_mem);
(gdb) 
65            tuplestore_set_eflags(tuplestorestate, node->eflags);
(gdb) 
66            if (node->eflags & EXEC_FLAG_MARK)
(gdb) 
78            node->tuplestorestate = tuplestorestate;
(gdb) 
85        eof_tuplestore = (tuplestorestate == NULL) ||
(gdb) 
86            tuplestore_ateof(tuplestorestate);
(gdb) 
85        eof_tuplestore = (tuplestorestate == NULL) ||
(gdb) 
88        if (!forward && eof_tuplestore)
(gdb) p eof_tuplestore
$1 = false
(gdb)

进入tuplestore_gettupleslot


(gdb) n
107        slot = node->ss.ps.ps_ResultTupleSlot;
(gdb) 
108        if (!eof_tuplestore)
(gdb) 
110            if (tuplestore_gettupleslot(tuplestorestate, forward, false, slot))
(gdb) step
tuplestore_gettupleslot (state=0x3069c18, forward=true, copy=false, slot=0x30687a8)
    at tuplestore.c:1084
1084        tuple = (MinimalTuple) tuplestore_gettuple(state, forward, &should_free);
(gdb)

进入tuplestore_gettuple


(gdb) step
tuplestore_gettuple (state=0x3069c18, forward=true, should_free=0x7ffd18474ff7)
    at tuplestore.c:906
906        TSReadPointer *readptr = &state->readptrs[state->activeptr];
(gdb)

tuplestore_gettuple->文件读写指针信息


(gdb) n
910        Assert(forward || (readptr->eflags & EXEC_FLAG_BACKWARD));
(gdb) p *readptr
$2 = {eflags = 2, eof_reached = false, current = 0, file = 2139062143, 
  offset = 9187201950435737471}

tuplestore_gettuple->当前状态为TSS_INMEM


(gdb) n
912        switch (state->status)
(gdb) p *state
$3 = {status = TSS_INMEM, eflags = 2, backward = false, interXact = false, 
  truncated = false, availMem = 4177896, allowedMem = 4194304, tuples = 0, myfile = 0x0, 
  context = 0x3067da0, resowner = 0x2fa62c8, copytup = 0xaba7bd <copytup_heap>, 
  writetup = 0xaba811 <writetup_heap>, readtup = 0xaba9d9 <readtup_heap>, 
  memtuples = 0x3051e90, memtupdeleted = 0, memtupcount = 0, memtupsize = 2048, 
  growmemtuples = true, readptrs = 0x3077f70, activeptr = 0, readptrcount = 1, 
  readptrsize = 8, writepos_file = 0, writepos_offset = 0}
(gdb) p state->status
$4 = TSS_INMEM
(gdb)

tuplestore_gettuple->返回NULL


(gdb) n
915                *should_free = false;
(gdb) n
916                if (forward)
(gdb) 
918                    if (readptr->eof_reached)
(gdb) 
920                    if (readptr->current < state->memtupcount)
(gdb) p readptr->current
$5 = 0
(gdb) p state->memtupcount
$6 = 0
(gdb) n
925                    readptr->eof_reached = true;
(gdb) 
926                    return NULL;
(gdb) 
1062    }
(gdb)

tuplestore_gettupleslot->返回false


(gdb) n
tuplestore_gettupleslot (state=0x3069c18, forward=true, copy=false, slot=0x30687a8)
    at tuplestore.c:1086
1086        if (tuple)
(gdb) 
1098            ExecClearTuple(slot);
(gdb) 
1099            return false;
(gdb)

回到ExecMaterial


(gdb) n
1101    }
(gdb) 
ExecMaterial (pstate=0x3068158) at nodeMaterial.c:112
112            if (forward)
(gdb) 
113                eof_tuplestore = true;
(gdb)

从outerPlan中获取一行（即从t_big_null中获取一行）


(gdb) n
124        if (eof_tuplestore && !node->eof_underlying)
(gdb) p node->eof_underlying
$7 = false
(gdb) n
133            outerNode = outerPlanState(node);
(gdb) 
###
#define innerPlanState(node)        (((PlanState *)(node))->righttree)
#define outerPlanState(node)        (((PlanState *)(node))->lefttree)
###
134            outerslot = ExecProcNode(outerNode);
(gdb) p outerNode
$8 = (PlanState *) 0x3068270
(gdb) p *outerNode
$9 = {type = T_SeqScanState, plan = 0x3037628, state = 0x3067eb8, 
  ExecProcNode = 0x6f802a <ExecProcNodeFirst>, ExecProcNodeReal = 0x72b904 <ExecSeqScan>, 
  instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0, qual = 0x0, 
  lefttree = 0x0, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, chgParam = 0x0, 
  ps_ResultTupleDesc = 0x3068578, ps_ResultTupleSlot = 0x0, ps_ExprContext = 0x3068388, 
  ps_ProjInfo = 0x0, scandesc = 0x7fab449cae98, 
  scanops = 0xc3e780 <TTSOpsBufferHeapTuple>, outerops = 0x0, innerops = 0x0, 
  resultops = 0xc3e780 <TTSOpsBufferHeapTuple>, scanopsfixed = true, 
  outeropsfixed = false, inneropsfixed = false, resultopsfixed = true, scanopsset = true, 
  outeropsset = false, inneropsset = false, resultopsset = true}
(gdb) p *outerNode->state
$10 = {type = T_EState, es_direction = ForwardScanDirection, es_snapshot = 0x2f9cd10, 
  es_crosscheck_snapshot = 0x0, es_range_table = 0x3042130, 
  es_range_table_array = 0x3068108, es_range_table_size = 2, es_relations = 0x3068130, 
  es_rowmarks = 0x0, es_plannedstmt = 0x3042438, 
  es_sourceText = 0x2f74d88 "select * from tbl a where a.id not in (select b.id from t_big_null b);", 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_partition_directory = 0x0, es_tuple_routing_result_relations = 0x0, 
  es_trig_target_relations = 0x0, es_param_list_info = 0x0, 
  es_param_exec_vals = 0x30680d0, es_queryEnv = 0x0, es_query_cxt = 0x3067da0, 
  es_tupleTable = 0x3068540, es_processed = 0, es_top_eflags = 16, es_instrument = 0, 
  es_finished = false, es_exprcontexts = 0x3068448, es_subplanstates = 0x3068950, 
  es_auxmodifytables = 0x0, es_per_tuple_exprcontext = 0x0, es_epq_active = 0x0, 
  es_use_parallel_mode = false, es_query_dsa = 0x0, es_jit_flags = 25, es_jit = 0x0, 
  es_jit_worker_instr = 0x0}
(gdb) p ((PlanState *)node)->righttree
$21 = (struct PlanState *) 0x0
(gdb)

回过头来看执行计划，Materialize Node的lefttree是Seq Scan on public.t_big_null b，righttree为NULL。


[local]:5432 pg12@testdb=# explain verbose select * from tbl a where a.id not in (select b.id from t_big_null b);
                                         QUERY PLAN                                        
-------------------------------------------------------------------------------------------
-
 Seq Scan on public.tbl a  (cost=0.00..129156.33 rows=1 width=8)
   Output: a.id, a.value
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..233310.68 rows=9999979 width=4)
           Output: b.id
           ->  Seq Scan on public.t_big_null b  (cost=0.00..144247.79 rows=9999979 width=4)
                 Output: b.id
(8 rows)
Time: 7.681 ms

获取outerslot


(gdb) n
135            if (TupIsNull(outerslot))
(gdb) p *outerslot
$16 = {type = T_TupleTableSlot, tts_flags = 16, tts_nvalid = 0, 
  tts_ops = 0xc3e780 <TTSOpsBufferHeapTuple>, tts_tupleDescriptor = 0x7fab449cae98, 
  tts_values = 0x30684f0, tts_isnull = 0x30684f8, tts_mcxt = 0x3067da0, tts_tid = {
    ip_blkid = {bi_hi = 0, bi_lo = 0}, ip_posid = 1}, tts_tableOid = 49155}
(gdb) p *outerslot->tts_values
$17 = 0
(gdb) p outerslot->tts_values[1]
$18 = 0
(gdb) p outerslot->tts_values[0]
$19 = 0
(gdb) p *outerslot->tts_tupleDescriptor
$20 = {natts = 1, tdtypeid = 49157, tdtypmod = -1, tdrefcount = 2, constr = 0x0, 
  attrs = 0x7fab449caeb0}

获取outerslot后，put到tuplestore中


(gdb) p *node
$22 = {ss = {ps = {type = T_MaterialState, plan = 0x3040a60, state = 0x3067eb8, 
      ExecProcNode = 0x720ecf <ExecMaterial>, ExecProcNodeReal = 0x720ecf <ExecMaterial>, 
      instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0, qual = 0x0, 
      lefttree = 0x3068270, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, 
      chgParam = 0x0, ps_ResultTupleDesc = 0x3068690, ps_ResultTupleSlot = 0x30687a8, 
      ps_ExprContext = 0x0, ps_ProjInfo = 0x0, scandesc = 0x3068578, 
      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}, 
    ss_currentRelation = 0x0, ss_currentScanDesc = 0x0, ss_ScanTupleSlot = 0x3068868}, 
  eflags = 2, eof_underlying = false, tuplestorestate = 0x3069c18}
(gdb) n
146            if (tuplestorestate)
(gdb) 
147                tuplestore_puttupleslot(tuplestorestate, outerslot);
(gdb) p outerslot->tts_values[0]
$23 = 0
(gdb) n
149            ExecCopySlot(slot, outerslot);
(gdb) p outerslot->tts_values[0]
$24 = 0
(gdb) n
150            return slot;
(gdb) p outerslot->tts_values[0]
$25 = 0
(gdb) p slot->tts_values[0]
$26 = 0
(gdb) n
157    }
(gdb)

继续“物化”


(gdb) n
ExecProcNodeFirst (node=0x3068158) at execProcnode.c:446
446    }
(gdb) c
Continuing.
Breakpoint 1, ExecMaterial (pstate=0x3068158) at nodeMaterial.c:41
41        MaterialState *node = castNode(MaterialState, pstate);
(gdb) n
49        CHECK_FOR_INTERRUPTS();
(gdb) 
54        estate = node->ss.ps.state;
(gdb) 
55        dir = estate->es_direction;
(gdb) 
56        forward = ScanDirectionIsForward(dir);
(gdb) 
57        tuplestorestate = node->tuplestorestate;
(gdb) 
62        if (tuplestorestate == NULL && node->eflags != 0)
(gdb) 
85        eof_tuplestore = (tuplestorestate == NULL) ||
(gdb) 
86            tuplestore_ateof(tuplestorestate);
(gdb) 
85        eof_tuplestore = (tuplestorestate == NULL) ||
(gdb) 
88        if (!forward && eof_tuplestore)
(gdb) 
107        slot = node->ss.ps.ps_ResultTupleSlot;
(gdb) 
108        if (!eof_tuplestore)
(gdb) 
124        if (eof_tuplestore && !node->eof_underlying)
(gdb) 
133            outerNode = outerPlanState(node);
(gdb) p eof_tuplestore
$27 = true
(gdb) n
134            outerslot = ExecProcNode(outerNode);
(gdb) 
135            if (TupIsNull(outerslot))
(gdb) 
146            if (tuplestorestate)
(gdb) 
147                tuplestore_puttupleslot(tuplestorestate, outerslot);
(gdb) 
149            ExecCopySlot(slot, outerslot);
(gdb) 
150            return slot;
(gdb) p slot->tts_values[0]
$28 = 2
(gdb)

第一次执行时间较久，第二次相对快2个数量级，需要继续研究。


[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: 3633462.666 ms (01:00:33.463) --> 包括了debug的时间，实际时间是5s左右
[local]:5432 pg12@testdb=# 
[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: 6.480 ms --> 第2+次就快很多
[local]:5432 pg12@testdb=#

DONE