On Sat, Dec 25, 2021 at 9:06 AM Amit Langote <amitlangot...@gmail.com> wrote: > > Executing generic plans involving partitions is known to become slower > as partition count grows due to a number of bottlenecks, with > AcquireExecutorLocks() showing at the top in profiles. > > Previous attempt at solving that problem was by David Rowley [1], > where he proposed delaying locking of *all* partitions appearing under > an Append/MergeAppend until "initial" pruning is done during the > executor initialization phase. A problem with that approach that he > has described in [2] is that leaving partitions unlocked can lead to > race conditions where the Plan node belonging to a partition can be > invalidated when a concurrent session successfully alters the > partition between AcquireExecutorLocks() saying the plan is okay to > execute and then actually executing it. > > However, using an idea that Robert suggested to me off-list a little > while back, it seems possible to determine the set of partitions that > we can safely skip locking. The idea is to look at the "initial" or > "pre-execution" pruning instructions contained in a given Append or > MergeAppend node when AcquireExecutorLocks() is collecting the > relations to lock and consider relations from only those sub-nodes > that survive performing those instructions. I've attempted > implementing that idea in the attached patch. >
In which cases, we will have "pre-execution" pruning instructions that can be used to skip locking partitions? Can you please give a few examples where this approach will be useful? The benchmark is showing good results, indeed. -- Best Wishes, Ashutosh Bapat