On Tue, May 11, 2021 at 11:57:10AM +0900, Amit Langote wrote:
> On Mon, May 10, 2021 at 11:40 PM Justin Pryzby <[email protected]> wrote:
> > 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
OK, I added this entry:
<listitem>
<!--
Author: Tom Lane <[email protected]>
2021-03-31 [86dc90056] Rework planning and execution of UPDATE and
DELETE.
Author: Tom Lane <[email protected]>
2021-04-06 [a1115fa07] Postpone some more stuff out of
ExecInitModifyTable.
Author: Tom Lane <[email protected]>
2021-04-06 [c5b7ba4e6] Postpone some stuff out of ExecInitModifyTable.
-->
<para>
Improve the performance of updates/deletes on partitioned tables
when only a few partitions are affected (Amit Langote, Tom Lane)
</para>
<para>
This also allows run-time pruning of updates/deletes on partitioned
tables.
</para>
</listitem>
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
