PostgreSQL execution engine – execScan projection filtering and predicate filtering

The previous blog PostgreSQL database query execution – SeqScan node execution introduced the execution of the SeqScan node. This blog mainly focuses on describing the execution of execScan predicate filtering. This code provides support for generalized relation scanning. ExecScan is passed a node and a pointer to a function to “do the right thing” and return a tuple from the relation. ExecScan then does some tedious stuff – checking the qualification and projecting the tuple appropriately. This code provides support for generalized relation scans. ExecScan is passed a node and a pointer to a function to “do the right thing” and return a tuple from the relation. ExecScan then does the tedious stuff – checking the qualification and projecting the tuple appropriately .

Scan operation supported by PG

The SCAN nodes supported by the PostgreSQL database are as follows T_SeqScan, T_SampleScan, T_IndexScan, T_IndexOnlyScan, T_BitmapIndexScan, T_BitmapHeapScan, T_TidScan, T_SubqueryScan, T_FunctionScan, T_ValuesScan, T_TableFuncScan, T_CteScan, T_NamedTuplestoreScan, T_Work_TableScanus, T_Work_TableScan. The create_scan_plan function (src/backend/optimizer/plan/createplan.c file Create a scan plan for the parent relation of ‘best_path’) converts the best Path to a SCAN node other than the T_BitmapIndexScan node Plan structure. Its execution process is as follows:

  1. Extract relevant restriction clauses as scan_clauses. If it is indexscan or index-only scan, use best_path->indexinfo->indrestrictinfo as restriction clauses; for other scans, extract relevant constraint clauses of parent relation, use best_path->parent-> baserestrictinfo as restriction clauses.
  2. Check whether there is a pseudoconstant clause. If so, you need to insert a gating Result node (evaluates the pseudoconstants as one-time quals), which can be used for projection. There is no requirement for the child node tlist. Use get_gating_quals(root, scan_clauses) to get the gating clause, and use create_gating_plan(root, best_path, plan, gating_clauses) to create the gating Result node.
  3. For table scan, use the tlist matching relation structure; otherwise use all Vars to generate tlist.
  4. Create the corresponding Plan according to the SCAN node type. For example, for seqscan, create_seqscan_plan(root, best_path, tlist, scan_clauses) assigns tlist to SeqScan.plan->targetlist, sorts scan_clauses [using order_qual_clauses(root, scan_clauses) to the best execution order, and uses extract_actual_clauses(scan_clauses , false) Crop the RestrictInfo list to bare expressions, use replace_nestloop_params(root, (Node *) scan_clauses) to replace outer-relation variables with nestloop params] assign to SeqScan.plan->qual, assign best_path->parent->relid For SeqScan.scanrelid, use copy_generic_path_info( & amp;scan_plan->plan, best_path) to copy the execution plan cost variables to plan (startup_cost, total_cost, plan_rows, plan_width, parallel_aware, parallel_safe).
