Re: PG 14 release notes, first draft - Mailing list pgsql-hackers

On Wed, May 12, 2021 at 6:23 AM Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, May 11, 2021 at 11:57:10AM +0900, Amit Langote wrote:
> > On Mon, May 10, 2021 at 11:40 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > > Same as the last couple years, I checked for missing items in the release
> > > notes, running something like this.
> > >
> > > git log --cherry-pick --oneline origin/REL_13_STABLE...origin/master
> > >
> > > Should any of these be included?
> > >
> > > 86dc90056d Rework planning and execution of UPDATE and DELETE.
> > > a1115fa078 Postpone some more stuff out of ExecInitModifyTable.
> > > c5b7ba4e67 Postpone some stuff out of ExecInitModifyTable.
> >
> > I was just about to ask Bruce what he thinks about these.
> >
> > To clarify, the first one is a big refactoring commit that allowed us
> > to get rid of inheritance_planner(), a fairly inefficient way of
> > planning updates/deletes on partitioned tables, especially when many
> > partitions remain after pruning (or when pruning cannot be used).  One
> > may see the performance of update/deletes, especially on partitioned
> > tables, to be generally improved as a result of this commit, but maybe
> > not as significantly as to be mentioned in E.1.3.1.1. Partitioning or
> > even E.1.3.1.4. General Performance.  However, one user-visible
> > feature that came out of this work is that updates/deletes can now use
> > run-time pruning whereas they couldn't before.  Maybe that ought to be
> > mentioned.  (This reminds me to send a patch to remove the note from
> > 5.11.4. Partition Pruning that says that runtime pruning cannot be
> > used for update/delete).
> >
> > The other two commits can lead to improved performance of
> > update/deletes when there are many unpruned partitions in the plan,
> > but runtime pruning (a new feature as mentioned above) leads to only
> > one or few partitions to actually be updated/deleted from.  I admit
> > though that the cases for which performance has been improved still
> > under-perform the cases that already performed better starting in v12,
> > that is, the cases where the planner itself is able to trim down the
> > plan to contain one or few partitions, so maybe nothing very big to
> > see here just yet.  You may want to take a look at the benchmark
> > results I had posted here:
> > https://www.postgresql.org/message-id/CA%2BHiwqEcawatEaUh1uTbZMEZTJeLzbroRTz9_X9Z5CFjTWJkhw%40mail.gmail.com
>
> Seems we might want to have a general release note item that mentions
> improved update/delete performance for partitioned tables, yes?

Yes, that would not be entirely wrong.

The cases that benefit more from these commits are those where the
planner is not able to prune many (or any) partitions, such as, for
example, if a generic plan is chosen for prepared update/delete
queries.  The cases where the planner *is* able to prune most
partitions should run faster with v14 but only marginally; that case
was already extensively improved by the work done in v12 release.

>  I think
> the run-time pruning and single-parition pullup are significant.

Assuming, by "single-partition pullup", you mean run-time pruning now
allows the executor to touch only one affected partition out of the
many contained in a update/delete plan, then yes, that's new in v14.

--
Amit Langote
EDB: http://www.enterprisedb.com