digoal
2024-03-26
PostgreSQL , PolarDB , DuckDB , Merge Append , sort , union
union 有去重的需要, 当多个子查询union时, 原来优化器把union放在所有结果中执行, 现在可以让子查询按union字段有序返回, 然后通过merge append在merge过程中去重, 有效提升了性能.
Here we adjust the union planner so that it can request that each subquery produce results in target list order so that these can be Merge Appended together and made unique with a Unique node.
Allow planner to use Merge Append to efficiently implement UNION
author David Rowley <drowley@postgresql.org>
Mon, 25 Mar 2024 01:31:14 +0000 (14:31 +1300)
committer David Rowley <drowley@postgresql.org>
Mon, 25 Mar 2024 01:31:14 +0000 (14:31 +1300)
commit 66c0185a3d14bbbf51d0fc9d267093ffec735231
tree ed16cb0999652ad23efef6b5e025554f4136020c tree
parent 47f99a407de224df6f9c43697d0a9c0a5598b250 commit | diff
Allow planner to use Merge Append to efficiently implement UNION
Until now, UNION queries have often been suboptimal as the planner has
only ever considered using an Append node and making the results unique
by either using a Hash Aggregate, or by Sorting the entire Append result
and running it through the Unique operator. Both of these methods
always require reading all rows from the union subqueries.
Here we adjust the union planner so that it can request that each subquery
produce results in target list order so that these can be Merge Appended
together and made unique with a Unique node. This can improve performance
significantly as the union child can make use of the likes of btree
indexes and/or Merge Joins to provide the top-level UNION with presorted
input. This is especially good if the top-level UNION contains a LIMIT
node that limits the output rows to a small subset of the unioned rows as
cheap startup plans can be used.
Author: David Rowley
Reviewed-by: Richard Guo, Andy Fan
Discussion: https://postgr.es/m/CAApHDvpb_63XQodmxKUF8vb9M7CxyUyT4sWvEgqeQU-GB7QFoQ@mail.gmail.com