static Plan *create_scan_plan(PlannerInfo *root, Path *best_path, int flags){<!-- -->
RelOptInfo *rel = best_path->parent;
\t
/* Extract the relevant restriction clauses from the parent relation. The executor must apply all these restrictions during the scan, except for pseudoconstants which we'll take care of below. If this is a plain indexscan or index-only scan, we need not consider restriction clauses that are implied by the index's predicate, so use indrestrictinfo not baserestrictinfo. Note that we can't do that for bitmap indexscans, since there's not necessarily a single index involved; able to get rid of such clauses anyway via predicate proof. */
List *scan_clauses;
switch (best_path->pathtype){<!-- -->
case T_IndexScan: case T_IndexOnlyScan:
scan_clauses = castNode(IndexPath, best_path)->indexinfo->inrestrictinfo; break;
default:
scan_clauses = rel->baserestrictinfo; break;
}
/* If this is a parameterized scan, we also need to enforce all the join clauses available from the outer relation(s). For paranoia's sake, don't modify the stored baserestrictinfo list. */
if (best_path->param_info) scan_clauses = list_concat(list_copy(scan_clauses), best_path->param_info->ppi_clauses);

/* Detect whether we have any pseudoconstant quals to deal with. Then, if we'll need a gating Result node, it will be able to project, so there are no requirements on the child's tlist. */
List *gating_clauses = get_gating_quals(root, scan_clauses);
if (gating_clauses) flags = 0;

/* For table scans, rather than using the relation targetlist (which is only those Vars actually needed by the query), we prefer to generate a tlist containing all Vars in order. This will allow the executor to optimize away projection of the table tuples , if possible. But if the caller is going to ignore our tlist anyway, then don't bother generating one at all. We use an exact equality test here, so that this only applies when CP_IGNORE_TLIST is the only flag set. */
List *tlist;
if (flags == CP_IGNORE_TLIST) {<!-- --> tlist = NULL;
}else if (use_physical_tlist(root, best_path, flags)){<!-- -->
if (best_path->pathtype == T_IndexOnlyScan){<!-- -->
tlist = copyObject(((IndexPath *) best_path)->indexinfo->indexlist); /* For index-only scan, the preferred tlist is the index's */
if (flags & amp; CP_LABEL_TLIST) apply_pathtarget_labeling_to_tlist(tlist, best_path->pathtarget); /* Transfer sortgroupref data to the replacement tlist, if requested (use_physical_tlist checked that this will work). */
}else{<!-- -->
tlist = build_physical_tlist(root, rel);
if (tlist == NIL){<!-- -->/* Failed because of dropped cols, so use regular method */
tlist = build_path_tlist(root, best_path);
}else{<!-- -->/* As above, transfer sortgroupref data to replacement tlist */
if (flags & CP_LABEL_TLIST) apply_pathtarget_labeling_to_tlist(tlist, best_path->pathtarget);
}
}
}else{<!-- -->tlist = build_path_tlist(root, best_path);}
\t
    Plan *plan;
switch (best_path->pathtype){<!-- -->
case T_SeqScan: plan = (Plan *) create_seqscan_plan(root,best_path,tlist,scan_clauses);break;
case T_SampleScan: plan = (Plan *) create_samplescan_plan(root, best_path, tlist, scan_clauses); break;
case T_IndexScan: plan = (Plan *) create_indexscan_plan(root, (IndexPath *) best_path, tlist, scan_clauses, false); break;
case T_IndexOnlyScan: plan = (Plan *) create_indexscan_plan(root, (IndexPath *) best_path, tlist, scan_clauses, true); break;
case T_BitmapHeapScan: plan = (Plan *) create_bitmap_scan_plan(root, (BitmapHeapPath *) best_path, tlist, scan_clauses); break;
case T_TidScan: plan = (Plan *) create_tidscan_plan(root,(TidPath *) best_path, tlist, scan_clauses); break;
case T_SubqueryScan: plan = (Plan *) create_subqueryscan_plan(root, (SubqueryScanPath *) best_path, tlist, scan_clauses); break;
case T_FunctionScan: plan = (Plan *) create_functionscan_plan(root, best_path, tlist, scan_clauses); break;
case T_TableFuncScan: plan = (Plan *) create_tablefuncscan_plan(root, best_path, tlist, scan_clauses); break;
case T_ValuesScan: plan = (Plan *) create_valuesscan_plan(root, best_path, tlist, scan_clauses); break;
case T_CteScan: plan = (Plan *) create_ctescan_plan(root, best_path, tlist, scan_clauses); break;
case T_NamedTuplestoreScan: plan = (Plan *) create_namedtuplestorescan_plan(root, best_path, tlist, scan_clauses); break;
case T_Result: plan = (Plan *) create_resultscan_plan(root, best_path, tlist, scan_clauses); break;
case T_WorkTableScan: plan = (Plan *) create_worktablescan_plan(root, best_path, tlist, scan_clauses); break;
case T_ForeignScan: plan = (Plan *) create_foreignscan_plan(root, (ForeignPath *) best_path, tlist, scan_clauses); break;
case T_CustomScan: plan = (Plan *) create_customscan_plan(root, (CustomPath *) best_path, tlist, scan_clauses); break;
default: elog(ERROR, "unrecognized node type: %d",(int) best_path->pathtype); plan = NULL; /* keep compiler quiet */ break;
}
\t
if (gating_clauses) plan = create_gating_plan(root, best_path, plan, gating_clauses); /* If there are any pseudoconstant clauses attached to this node, insert a gating Result node that evaluates the pseudoconstants as one-time quals. */
return plan;
}

Projection filtering and predicate filtering initialization

