Horton

Re: generic plans and "initial" pruning

Amit Langote <amitlangote09@gmail.com>

View thread View on postgr.es
From: Amit Langote <amitlangote09@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tender Wang <tndrwang@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, Tomas Vondra <tomas@vondra.me>, Robert Haas <robertmhaas@gmail.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Andres Freund <andres@anarazel.de>, Daniel Gustafsson <daniel@yesql.se>, David Rowley <dgrowleyml@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Thom Brown <thom@linux.com>
Date: 2025-07-22T06:43:26Z
Lists: pgsql-hackers 
On Thu, Jul 17, 2025 at 9:11 PM Amit Langote <amitlangote09@gmail.com> wrote:
> The refinements I described in my email above might help mitigate some
> of those executor-related issues. However, I'm starting to wonder if
> it's worth reconsidering our decision to handle pruning, locking, and
> validation entirely at executor startup, which was the approach taken
> in the reverted patch.
>
> The alternative approach, doing initial pruning and locking within
> plancache.c itself (which I floated a while ago), might be worth
> revisiting. It avoids the complications we've discussed around the
> executor API and preserves the clear separation of concerns that
> plancache.c provides, though it does introduce some new layering
> concerns, which I describe further below.
>
> To support this, we'd need a mechanism to pass pruning results to the
> executor alongside each PlannedStmt. For each PartitionPruneInfo in
> the plan, that would include the corresponding PartitionPruneState and
> the bitmapset of surviving relids determined by initial pruning. Given
> that a CachedPlan can contain multiple PlannedStmts, this would
> effectively be a list of pruning results, one per statement. One
> reasonable way to handle that might be to define a parallel data
> structure, separate from PlannedStmt, constructed by plancache.c and
> carried via QueryDesc. The memory and lifetime management would mirror
> how ParamListInfo is handled today, leaving the executor API unchanged
> and avoiding intrusive changes to PlannedStmt.
>
> However, one potentially problematic aspect of this design is managing
> the lifecycle of the relations referenced by PartitionPruneState.
> Currently, partitioned table relations are opened by the executor
> after entering ExecutorStart() and closed automatically by
> ExecEndPlan(), allowing cleanup of pruning states implicitly. If we
> perform initial pruning earlier, we'd need to keep these relations
> open longer, necessitating explicit cleanup calls (e.g., a new
> FinishPartitionPruneState()) invoked by the caller of the executor,
> such as from ExecutorEnd() or even higher-level callers. This
> introduces some questionable layering by shifting responsibility for
> relation management tasks, which ideally belong within the executor,
> into its callers.
>
> My sense is that the complexity involved in carrying pruning results
> via this parallel data structure was one of the concerns Tom raised
> previously, alongside the significant pruning code refactoring that
> the earlier patch required. The latter, at least, should no longer be
> necessary given recent code improvements.

One point I forgot to mention about this approach is that we'd also
need to ensure permissions on parent relations are checked before
performing initial pruning in plancache.c, since pruning may involve
evaluating user-provided expressions. So in effect, we'd need to
invoke not just ExecDoInitialPruning(), but also
ExecCheckPermissions(), or some variant of it, prior to executor
startup. While manageable, it does add slightly to the complexity.

-- 
Thanks, Amit Langote

Commits

  1. Stamp 19beta1.

  2. Revert "Don't lock partitions pruned by initial pruning"

  3. Ensure first ModifyTable rel initialized if all are pruned

  4. Fix bug in cbc127917 to handle nested Append correctly

  5. Remove unstable test suite added by 525392d57

  6. Don't lock partitions pruned by initial pruning

  7. Fix an oversight in cbc127917 to handle MERGE correctly

  8. Track unpruned relids to avoid processing pruned relations

  9. Perform runtime initial pruning outside ExecInitNode()

  10. Move PartitionPruneInfo out of plan nodes into PlannedStmt

  11. Fix setrefs.c's failure to do expression processing on prune steps.

  12. Remove obsolete executor cleanup code

  13. Revert "Move PartitionPruneInfo out of plan nodes into PlannedStmt"

  14. Move PartitioPruneInfo out of plan nodes into PlannedStmt

  15. Refactor and cleanup runtime partition prune code a little

  16. Remove some unnecessary fields from Plan trees.

  17. Remove more redundant relation locking during executor startup.

  18. Shut down Gather's children before shutting down Gather itself.