Plan ID Jumbling
This page describes the proposed feature for Postgres 18 or 19 that records a planid, similar to the existing queryid recorded by query jumbling (previously done by pg_stat_statements).
See Commitfest entry and pgsql-hackers thread.
What to jumble
The current thesis behind what should be jumbled (included in the planid hash) is that plans that have the same EXPLAIN (COSTS OFF) output should yield the same planid. That is, a difference in "plan shape" is what constitutes a different planid, but different costs/selectivity or execution time statistics do not.
Note that plan jumbling relies on the existing query jumbling logic and decisions for any expressions, and as such e.g. ignores A_Const nodes, so a plan with different parameter values but the same shape will yield the same planid.
Plan jumbling is currently proposed to occur during the existing treewalk in src/backend/optimizer/plan/setrefs.c, and as such fields that would cause us to descend down the plan tree must be ignored for jumbling instead, noted as "Indirect" in the table below.
Further, to ease maintenance we jumble any field that is not explicitly causing issues with a changing planid, even if the field is not actually used by src/backend/commands/explain.c for showing EXPLAIN (COSTS OFF).
We could alternatively omit any fields that are duplicated (e.g. only have one of IndexScan.indexqual and IndexScan.indexqualorig), or omit those only used by the executor (e.g. Plan.extParam) to optimize for jumbling performance at the expense of higher maintenance overhead (review to be done) when adding new fields.
Jumbling details for all plan struct (plannodes.h) fields
For easier review/discussion, the table below represents all fields under consideration to be jumbled/not jumbled:
| Struct / Field | Include in Jumble Hash? | Why not? / Notes |
|---|---|---|
| Plan (abstract) | ||
| type | Yes | |
| disabled_nodes | No | Costing/selectivity information should be ignored |
| startup_cost | No | Costing/selectivity information should be ignored |
| total_cost | No | Costing/selectivity information should be ignored |
| plan_rows | No | Costing/selectivity information should be ignored |
| plan_width | No | Costing/selectivity information should be ignored |
| parallel_aware | Yes | |
| parallel_safe | Yes | |
| async_capable | Yes | |
| plan_node_id | Yes | |
| targetlist | Yes | |
| qual | Yes | |
| lefttree | Indirect | Jumbled via setrefs.c treewalk |
| righttree | Indirect | Jumbled via setrefs.c treewalk |
| initPlan | Indirect | Jumbled via planner.c subplans treewalk |
| extParam | Yes | |
| allParam | Yes | |
| Result | ||
| plan | Yes | |
| resconstantqual | Yes | |
| ProjectSet | ||
| plan | Yes | |
| ModifyTable | ||
| plan | Yes | |
| operation | Yes | |
| canSetTag | Yes | |
| nominalRelation | Yes | |
| rootRelation | Yes | |
| partColsUpdated | Yes | |
| resultRelations | Yes | |
| updateColnosLists | Yes | |
| withCheckOptionLists | Yes | |
| returningOldAlias | Yes | |
| returningNewAlias | Yes | |
| returningLists | Yes | |
| fdwPrivLists | No | Private data for the FDW |
| fdwDirectModifyPlans | Yes | |
| rowMarks | Yes | PlanRowMark nodes are ignored for jumbling |
| epqParam | Yes | |
| onConflictAction | Yes | |
| arbiterIndexes | Yes | |
| onConflictSet | Yes | |
| onConflictCols | Yes | |
| onConflictWhere | Yes | |
| exclRelRTI | Yes | |
| exclRelTlist | Yes | |
| mergeActionLists | Yes | |
| mergeJoinConditions | Yes | |
| Append | ||
| plan | Yes | |
| apprelids | Yes | |
| appendplans | Indirect | Jumbled via setrefs.c treewalk |
| nasyncplans | Yes | |
| first_partial_plan | Yes | |
| part_prune_index | Yes | |
| MergeAppend | ||
| plan | Yes | |
| apprelids | Yes | |
| mergeplans | Indirect | Jumbled via setrefs.c treewalk |
| numCols | Yes | |
| sortColIdx | Yes | |
| sortOperators | Yes | |
| collations | Yes | |
| nullsFirst | Yes | |
| part_prune_index | Yes | |
| RecursiveUnion | ||
| plan | Yes | |
| wtParam | Yes | |
| numCols | Yes | |
| dupColIdx | Yes | |
| dupOperators | Yes | |
| dupCollations | Yes | |
| numGroups | No | Costing/selectivity information should be ignored |
| BitmapAnd | ||
| plan | Yes | |
| bitmapplans | Indirect | Jumbled via setrefs.c treewalk |
| BitmapOr | ||
| plan | Yes | |
| isshared | Yes | |
| bitmapplans | Indirect | Jumbled via setrefs.c treewalk |
| Scan (abstract) | ||
| plan | Yes | |
| scanrelid | Yes | |
| SeqScan | ||
| scan | Yes | |
| SampleScan | ||
| scan | Yes | |
| tablesample | Yes | |
| IndexScan | ||
| scan | Yes | |
| indexid | Yes | |
| indexqual | Yes | |
| indexqualorig | Yes | |
| indexorderby | Yes | |
| indexorderbyorig | Yes | |
| indexorderbyops | Yes | |
| indexorderdir | Yes | |
| IndexOnlyScan | ||
| scan | Yes | |
| indexid | Yes | |
| indexqual | Yes | |
| recheckqual | Yes | |
| indexorderby | Yes | |
| indextlist | Yes | |
| indexorderdir | Yes | |
| BitmapIndexScan | ||
| scan | Yes | |
| indexid | Yes | |
| isshared | Yes | |
| indexqual | Yes | |
| indexqualorig | Yes | |
| BitmapHeapScan | ||
| scan | Yes | |
| bitmapqualorig | Yes | |
| TidScan | ||
| scan | Yes | |
| tidquals | Yes | |
| TidRangeScan | ||
| scan | Yes | |
| tidrangequals | Yes | |
| SubqueryScan | ||
| scan | Yes | |
| subplan | Indirect | Jumbled via setrefs.c treewalk |
| scanstatus | Yes | |
| FunctionScan | ||
| scan | Yes | |
| functions | Yes | |
| funcordinality | Yes | |
| ValuesScan | ||
| scan | Yes | |
| values_lists | Yes | Fixed values get ignored since they are A_Const |
| TableFuncScan | ||
| scan | Yes | |
| tablefunc | Yes | |
| CteScan | ||
| scan | Yes | |
| ctePlanId | Yes | |
| cteParam | Yes | |
| NamedTuplestoreScan | ||
| scan | Yes | |
| enrname | Yes | |
| WorkTableScan | ||
| scan | Yes | |
| wtParam | Yes | |
| ForeignScan | ||
| scan | Yes | |
| operation | Yes | |
| resultRelation | Yes | |
| checkAsUser | Yes | |
| fs_server | Yes | |
| fdw_exprs | Yes | |
| fdw_private | No | Private data for the FDW |
| fdw_scan_tlist | Yes | |
| fdw_recheck_quals | Yes | |
| fs_relids | Yes | |
| fs_base_relids | Yes | |
| fsSystemCol | Yes | |
| CustomScan | ||
| scan | Yes | |
| flags | Yes | |
| custom_plans | Yes | |
| custom_exprs | Yes | |
| custom_private | No | Private data for the Custom Scan Provider |
| custom_scan_tlist | Yes | |
| custom_relids | Yes | |
| methods | No | Pointer to a static struct that can't be jumbled |
| Join (abstract) | ||
| plan | Yes | |
| jointype | Yes | |
| inner_unique | Yes | |
| joinqual | Yes | |
| NestLoop | ||
| join | Yes | |
| nestParams | Yes | |
| MergeJoin | ||
| join | Yes | |
| skip_mark_restore | Yes | |
| mergeclauses | Yes | |
| mergeFamilies | Yes | |
| mergeCollations | Yes | |
| mergeReversals | Yes | |
| mergeNullsFirst | Yes | |
| HashJoin | ||
| join | Yes | |
| hashclauses | Yes | |
| hashoperators | Yes | |
| hashcollations | Yes | |
| hashkeys | Yes | |
| Material | ||
| plan | Yes | |
| Memoize | ||
| plan | Yes | |
| numKeys | Yes | |
| hashOperators | Yes | |
| collations | Yes | |
| param_exprs | Yes | |
| singlerow | Yes | |
| binary_mode | Yes | |
| est_entries | No | Costing/selectivity information should be ignored |
| keyparamids | Yes | |
| Sort | ||
| plan | Yes | |
| numCols | Yes | |
| sortColIdx | Yes | |
| sortOperators | Yes | |
| collations | Yes | |
| nullsFirst | Yes | |
| IncrementalSort | ||
| sort | Yes | |
| nPresortedCols | Yes | |
| Group | ||
| plan | Yes | |
| numCols | Yes | |
| grpColIdx | Yes | |
| grpOperators | Yes | |
| grpCollations | Yes | |
| Agg | ||
| plan | Yes | |
| aggstrategy | Yes | |
| aggsplit | Yes | |
| numCols | Yes | |
| grpColIdx | Yes | |
| grpOperators | Yes | |
| grpCollations | Yes | |
| numGroups | No | Costing/selectivity information should be ignored |
| transitionSpace | Yes | |
| aggParams | Yes | |
| groupingSets | Yes | |
| chain | Yes | |
| WindowAgg | ||
| plan | Yes | |
| winref | Yes | |
| partNumCols | Yes | |
| partColIdx | Yes | |
| partOperators | Yes | |
| partCollations | Yes | |
| ordNumCols | Yes | |
| ordColIdx | Yes | |
| ordOperators | Yes | |
| ordCollations | Yes | |
| frameOptions | Yes | |
| startOffset | Yes | |
| endOffset | Yes | |
| runCondition | Yes | |
| runConditionOrig | Yes | |
| startInRangeFunc | Yes | |
| endInRangeFunc | Yes | |
| inRangeColl | Yes | |
| inRangeAsc | Yes | |
| inRangeNullsFirst | Yes | |
| topWindow | Yes | |
| Unique | ||
| plan | Yes | |
| numCols | Yes | |
| uniqColIdx | Yes | |
| uniqOperators | Yes | |
| uniqCollations | Yes | |
| Gather | ||
| plan | Yes | |
| num_workers | Yes | |
| rescan_param | Yes | |
| single_copy | Yes | |
| invisible | Yes | |
| initParam | Yes | |
| GatherMerge | ||
| plan | Yes | |
| num_workers | Yes | |
| rescan_param | Yes | |
| numCols | Yes | |
| sortColIdx | Yes | |
| sortOperators | Yes | |
| collations | Yes | |
| nullsFirst | Yes | |
| initParam | Yes | |
| Hash | ||
| plan | Yes | |
| hashkeys | Yes | |
| skewTable | Yes | |
| skewColumn | Yes | |
| skewInherit | Yes | |
| rows_total | No | Costing/selectivity information should be ignored |
| SetOp | ||
| plan | Yes | |
| cmd | Yes | |
| strategy | Yes | |
| numCols | Yes | |
| cmpColIdx | Yes | |
| cmpOperators | Yes | |
| cmpCollations | Yes | |
| cmpNullsFirst | Yes | |
| numGroups | No | Costing/selectivity information should be ignored |
| LockRows | ||
| plan | Yes | |
| rowMarks | Yes | PlanRowMark nodes are ignored for jumbling |
| epqParam | Yes | |
| Limit | ||
| plan | Yes | |
| limitOffset | Yes | Fixed OFFSET values get ignored since they are A_Const |
| limitCount | Yes | Fixed LIMIT values get ignored since they are A_Const |
| limitOption | Yes | |
| uniqNumCols | Yes | |
| uniqColIdx | Yes | |
| uniqOperators | Yes | |
| uniqCollations | Yes |
