Re: pgsql: docs: fist draft version of the PG 12 release notes

Hi Bruce,

On 2019/05/07 8:02, Bruce Momjian wrote:
> docs: fist draft version of the PG 12 release notes
>
> Still needs text markup, links, word wrap, and indenting.

Thanks for crafting these up, as always.

About the partitioning performance improvement item:

+<listitem>
+<!--
+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2018-11-07 [c6e4133fa] Postpone calculating total_table_pages until after
pruni
+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2018-11-15 [34c9e455d] Improve performance of partition pruning remapping
a lit
+Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
+2018-11-16 [3f2393ede] Redesign initialization of partition routing
structures
+Author: Robert Haas <rhaas(at)postgresql(dot)org>
+2019-02-21 [9eefba181] Delay lock acquisition for partitions until we
route a t
+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2019-03-30 [428b260f8] Speed up planning when partitions can be pruned at
plan
+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2019-04-05 [959d00e9d] Use Append rather than MergeAppend for scanning
ordered
+-->
+
+<para>
+Improve performance of pruning many partitions (Amit Langote, David
Rowley, Tom Lane, Álvaro Herrera)
+</para>
+
+<para>
+Now thousands of partitions can be pruned efficiently.
+</para>
+</listitem>

I think there may be two (or more) distinct improvements here.

* Performance of "pruning" itself which was bottle-necked in the planner
is improved mostly due to:

+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2019-03-30 [428b260f8] Speed up planning when partitions can be pruned at
plan

* Also improved is the performance of inserting small number of rows into
partitioned tables which was bottle-necked in the executor because tuple
routing would do some redundant processing per statement. The
improvements are due to:

+Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
+2018-11-16 [3f2393ede] Redesign initialization of partition routing
structures
+Author: Robert Haas <rhaas(at)postgresql(dot)org>
+2019-02-21 [9eefba181] Delay lock acquisition for partitions until we
route a t

* AFAICT, the following two commits don't seem to have much to do with
improving the performance of pruning, although they are good improvements
in their own right.

+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2019-04-05 [959d00e9d] Use Append rather than MergeAppend for scanning
ordered

My summary of this optimization is that with it we can avoid paying the
cost of merging the ordered partition outputs if partitions are determined
to be ordered among themselves (for example, range partitions).

+Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
+2018-11-07 [c6e4133fa] Postpone calculating total_table_pages until after
pruni

My summary of this commit is that planner now correctly considers the
effect of partition pruning on cost calculations, whereas previously it
might end up making poor plan choices because it didn't subtract the pages
of pruned partitions from the total_table_pages global counter.

Thanks,
Amit