The ability of execScan to provide predicate projection is implemented by the ExecAssignScanProjectionInfo and ExecAssignScanProjectionInfoWithVarno functions.
src/backend/executor/nodeBitmapHeapscan.c file ExecInitBitmapHeapScan function, src/backend/executor/nodeCtescan.c file ExecInitCteScan function, src/backend/executor/nodeFunctionscan.c file ExecInitFunctionScan function, src/backend/executor/nodeIndexscan.c file ExecInitIndexScan Function, src/backend/executor/nodeNamedtuplestorescan.c file ExecInitNamedTuplestoreScan function, src/backend/executor/nodeSamplescan.c file ExecInitSampleScan function, src/backend/executor/nodeSeqscan.c file ExecInitSeqScan function, src/backend/executor/nodeSubqueryscan.c File ExecInitSubqueryScan function, src/backend/executor/nodeTableFuncscan.c file ExecInitTableFuncScan function, src/backend/executor/nodeTidscan.c file ExecInitTidScan function, src/backend/executor/nodeValuesscan.c file ExecInitValuesScan function, src/backend/executor/nodeWorktablescan The ExecWorkTableScan function in the .c file will call ExecAssignScanProjectionInfo to create projection information for the scan node.

The ExecInitCustomScan function in the src/backend/executor/nodeCustom.c file, the ExecInitForeignScan function in the src/backend/executor/nodeForeignscan.c file, and the ExecInitIndexOnlyScan function in the src/backend/executor/nodeIndexonlyscan.c file will call ExecAssignScanProjectionInfoWithVarno to create projection information for the scan node .

execScan provides predicate filtering capabilities except for T_BitmapIndexScan and T_CustomScan applied to other SCAN nodes:

  • src/backend/executor/nodeBitmapHeapscan.c ExecInitBitmapHeapScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeCtescan.c ExecInitCteScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeForeignscan.c ExecInitForeignScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate) and ExecInitQual(node->fdw_recheck_quals, (PlanState * ) scanstate)
  • src/backend/executor/nodeFunctionscan.c ExecInitFunctionScan function execution ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeIndexonlyscan.c ExecInitIndexOnlyScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) indexstate) and ExecInitQual(node->indexqual, (PlanState * ) indexstate)
  • src/backend/executor/nodeIndexscan.c ExecInitIndexScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) indexstate)
  • src/backend/executor/nodeNamedtuplestorescan.c ExecInitNamedTuplestoreScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeSamplescan.c ExecInitSampleScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeSeqscan.c ExecInitSeqScan function execution scanstate->ss.ps.qual = ExecInitQual(node->plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeSubqueryscan.c ExecInitSubqueryScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) subquerystate)
  • src/backend/executor/nodeTableFuncscan.c ExecInitTableFuncScan function executes ExecInitQual(node->scan.plan.qual, & amp;scanstate->ss.ps)
  • src/backend/executor/nodeTidscan.c ExecInitTidScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) tidstate)
  • src/backend/executor/nodeValuesscan.c ExecInitValuesScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)
  • src/backend/executor/nodeWorktablescan.c ExecInitWorkTableScan function executes ExecInitQual(node->scan.plan.qual, (PlanState *) scanstate)

ExecScan projection filtering and predicate filtering

The ExecScan function uses the access method to scan the relation table, and returns a tuple whose scanning direction specified in the global variable ExecDirection meets the requirements. The access method is responsible for returning the next tuple and ExecScan is responsible for checking the tuple using the qual-clause. The recheck method must provide an arbitrary tuple that can check the relation table against any qual conditions implemented internally by the access method.

