Re: generic plans and "initial" pruning - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: generic plans and "initial" pruning
Date
Msg-id CAExHW5vaO3n8X7-0RytZqxZjF_99cLmfw-fFco_RUeRFDyXwCQ@mail.gmail.com
Whole thread Raw
In response to generic plans and "initial" pruning  (Amit Langote <amitlangote09@gmail.com>)
Responses Re: generic plans and "initial" pruning
List pgsql-hackers
On Sat, Dec 25, 2021 at 9:06 AM Amit Langote <amitlangote09@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



pgsql-hackers by date:

Previous
From: "Godfrin, Philippe E"
Date:
Subject: toast tables and toast indexes
Next
From: Andrew Dunstan
Date:
Subject: Extend compatibility of PostgreSQL::Test::Cluster