Over on [1], Benjamin highlighted that we don't do ordered partition
scans in some cases where we could.
Basically, what was added in 959d00e9d only works when at least one
child path has pathkeys that suit the required query pathkeys. If the
partitions or partitioned table does not have an index matching the
partitioning columns or some subset thereof, then we'll not add an
ordered Append path.
I've attached a patch. This is what it does:
create table lp (a int) partition by list(a);
create table lp1 partition of lp for values in(1);
create table lp2 partition of lp for values in(2);
explain (costs off) select * from lp order by a;
master;
QUERY PLAN
----------------------------------
Sort
Sort Key: lp.a
-> Append
-> Seq Scan on lp1 lp_1
-> Seq Scan on lp2 lp_2
(5 rows)
patched:
QUERY PLAN
----------------------------------
Append
-> Sort
Sort Key: lp_1.a
-> Seq Scan on lp1 lp_1
-> Sort
Sort Key: lp_2.a
-> Seq Scan on lp2 lp_2
(7 rows)
There's still something in there that I'm not happy with which relates
to the tests I added in inherit.sql. Anyone looking at the new tests
might expect that the following query should work too:
explain (costs off) select * from range_parted order by a,b,c;
but it *appears* not to. We do build an AppendPath for that, it's
just that the AppendPath added by the following code seems to win over
it:
/*
* If we found unparameterized paths for all children, build an unordered,
* unparameterized Append path for the rel. (Note: this is correct even
* if we have zero or one live subpath due to constraint exclusion.)
*/
if (subpaths_valid)
add_path(rel, (Path *) create_append_path(root, rel, subpaths, NIL,
NIL, NULL, 0, false,
-1));
I still need to look to see if there's some small amount of data that
can be loaded into the table to help coax the planner into producing
the ordered scan for this one. It works fine as-is for ORDER BY a,b
and ORDER BY a; so I've put tests in for that.
David
[1]
https://postgr.es/m/CABTcpyuXXY1625-Mns=mpfcvsf4aougirvylpigqq0dot0p...@mail.gmail.com
diff --git a/src/backend/optimizer/path/allpaths.c
b/src/backend/optimizer/path/allpaths.c
index 4a330192f2..9151d53b62 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1282,6 +1282,43 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo
*rel,
/* If appropriate, consider parallel append */
pa_subpaths_valid = enable_parallel_append && rel->consider_parallel;
+ /*
+ * When the partitioned table is partitioned in a way that naturally
+ * guarantees some ordering, we'll add the pathkeys for that order to
+ * all_child_pathkeys. In generate_orderedappend_paths this may allow
+ * sorts to be pushed below an Append in more cases.
+ */
+ if (rel->part_scheme != NULL && IS_SIMPLE_REL(rel) &&
+ partitions_are_ordered(rel->boundinfo, rel->live_parts))
+ {
+ List *partition_pathkeys;
+ bool partial;
+
+ partition_pathkeys = build_partition_pathkeys(root, rel,
+
ForwardScanDirection,
+
&partial);
+
+ partition_pathkeys = truncate_useless_pathkeys(root, rel,
+
partition_pathkeys);
+
+ /* we need not pay attention to partial matches */
+ if (partition_pathkeys != NULL)
+ all_child_pathkeys = lappend(all_child_pathkeys,
+
partition_pathkeys);
+
+ /* as above but for DESC ordering */
+ partition_pathkeys = build_partition_pathkeys(root, rel,
+
BackwardScanDirection,
+
&partial);
+
+ partition_pathkeys = truncate_useless_pathkeys(root, rel,
+
partition_pathkeys);
+
+ if (partition_pathkeys != NULL)
+ all_child_pathkeys = lappend(all_child_pathkeys,
+
partition_pathkeys);
+ }
+
/*
* For every non-dummy child, remember the cheapest path. Also,
identify
* all pathkeys (orderings) and parameterizations (required_outer sets)
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index 2d49e765de..c75d0ab465 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2423,7 +2423,7 @@ drop table bool_rp;
create table range_parted (a int, b int, c int) partition by range(a, b);
create table range_parted1 partition of range_parted for values from (0,0) to
(10,10);
create table range_parted2 partition of range_parted for values from (10,10)
to (20,20);
-create index on range_parted (a,b,c);
+create index range_parted_a_b_c_idx on range_parted (a,b,c);
explain (costs off) select * from range_parted order by a,b,c;
QUERY PLAN
-------------------------------------------------------------------------------------
@@ -2440,6 +2440,58 @@ explain (costs off) select * from range_parted order by
a desc,b desc,c desc;
-> Index Only Scan Backward using range_parted1_a_b_c_idx on range_parted1
range_parted_1
(3 rows)
+drop index range_parted_a_b_c_idx;
+-- Ensure we make use of an Append scan even when no index exists to provide
+-- presorted input. We expect we can push a Sort below the Append.
+explain (costs off) select * from range_parted order by a;
+ QUERY PLAN
+------------------------------------------------------
+ Append
+ -> Sort
+ Sort Key: range_parted_1.a
+ -> Seq Scan on range_parted1 range_parted_1
+ -> Sort
+ Sort Key: range_parted_2.a
+ -> Seq Scan on range_parted2 range_parted_2
+(7 rows)
+
+explain (costs off) select * from range_parted order by a desc;
+ QUERY PLAN
+------------------------------------------------------
+ Append
+ -> Sort
+ Sort Key: range_parted_2.a DESC
+ -> Seq Scan on range_parted2 range_parted_2
+ -> Sort
+ Sort Key: range_parted_1.a DESC
+ -> Seq Scan on range_parted1 range_parted_1
+(7 rows)
+
+-- As above but with a more strict ordering requirement
+explain (costs off) select * from range_parted order by a,b;
+ QUERY PLAN
+------------------------------------------------------
+ Append
+ -> Sort
+ Sort Key: range_parted_1.a, range_parted_1.b
+ -> Seq Scan on range_parted1 range_parted_1
+ -> Sort
+ Sort Key: range_parted_2.a, range_parted_2.b
+ -> Seq Scan on range_parted2 range_parted_2
+(7 rows)
+
+explain (costs off) select * from range_parted order by a desc,b desc;
+ QUERY PLAN
+----------------------------------------------------------------
+ Append
+ -> Sort
+ Sort Key: range_parted_2.a DESC, range_parted_2.b DESC
+ -> Seq Scan on range_parted2 range_parted_2
+ -> Sort
+ Sort Key: range_parted_1.a DESC, range_parted_1.b DESC
+ -> Seq Scan on range_parted1 range_parted_1
+(7 rows)
+
drop table range_parted;
-- Check that we allow access to a child table's statistics when the user
-- has permissions only for the parent table.
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 195aedb5ff..c2d26dc860 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -866,11 +866,22 @@ drop table bool_rp;
create table range_parted (a int, b int, c int) partition by range(a, b);
create table range_parted1 partition of range_parted for values from (0,0) to
(10,10);
create table range_parted2 partition of range_parted for values from (10,10)
to (20,20);
-create index on range_parted (a,b,c);
+create index range_parted_a_b_c_idx on range_parted (a,b,c);
explain (costs off) select * from range_parted order by a,b,c;
explain (costs off) select * from range_parted order by a desc,b desc,c desc;
+drop index range_parted_a_b_c_idx;
+
+-- Ensure we make use of an Append scan even when no index exists to provide
+-- presorted input. We expect we can push a Sort below the Append.
+explain (costs off) select * from range_parted order by a;
+explain (costs off) select * from range_parted order by a desc;
+
+-- As above but with a more strict ordering requirement
+explain (costs off) select * from range_parted order by a,b;
+explain (costs off) select * from range_parted order by a desc,b desc;
+
drop table range_parted;
-- Check that we allow access to a child table's statistics when the user