TupleTableSlot *ExecScan(ScanState *node, ExecScanAccessMtd accessMtd, ExecScanRecheckMtd recheckMtd){<!-- -->
ExprState *qual = node->ps.qual; ProjectionInfo *projInfo = node->ps.ps_ProjInfo;

    ExprContext *econtext = node->ps.ps_ExprContext; /* Fetch data from node */
if (!qual & amp; & amp; !projInfo){<!-- --> /* If we have neither a qual to check nor a projection to do, just skip all the overhead and return the raw scan tuple. * // No need for predicate filtering and projection
ResetExprContext(econtext);
return ExecScanFetch(node, accessMtd, recheckMtd);
}
ResetExprContext(econtext); /* Reset per-tuple memory context to free any expression evaluation storage allocated in the previous tuple cycle. */

/* get a tuple from the access method. Loop until we obtain a tuple that passes the qualification. */
for (;;){<!-- -->
TupleTableSlot *slot = ExecScanFetch(node, accessMtd, recheckMtd);

/* if the slot returned by the accessMtd contains NULL, then it means there is nothing more to scan so we just return an empty slot, being careful to use the projection result slot so it has correct tupleDesc. */ // if by accessMtd The returned slot contains NULL, so there is no need to scan again and return an empty slot directly. Special handling for projInfo.
if (TupIsNull(slot)){<!-- -->
if (projInfo) return ExecClearTuple(projInfo->pi_state.resultslot);
else return slot;
}

\t\t
econtext->ecxt_scantuple = slot; /* place the current tuple into the expr context */
/* check that the current tuple satisfies the qual-clause
* check for non-null qual here to avoid a function call to ExecQual() when the qual is null ... saves only a few cycles, but they add up ... */
if (qual == NULL || ExecQual(qual, econtext)){<!-- --> /* Found a satisfactory scan tuple. */
if (projInfo){<!-- --> /* Form a projection tuple, store it in the result tuple slot and return it. */
return ExecProject(projInfo);
}else{<!-- -->
return slot; /* Here, we aren't projecting, so just return scan tuple. */
}
}else InstrCountFiltered1(node, 1);
\t\t
ResetExprContext(econtext); /* Tuple fails qual, so free per-tuple memory and try again. */
}
}

The src/backend/executor/execScan.c file contains the ExecScan function caller:

  • src/backend/executor/nodeBitmapHeapscan.c ExecBitmapHeapScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) BitmapHeapNext, (ExecScanRecheckMtd) BitmapHeapRecheck)
  • src/backend/executor/nodeCtescan.c ExecCteScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) CteScanNext, (ExecScanRecheckMtd) CteScanRecheck)
  • src/backend/executor/nodeForeignscan.c ExecForeignScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) ForeignNext,(ExecScanRecheckMtd) ForeignRecheck)
  • src/backend/executor/nodeFunctionscan.c ExecFunctionScan function execution ExecScan( & amp;node->ss, (ExecScanAccessMtd) FunctionNext, (ExecScanRecheckMtd) FunctionRecheck)
  • src/backend/executor/nodeIndexonlyscan.c ExecIndexOnlyScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) IndexOnlyNext, (ExecScanRecheckMtd) IndexOnlyRecheck)
  • src/backend/executor/nodeIndexscan.c ExecIndexScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) IndexNextWithReorder,(ExecScanRecheckMtd) IndexRecheck) or ExecScan( & amp;node- >ss, (ExecScanAccessMtd) IndexNext, (ExecScanRecheckMtd) IndexRecheck)
  • src/backend/executor/nodeNamedtuplestorescan.c ExecNamedTuplestoreScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) NamedTuplestoreScanNext, (ExecScanRecheckMtd) NamedTuplestoreScanRecheck)
  • src/backend/executor/nodeSamplescan.c ExecSampleScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) SampleNext, (ExecScanRecheckMtd) SampleRecheck)
  • src/backend/executor/nodeSeqscan.c ExecSeqScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) SeqNext, (ExecScanRecheckMtd) SeqRecheck)
  • src/backend/executor/nodeSubqueryscan.c ExecSubqueryScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) SubqueryNext, (ExecScanRecheckMtd) SubqueryRecheck)
  • src/backend/executor/nodeTableFuncscan.c ExecTableFuncScan function execution ExecScan( & amp;node->ss, (ExecScanAccessMtd) TableFuncNext, (ExecScanRecheckMtd) TableFuncRecheck)
  • src/backend/executor/nodeTidscan.c ExecTidScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) TidNext, (ExecScanRecheckMtd) TidRecheck)
  • src/backend/executor/nodeValuesscan.c ExecValuesScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) ValuesNext, (ExecScanRecheckMtd) ValuesRecheck)
  • src/backend/executor/nodeWorktablescan.c ExecWorkTableScan function executes ExecScan( & amp;node->ss, (ExecScanAccessMtd) WorkTableScanNext, (ExecScanRecheckMtd) WorkTableScanRecheck)

ExecScanReScan

For the plan node that uses the ExecScan function, its ReScan function must also use ExecScanReScan.