Hi David,
On 2019/05/10 14:51, David Rowley wrote:
> On Fri, 10 May 2019 at 16:57, Justin Pryzby <pryzby@telsasoft.com> wrote:
>>> 959d00e Use Append rather than MergeAppend for scanning ordered partitions.
>
> I also think this is worth a mention. The speedup can be quite large
> when the query involves a LIMIT clause, and I think it will apply
> fairly often. Most of the times I've seen partitioned table the wild
> they were RANGE partitioned by a timestamp, or at least they were
> inheritance based tables partitioned by timestamp that could one day
> be changed to a RANGE partitioned table.
>
> I'd say something like:
>
> * Allow the optimizer to exploit the ordering of RANGE and LIST
> partitioned tables when generating paths for partitioned tables.
>
> This saves the optimizer from using MergeAppend node to scan a
> partitioned table in order when an Append node will do.
FWIW, I've asked [1] Bruce to mention this commit in its own release note
item. Currently, it's buried under pruning performance improvement item,
like this.
<listitem>
<!--
Author: Tom Lane <tgl@sss.pgh.pa.us>
2018-11-07 [c6e4133fa] Postpone calculating total_table_pages until after
pruni
Author: Tom Lane <tgl@sss.pgh.pa.us>
2018-11-15 [34c9e455d] Improve performance of partition pruning remapping
a lit
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
2018-11-16 [3f2393ede] Redesign initialization of partition routing structures
Author: Robert Haas <rhaas@postgresql.org>
2019-02-21 [9eefba181] Delay lock acquisition for partitions until we
route a t
Author: Tom Lane <tgl@sss.pgh.pa.us>
2019-03-30 [428b260f8] Speed up planning when partitions can be pruned at
plan
Author: Tom Lane <tgl@sss.pgh.pa.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>
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/3f0333be-fd32-55f2-9817-5853a6bbd233%40lab.ntt.co.jp
