This is an automated email from the ASF dual-hosted git repository.
chenjinbao1989 pushed a commit to branch cbdb-postgres-merge
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/cbdb-postgres-merge by this
push:
new 54fd0358db6 Fix window answer file
54fd0358db6 is described below
commit 54fd0358db6b742147bdf19501741210cf6dc2cf
Author: Jinbao Chen <[email protected]>
AuthorDate: Thu Nov 27 22:05:09 2025 +0800
Fix window answer file
---
src/backend/nodes/outfast.c | 3 +
src/backend/nodes/outfuncs.c | 4 +-
src/backend/nodes/readfast.c | 3 +
src/backend/nodes/readfuncs.c | 20 +
src/backend/parser/gram.y | 7 +-
src/include/parser/kwlist.h | 8 +-
src/test/regress/expected/jsonb.out | 51 ++-
src/test/regress/expected/window.out | 859 ++++++++++++++++++++---------------
src/test/regress/serial_schedule | 18 +-
src/test/regress/sql/json.sql | 3 -
src/test/regress/sql/window.sql | 4 +-
11 files changed, 558 insertions(+), 422 deletions(-)
diff --git a/src/backend/nodes/outfast.c b/src/backend/nodes/outfast.c
index c6e12f02d04..9b8558b105a 100644
--- a/src/backend/nodes/outfast.c
+++ b/src/backend/nodes/outfast.c
@@ -1967,6 +1967,9 @@ _outNode(StringInfo str, void *obj)
case T_PublicationTable:
_outPublicationTable(str, obj);
break;
+ case T_WindowDef:
+ _outWindowDef(str, obj);
+ break;
default:
elog(ERROR, "could not serialize unrecognized
node type: %d",
(int) nodeTag(obj));
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 09f0397a974..5c65f457937 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1071,6 +1071,7 @@ _outWindowAgg(StringInfo str, const WindowAgg *node)
WRITE_OID_FIELD(inRangeColl);
WRITE_BOOL_FIELD(inRangeAsc);
WRITE_BOOL_FIELD(inRangeNullsFirst);
+ WRITE_BOOL_FIELD(topWindow);
}
static void
@@ -4039,7 +4040,6 @@ _outSortBy(StringInfo str, const SortBy *node)
WRITE_LOCATION_FIELD(location);
}
-#ifndef COMPILING_BINARY_FUNCS
static void
_outWindowDef(StringInfo str, const WindowDef *node)
{
@@ -4055,6 +4055,8 @@ _outWindowDef(StringInfo str, const WindowDef *node)
WRITE_LOCATION_FIELD(location);
}
+#ifndef COMPILING_BINARY_FUNCS
+
static void
_outRangeSubselect(StringInfo str, const RangeSubselect *node)
{
diff --git a/src/backend/nodes/readfast.c b/src/backend/nodes/readfast.c
index d47894570d9..80c00523bf2 100644
--- a/src/backend/nodes/readfast.c
+++ b/src/backend/nodes/readfast.c
@@ -2973,6 +2973,9 @@ readNodeBinary(void)
case T_PublicationTable:
return_value = _readPublicationTable();
break;
+ case T_WindowDef:
+ return_value = _readWindowDef();
+ break;
default:
return_value = NULL; /* keep the compiler
silent */
elog(ERROR, "could not deserialize unrecognized
node type: %d",
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 080c599474a..f33fd633120 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -2535,6 +2535,7 @@ _readWindowAgg(void)
READ_OID_FIELD(inRangeColl);
READ_BOOL_FIELD(inRangeAsc);
READ_BOOL_FIELD(inRangeNullsFirst);
+ READ_BOOL_FIELD(topWindow);
READ_DONE();
}
@@ -2978,6 +2979,23 @@ _readPublicationTable(void)
READ_DONE();
}
+static WindowDef *
+_readWindowDef(void)
+{
+ READ_LOCALS(WindowDef);
+
+ READ_STRING_FIELD(name);
+ READ_STRING_FIELD(refname);
+ READ_NODE_FIELD(partitionClause);
+ READ_NODE_FIELD(orderClause);
+ READ_INT_FIELD(frameOptions);
+ READ_NODE_FIELD(startOffset);
+ READ_NODE_FIELD(endOffset);
+ READ_LOCATION_FIELD(location);
+
+ READ_DONE();
+}
+
#include "readfuncs_common.c"
#ifndef COMPILING_BINARY_FUNCS
/*
@@ -3505,6 +3523,8 @@ parseNodeString(void)
return_value = _readPublicationObjSpec();
else if (MATCHX("PUBLICATIONTABLE"))
return_value = _readPublicationTable();
+ else if (MATCHX("WINDOWDEF"))
+ return_value = _readWindowDef();
else
{
ereport(ERROR,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1ac7cbf362e..3f9fd0a44b6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -21158,6 +21158,7 @@ unreserved_keyword:
| FILTER
| FINALIZE
| FIRST_P
+ | FOLLOWING
| FORCE
| FORMAT
| FORWARD
@@ -21282,6 +21283,7 @@ unreserved_keyword:
| PERSISTENTLY
| PLANS
| POLICY
+ | PRECEDING
| PREPARE
| PREPARED
| PRESERVE
@@ -21392,6 +21394,7 @@ unreserved_keyword:
| TYPE_P
| TYPES_P
| UESCAPE
+ | UNBOUNDED
| UNCOMMITTED
| UNENCRYPTED
| UNKNOWN
@@ -21916,7 +21919,6 @@ reserved_keyword:
| EXCLUDE
| FALSE_P
| FETCH
- | FOLLOWING
| FOR
| FOREIGN
| FROM
@@ -21941,7 +21943,6 @@ reserved_keyword:
| ORDER
| PARTITION
| PLACING
- | PRECEDING
| PRIMARY
| REFERENCES
| RETURNING
@@ -21956,7 +21957,6 @@ reserved_keyword:
| TO
| TRAILING
| TRUE_P
- | UNBOUNDED
| UNION
| UNIQUE
| USER
@@ -22306,7 +22306,6 @@ bare_label_keyword:
| POLICY
| POSITION
| PRECEDING
- | PRECISION
| PREPARE
| PREPARED
| PRESERVE
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 43c5db05afc..24b6936bd46 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -197,7 +197,7 @@ PG_KEYWORD("filter", FILTER, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("finalize", FINALIZE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("first", FIRST_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("float", FLOAT_P, COL_NAME_KEYWORD, BARE_LABEL)
-PG_KEYWORD("following", FOLLOWING, RESERVED_KEYWORD, BARE_LABEL) /*
Unreserved in standard */
+PG_KEYWORD("following", FOLLOWING, UNRESERVED_KEYWORD, BARE_LABEL) /*
Unreserved in standard */
PG_KEYWORD("for", FOR, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("force", FORCE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("foreign", FOREIGN, RESERVED_KEYWORD, BARE_LABEL)
@@ -381,8 +381,8 @@ PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL)
-PG_KEYWORD("preceding", PRECEDING, RESERVED_KEYWORD, BARE_LABEL) /*
unreserved in standard */
-PG_KEYWORD("precision", PRECISION, COL_NAME_KEYWORD, BARE_LABEL) /*
GPDB */
+PG_KEYWORD("preceding", PRECEDING, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("precision", PRECISION, COL_NAME_KEYWORD, AS_LABEL)
PG_KEYWORD("prepare", PREPARE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("prepared", PREPARED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("preserve", PRESERVE, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -519,7 +519,7 @@ PG_KEYWORD("trusted", TRUSTED, UNRESERVED_KEYWORD,
BARE_LABEL)
PG_KEYWORD("type", TYPE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("types", TYPES_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("uescape", UESCAPE, UNRESERVED_KEYWORD, BARE_LABEL)
-PG_KEYWORD("unbounded", UNBOUNDED, RESERVED_KEYWORD, BARE_LABEL)
/* Unreserved in standard */
+PG_KEYWORD("unbounded", UNBOUNDED, UNRESERVED_KEYWORD, BARE_LABEL)
/* Unreserved in standard */
PG_KEYWORD("uncommitted", UNCOMMITTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("unencrypted", UNENCRYPTED, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("union", UNION, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/test/regress/expected/jsonb.out
b/src/test/regress/expected/jsonb.out
index 3f8afaf92a6..c0e0de5d726 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3050,16 +3050,17 @@ SELECT count(*) FROM testjsonb WHERE j ?&
ARRAY['public','disabled'];
EXPLAIN (COSTS OFF)
SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == null';
- QUERY PLAN
------------------------------------------------------------------------
- Aggregate
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
- -> Bitmap Heap Scan on testjsonb
- Recheck Cond: (j @@ '($."wait" == null)'::jsonpath)
- -> Bitmap Index Scan on jidx
- Index Cond: (j @@ '($."wait" == null)'::jsonpath)
+ -> Partial Aggregate
+ -> Bitmap Heap Scan on testjsonb
+ Recheck Cond: (j @@ '($."wait" == null)'::jsonpath)
+ -> Bitmap Index Scan on jidx
+ Index Cond: (j @@ '($."wait" == null)'::jsonpath)
Optimizer: Postgres query optimizer
-(7 rows)
+(8 rows)
SELECT count(*) FROM testjsonb WHERE j @@ '$.wait == null';
count
@@ -3165,16 +3166,17 @@ SELECT count(*) FROM testjsonb WHERE j @@
'exists($.public) && exists($.disabled
EXPLAIN (COSTS OFF)
SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)';
- QUERY PLAN
--------------------------------------------------------------------------
- Aggregate
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
- -> Bitmap Heap Scan on testjsonb
- Recheck Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
- -> Bitmap Index Scan on jidx
- Index Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
+ -> Partial Aggregate
+ -> Bitmap Heap Scan on testjsonb
+ Recheck Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
+ -> Bitmap Index Scan on jidx
+ Index Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
Optimizer: Postgres query optimizer
-(7 rows)
+(8 rows)
SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)';
count
@@ -3473,16 +3475,17 @@ SELECT count(*) FROM testjsonb WHERE j @@ 'exists($)';
EXPLAIN (COSTS OFF)
SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)';
- QUERY PLAN
--------------------------------------------------------------------------
- Aggregate
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
- -> Bitmap Heap Scan on testjsonb
- Recheck Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
- -> Bitmap Index Scan on jidx
- Index Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
+ -> Partial Aggregate
+ -> Bitmap Heap Scan on testjsonb
+ Recheck Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
+ -> Bitmap Index Scan on jidx
+ Index Cond: (j @? '$."wait"?(@ == null)'::jsonpath)
Optimizer: Postgres query optimizer
-(7 rows)
+(8 rows)
SELECT count(*) FROM testjsonb WHERE j @? '$.wait ? (@ == null)';
count
diff --git a/src/test/regress/expected/window.out
b/src/test/regress/expected/window.out
index 107c3dac934..31e3b5271b2 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -401,223 +401,211 @@ SELECT first_value(ten) OVER (PARTITION BY four ORDER
BY ten), ten, four FROM te
-- test split window func
explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (rank() OVER (?) < 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (row_number() OVER (?) < 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (dense_rank() OVER (?) < 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < '0.5'::double precision)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (percent_rank() OVER (?) < '0.5'::double precision)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
set optimizer_force_split_window_function to on;
-- worked
explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (rank() OVER (?) < 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (row_number() OVER (?) < 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (dense_rank() OVER (?) < 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 <= 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (rank() OVER (?) <= 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT row_number() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 <= 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (row_number() OVER (?) <= 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT dense_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 <= 3)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (dense_rank() OVER (?) <= 3)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
-- no worked
explain SELECT * FROM (SELECT cume_dist() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 <= 1;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 <= '1'::double precision)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (cume_dist() OVER (?) <= '1'::double precision)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT percent_rank() OVER (PARTITION BY four ORDER BY
ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 < 0.5;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.76 rows=3 width=16)
- -> Subquery Scan on t (cost=8.60..8.71 rows=1 width=16)
- Filter: (t.rank_1 < '0.5'::double precision)
- -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
- Partition By: tenk1.four
- Order By: tenk1.ten
- -> Sort (cost=8.60..8.61 rows=3 width=8)
- Sort Key: tenk1.four, tenk1.ten
- -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
- Hash Key: tenk1.four
- -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
- Index Cond: (unique2 < 10)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=8.60..8.80 rows=10 width=16)
+ -> WindowAgg (cost=8.60..8.67 rows=3 width=16)
+ Run Condition: (percent_rank() OVER (?) < '0.5'::double precision)
+ Partition By: tenk1.four
+ Order By: tenk1.ten
+ -> Sort (cost=8.60..8.61 rows=3 width=8)
+ Sort Key: tenk1.four, tenk1.ten
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.16..8.57 rows=3 width=8)
+ Hash Key: tenk1.four
+ -> Index Scan using tenk1_unique2 on tenk1
(cost=0.16..8.51 rows=3 width=8)
+ Index Cond: (unique2 < 10)
Optimizer: Postgres query optimizer
-(13 rows)
+(12 rows)
explain SELECT * FROM (SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS
rank_1, ten, four FROM tenk1 WHERE unique2 < 10) t WHERE rank_1 > 1;
QUERY PLAN
@@ -3812,13 +3800,17 @@ SELECT
cume_dist() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING) cd
FROM empsalary;
- QUERY PLAN
-----------------------------------------
- WindowAgg
- -> Sort
- Sort Key: depname, enroll_date
- -> Seq Scan on empsalary
-(4 rows)
+ QUERY PLAN
+----------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> WindowAgg
+ Partition By: depname
+ Order By: enroll_date
+ -> Sort
+ Sort Key: depname, enroll_date
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(8 rows)
-- Ensure WindowFuncs which cannot support their WindowClause's frameOptions
-- being changed are untouched
@@ -3832,18 +3824,26 @@ SELECT
count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
- WindowAgg
- Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)),
count(*) OVER (?), enroll_date
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)),
(count(*) OVER (?)), enroll_date
-> WindowAgg
- Output: depname, enroll_date, empno, row_number() OVER (?), rank()
OVER (?)
- -> Sort
- Output: depname, enroll_date, empno
- Sort Key: empsalary.depname, empsalary.enroll_date
- -> Seq Scan on pg_temp.empsalary
+ Output: empno, depname, (row_number() OVER (?)), (rank() OVER (?)),
count(*) OVER (?), enroll_date
+ Partition By: empsalary.depname
+ Order By: empsalary.enroll_date
+ -> WindowAgg
+ Output: depname, enroll_date, empno, row_number() OVER (?),
rank() OVER (?)
+ Partition By: empsalary.depname
+ Order By: empsalary.enroll_date
+ -> Sort
Output: depname, enroll_date, empno
-(9 rows)
+ Sort Key: empsalary.depname, empsalary.enroll_date
+ -> Seq Scan on pg_temp.empsalary
+ Output: depname, enroll_date, empno
+ Settings: enable_incremental_sort = 'on'
+ Optimizer: Postgres query optimizer
+(17 rows)
-- Ensure the above query gives us the expected results
SELECT
@@ -3956,14 +3956,18 @@ SELECT * FROM
row_number() OVER (ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (row_number() OVER (?) < 3)
- -> Sort
- Sort Key: empsalary.empno
- -> Seq Scan on empsalary
-(5 rows)
+ Order By: empsalary.empno
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.empno
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(9 rows)
-- The following 3 statements should result the same result.
SELECT * FROM
@@ -4007,14 +4011,18 @@ SELECT * FROM
rank() OVER (ORDER BY salary DESC) r
FROM empsalary) emp
WHERE r <= 3;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (rank() OVER (?) <= 3)
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(5 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(9 rows)
SELECT * FROM
(SELECT empno,
@@ -4037,16 +4045,20 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
Run Condition: (dense_rank() OVER (?) <= 1)
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(7 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(11 rows)
SELECT * FROM
(SELECT empno,
@@ -4067,14 +4079,18 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (count(*) OVER (?) <= 3)
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(5 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(9 rows)
SELECT * FROM
(SELECT empno,
@@ -4096,14 +4112,18 @@ SELECT * FROM
count(1) OVER (ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (count(1) OVER (?) <= 3)
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(5 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(9 rows)
SELECT * FROM
(SELECT empno,
@@ -4125,14 +4145,18 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND
UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c >= 3;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (count(*) OVER (?) >= 3)
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(5 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(9 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -4141,12 +4165,14 @@ SELECT * FROM
count(*) OVER () c
FROM empsalary) emp
WHERE 11 <= c;
- QUERY PLAN
---------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (11 <= count(*) OVER (?))
- -> Seq Scan on empsalary
-(3 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(5 rows)
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -4156,16 +4182,20 @@ SELECT * FROM
dense_rank() OVER (ORDER BY salary DESC) dr
FROM empsalary) emp
WHERE dr = 1;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.dr = 1)
-> WindowAgg
Run Condition: (dense_rank() OVER (?) <= 1)
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(7 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(11 rows)
-- Ensure we get a run condition when there's a PARTITION BY clause
EXPLAIN (COSTS OFF)
@@ -4175,14 +4205,19 @@ SELECT * FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------
- WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
- -> Sort
- Sort Key: empsalary.depname, empsalary.empno
- -> Seq Scan on empsalary
-(5 rows)
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.depname, empsalary.empno
+ -> WindowAgg
+ Run Condition: (row_number() OVER (?) < 3)
+ Partition By: empsalary.depname
+ Order By: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.depname, empsalary.empno
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- and ensure we get the correct results from the above plan
SELECT * FROM
@@ -4210,15 +4245,20 @@ SELECT empno, depname FROM
row_number() OVER (PARTITION BY depname ORDER BY empno) rn
FROM empsalary) emp
WHERE rn < 3;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Subquery Scan on emp
- -> WindowAgg
- Run Condition: (row_number() OVER (?) < 3)
- -> Sort
- Sort Key: empsalary.depname, empsalary.empno
- -> Seq Scan on empsalary
-(6 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.depname, empsalary.empno
+ -> WindowAgg
+ Run Condition: (row_number() OVER (?) < 3)
+ Partition By: empsalary.depname
+ Order By: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.depname, empsalary.empno
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(11 rows)
-- likewise with count(1) instead of row_number()
EXPLAIN (COSTS OFF)
@@ -4229,14 +4269,19 @@ SELECT * FROM
count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
-------------------------------------------------------------
- WindowAgg
- Run Condition: (count(1) OVER (?) <= 3)
- -> Sort
- Sort Key: empsalary.depname, empsalary.salary DESC
- -> Seq Scan on empsalary
-(5 rows)
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.depname, empsalary.salary
+ -> WindowAgg
+ Run Condition: (count(1) OVER (?) <= 3)
+ Partition By: empsalary.depname
+ Order By: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.depname, empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- and again, check the results are what we expect.
SELECT * FROM
@@ -4268,12 +4313,14 @@ SELECT * FROM
count(1) OVER () c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+------------------------------------------------
WindowAgg
Run Condition: (count(1) OVER (?) = 1)
- -> Seq Scan on empsalary
-(3 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(5 rows)
-- Try another case with a WindowFunc with a byref return type
SELECT * FROM
@@ -4296,23 +4343,35 @@ SELECT * FROM
ntile(2) OVER (PARTITION BY depname) nt -- w2
FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
Subquery Scan on e
- -> WindowAgg
- Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2))
- Run Condition: (count(1) OVER (?) <= 3)
- -> Sort
- Sort Key: (((empsalary.depname)::text || ''::text))
- -> WindowAgg
- Run Condition: ((row_number() OVER (?) <= 1) AND
(ntile(2) OVER (?) < 2))
- -> Sort
- Sort Key: empsalary.depname
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: (((empsalary.depname)::text || ''::text))
+ -> WindowAgg
+ Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER
(?)) < 2))
+ Run Condition: (count(1) OVER (?) <= 3)
+ Partition By: (((empsalary.depname)::text || ''::text))
+ -> Sort
+ Sort Key: (((empsalary.depname)::text || ''::text))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: (((empsalary.depname)::text || ''::text))
-> WindowAgg
+ Run Condition: ((row_number() OVER (?) <= 1)
AND (ntile(2) OVER (?) < 2))
+ Partition By: empsalary.depname
-> Sort
- Sort Key: ((''::text ||
(empsalary.depname)::text))
- -> Seq Scan on empsalary
-(14 rows)
+ Sort Key: empsalary.depname
+ -> Redistribute Motion 3:3 (slice3;
segments: 3)
+ Hash Key: empsalary.depname
+ -> WindowAgg
+ Partition By: ((''::text ||
(empsalary.depname)::text))
+ -> Sort
+ Sort Key: ((''::text
|| (empsalary.depname)::text))
+ -> Redistribute
Motion 3:3 (slice4; segments: 3)
+ Hash Key:
((''::text || (empsalary.depname)::text))
+ -> Seq Scan on
empsalary
+ Optimizer: Postgres query optimizer
+(26 rows)
-- Ensure we correctly filter out all of the run conditions from each window
SELECT * FROM
@@ -4341,15 +4400,19 @@ SELECT * FROM
count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND
UNBOUNDED FOLLOWING) c
FROM empsalary) emp
WHERE c <= 3;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c <= 3)
-> WindowAgg
- -> Sort
- Sort Key: empsalary.salary DESC
- -> Seq Scan on empsalary
-(6 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- Ensure we don't push down when the window function's monotonic properties
-- don't match that of the clauses.
@@ -4360,15 +4423,19 @@ SELECT * FROM
count(*) OVER (ORDER BY salary) c
FROM empsalary) emp
WHERE 3 <= c;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (3 <= emp.c)
-> WindowAgg
- -> Sort
- Sort Key: empsalary.salary
- -> Seq Scan on empsalary
-(6 rows)
+ Order By: empsalary.salary
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.salary
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- Ensure we don't use a run condition when there's a volatile function in the
-- WindowFunc
@@ -4379,15 +4446,19 @@ SELECT * FROM
count(random()) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
- -> Sort
- Sort Key: empsalary.empno DESC
- -> Seq Scan on empsalary
-(6 rows)
+ Order By: empsalary.empno
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.empno DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- Ensure we don't use a run condition when the WindowFunc arg contains a Var
EXPLAIN (COSTS OFF)
@@ -4397,15 +4468,19 @@ SELECT * FROM
count(empno) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
- -> Sort
- Sort Key: empsalary.empno DESC
- -> Seq Scan on empsalary
-(6 rows)
+ Order By: empsalary.empno
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.empno DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- As above but with ntile().
EXPLAIN (COSTS OFF)
@@ -4415,15 +4490,19 @@ SELECT * FROM
ntile(empno::int) OVER (ORDER BY empno DESC) nt
FROM empsalary) emp
WHERE nt = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.nt = 1)
-> WindowAgg
- -> Sort
- Sort Key: empsalary.empno DESC
- -> Seq Scan on empsalary
-(6 rows)
+ Order By: empsalary.empno
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.empno DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(10 rows)
-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
@@ -4433,17 +4512,21 @@ SELECT * FROM
count((SELECT 1)) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Subquery Scan on emp
Filter: (emp.c = 1)
-> WindowAgg
- InitPlan 1 (returns $0)
+ Order By: empsalary.empno
+ InitPlan 1 (returns $0) (slice2)
-> Result
- -> Sort
- Sort Key: empsalary.empno DESC
- -> Seq Scan on empsalary
-(8 rows)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: empsalary.empno
+ -> Sort
+ Sort Key: empsalary.empno DESC
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(12 rows)
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
@@ -4481,17 +4564,24 @@ SELECT empno,
min(salary) OVER (PARTITION BY depname order by enroll_date)
depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
-----------------------------------------------------
- WindowAgg
- -> Incremental Sort
- Sort Key: depname, empno
- Presorted Key: depname
- -> WindowAgg
- -> Sort
- Sort Key: depname, enroll_date
- -> Seq Scan on empsalary
-(8 rows)
+ QUERY PLAN
+----------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: depname, empno
+ -> WindowAgg
+ Partition By: depname
+ Order By: empno
+ -> Incremental Sort
+ Sort Key: depname, empno
+ Presorted Key: depname
+ -> WindowAgg
+ Partition By: depname
+ Order By: enroll_date
+ -> Sort
+ Sort Key: depname, enroll_date
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(15 rows)
-- As above, but with an adjusted ORDER BY to ensure the above plan didn't
-- perform only 2 sorts by accident.
@@ -4503,17 +4593,24 @@ SELECT empno,
min(salary) OVER (PARTITION BY depname order by enroll_date)
depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------
- WindowAgg
- -> Incremental Sort
- Sort Key: depname, enroll_date
- Presorted Key: depname
- -> WindowAgg
- -> Sort
- Sort Key: depname, empno
- -> Seq Scan on empsalary
-(8 rows)
+ QUERY PLAN
+-----------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: depname, enroll_date
+ -> WindowAgg
+ Partition By: depname
+ Order By: enroll_date
+ -> Incremental Sort
+ Sort Key: depname, enroll_date
+ Presorted Key: depname
+ -> WindowAgg
+ Partition By: depname
+ Order By: empno
+ -> Sort
+ Sort Key: depname, empno
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(15 rows)
SET enable_hashagg TO off;
-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the
@@ -4527,21 +4624,28 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date)
depminsalary
FROM empsalary
ORDER BY depname, enroll_date;
- QUERY PLAN
------------------------------------------------------------------------------------------------
- Unique
- -> Incremental Sort
- Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)),
(min(salary) OVER (?))
- Presorted Key: depname, enroll_date
- -> WindowAgg
- -> Incremental Sort
- Sort Key: depname, enroll_date
- Presorted Key: depname
- -> WindowAgg
- -> Sort
- Sort Key: depname, empno
- -> Seq Scan on empsalary
-(12 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: depname, enroll_date, empno, (sum(salary) OVER (?)),
(min(salary) OVER (?))
+ -> Unique
+ Group Key: depname, enroll_date, empno, (sum(salary) OVER (?)),
(min(salary) OVER (?))
+ -> Sort
+ Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)),
(min(salary) OVER (?))
+ -> WindowAgg
+ Partition By: depname
+ Order By: enroll_date
+ -> Incremental Sort
+ Sort Key: depname, enroll_date
+ Presorted Key: depname
+ -> WindowAgg
+ Partition By: depname
+ Order By: empno
+ -> Sort
+ Sort Key: depname, empno
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(19 rows)
-- As above but adjust the ORDER BY clause to help ensure the plan with the
-- minimum amount of sorting wasn't a fluke.
@@ -4554,21 +4658,28 @@ SELECT DISTINCT
min(salary) OVER (PARTITION BY depname order by enroll_date)
depminsalary
FROM empsalary
ORDER BY depname, empno;
- QUERY PLAN
------------------------------------------------------------------------------------------------
- Unique
- -> Incremental Sort
- Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)),
(min(salary) OVER (?))
- Presorted Key: depname, empno
- -> WindowAgg
- -> Incremental Sort
- Sort Key: depname, empno
- Presorted Key: depname
- -> WindowAgg
- -> Sort
- Sort Key: depname, enroll_date
- -> Seq Scan on empsalary
-(12 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: depname, empno, enroll_date, (sum(salary) OVER (?)),
(min(salary) OVER (?))
+ -> Unique
+ Group Key: depname, empno, enroll_date, (sum(salary) OVER (?)),
(min(salary) OVER (?))
+ -> Sort
+ Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)),
(min(salary) OVER (?))
+ -> WindowAgg
+ Partition By: depname
+ Order By: empno
+ -> Incremental Sort
+ Sort Key: depname, empno
+ Presorted Key: depname
+ -> WindowAgg
+ Partition By: depname
+ Order By: enroll_date
+ -> Sort
+ Sort Key: depname, enroll_date
+ -> Seq Scan on empsalary
+ Optimizer: Postgres query optimizer
+(19 rows)
RESET enable_hashagg;
-- Test Sort node reordering
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 4446c1b9ebd..818724423f8 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -160,15 +160,15 @@ test: tsearch
test: tsdicts
test: foreign_data
test: gp_foreign_data
-# test: window
-# test: xmlmap
-# test: functional_deps
-# test: advisory_lock
-# test: indirect_toast
-# test: equivclass
-# test: json
-# test: jsonb
-# test: json_encoding
+test: window
+test: xmlmap
+test: functional_deps
+test: advisory_lock
+test: indirect_toast
+test: equivclass
+test: json
+test: jsonb
+test: json_encoding
# test: jsonpath
# test: jsonpath_encoding
# test: jsonb_jsonpath
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index f5bc59f0bfc..b46d7f701dc 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -748,12 +748,9 @@ select json_object('{a,b,NULL,"d e f"}','{1,2,3,"a b c"}');
select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
-<<<<<<< HEAD
-=======
-- json_object_agg_unique requires unique keys
select json_object_agg_unique(mod(i,100), i) from generate_series(0, 199) i;
->>>>>>> REL_16_9
-- json_to_record and json_to_recordset
select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index e10c3fe0d96..ce3cb6b4829 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1103,7 +1103,6 @@ SELECT * FROM
FROM empsalary) emp
WHERE depname = 'sales';
-<<<<<<< HEAD
-- pushdown is unsafe because the subquery contains window functions and the
qual is volatile:
EXPLAIN (COSTS OFF)
SELECT * FROM
@@ -1112,7 +1111,7 @@ SELECT * FROM
min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary
FROM empsalary) emp
WHERE depname = 'sales' OR RANDOM() > 0.5;
-=======
+
-- Test window function run conditions are properly pushed down into the
-- WindowAgg
EXPLAIN (COSTS OFF)
@@ -1375,7 +1374,6 @@ SELECT * FROM
count((SELECT 1)) OVER (ORDER BY empno DESC) c
FROM empsalary) emp
WHERE c = 1;
->>>>>>> REL_16_9
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
