0006-disable-frame-opt.txt
text/plain
From 458224afa9df8438413b2f37f87175581f82aeab Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Wed, 4 Mar 2026 15:59:45 +0900
Subject: [PATCH 06/12] Disable frame optimization for RPR windows
---
src/backend/optimizer/plan/planner.c | 14 ++-
src/test/regress/expected/rpr_explain.out | 145 ++++++++++++++++++++++
src/test/regress/sql/rpr_explain.sql | 98 +++++++++++++++
3 files changed, 256 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 04faf919033..842c2aa2b94 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -5907,6 +5907,14 @@ optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
if (wflists->windowFuncs[wc->winref] == NIL)
continue;
+ /*
+ * If a DEFINE clause exists, do not let support functions replace the
+ * frame with a non-RPR-compatible one. RPR windows require ROWS
+ * BETWEEN CURRENT ROW AND ...
+ */
+ if (wc->defineClause != NIL)
+ continue;
+
foreach(lc2, wflists->windowFuncs[wc->winref])
{
SupportRequestOptimizeWindowClause req;
@@ -5990,7 +5998,11 @@ optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
equal(wc->orderClause, existing_wc->orderClause) &&
wc->frameOptions == existing_wc->frameOptions &&
equal(wc->startOffset, existing_wc->startOffset) &&
- equal(wc->endOffset, existing_wc->endOffset))
+ equal(wc->endOffset, existing_wc->endOffset) &&
+ wc->rpSkipTo == existing_wc->rpSkipTo &&
+ wc->initial == existing_wc->initial &&
+ equal(wc->defineClause, existing_wc->defineClause) &&
+ equal(wc->rpPattern, existing_wc->rpPattern))
{
ListCell *lc4;
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index 3c70a12874a..e07ad57f300 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -3821,3 +3821,148 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=500.00 loops=1)
(9 rows)
+--
+-- Planner optimization: optimize_window_clauses must not alter RPR frame
+--
+-- optimize_window_clauses() replaces frame options via prosupport functions.
+-- Affected functions: row_number, rank, dense_rank, percent_rank, cume_dist,
+-- ntile. All would change the frame to ROWS UNBOUNDED PRECEDING, breaking
+-- RPR's required ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
+-- Test with row_number() as representative case.
+--
+-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
+CREATE VIEW rpr_ev87 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+);
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+ QUERY PLAN
+--------------------------------------------------------------
+ Subquery Scan on rpr_ev87
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING)
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(6 rows)
+
+-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+CREATE VIEW rpr_ev88 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+);
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Subquery Scan on rpr_ev88
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: a b+
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(7 rows)
+
+--
+-- Planner optimization: non-RPR and RPR windows that share the same base frame
+-- after frame optimization are kept as separate WindowAgg nodes.
+--
+CREATE VIEW rpr_ev89 AS
+SELECT
+ row_number() OVER w_normal AS rn_normal,
+ row_number() OVER w_rpr AS rn_rpr
+FROM generate_series(1, 5) AS s(v)
+WINDOW
+ w_normal AS (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ w_rpr AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS v > 1
+ );
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev89;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Subquery Scan on rpr_ev89
+ -> WindowAgg
+ Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: a+"
+ -> WindowAgg
+ Window: w_normal AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING)
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(9 rows)
+
+--
+-- Planner optimization: find_window_run_conditions must not push down
+-- RPR window function results as Run Conditions.
+--
+-- find_window_run_conditions() pushes WHERE filters on monotonic window
+-- functions into WindowAgg as Run Conditions for early termination.
+-- With RPR's required frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED
+-- FOLLOWING), the monotonic direction determines which operators trigger
+-- Run Condition pushdown:
+-- INCREASING (<=): row_number, rank, dense_rank, percent_rank,
+-- cume_dist, ntile
+-- DECREASING (>): count(*) (via int8inc, END_UNBOUNDED_FOLLOWING)
+-- RPR window function results are match-dependent, not monotonic.
+-- Test with count(*) > 0 as representative case.
+--
+-- Without RPR: count(*) > 0 is pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ )
+) t WHERE cnt > 0;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Subquery Scan on t
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Run Condition: (count(*) OVER w > 0)
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(7 rows)
+
+-- With RPR: count(*) > 0 must not be pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+ )
+) t WHERE cnt > 0;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Subquery Scan on t
+ Filter: (t.cnt > 0)
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: a b+
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(8 rows)
+
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index 8e22382a68e..93e06b0cbdf 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -2196,3 +2196,101 @@ WINDOW w AS (
E AS v % 100 = 5
);');
+--
+-- Planner optimization: optimize_window_clauses must not alter RPR frame
+--
+-- optimize_window_clauses() replaces frame options via prosupport functions.
+-- Affected functions: row_number, rank, dense_rank, percent_rank, cume_dist,
+-- ntile. All would change the frame to ROWS UNBOUNDED PRECEDING, breaking
+-- RPR's required ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
+-- Test with row_number() as representative case.
+--
+
+-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
+CREATE VIEW rpr_ev87 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+);
+
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+
+-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+CREATE VIEW rpr_ev88 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+);
+
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+
+--
+-- Planner optimization: non-RPR and RPR windows that share the same base frame
+-- after frame optimization are kept as separate WindowAgg nodes.
+--
+CREATE VIEW rpr_ev89 AS
+SELECT
+ row_number() OVER w_normal AS rn_normal,
+ row_number() OVER w_rpr AS rn_rpr
+FROM generate_series(1, 5) AS s(v)
+WINDOW
+ w_normal AS (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
+ w_rpr AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS v > 1
+ );
+
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev89;
+
+--
+-- Planner optimization: find_window_run_conditions must not push down
+-- RPR window function results as Run Conditions.
+--
+-- find_window_run_conditions() pushes WHERE filters on monotonic window
+-- functions into WindowAgg as Run Conditions for early termination.
+-- With RPR's required frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED
+-- FOLLOWING), the monotonic direction determines which operators trigger
+-- Run Condition pushdown:
+-- INCREASING (<=): row_number, rank, dense_rank, percent_rank,
+-- cume_dist, ntile
+-- DECREASING (>): count(*) (via int8inc, END_UNBOUNDED_FOLLOWING)
+-- RPR window function results are match-dependent, not monotonic.
+-- Test with count(*) > 0 as representative case.
+--
+
+-- Without RPR: count(*) > 0 is pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ )
+) t WHERE cnt > 0;
+
+-- With RPR: count(*) > 0 must not be pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+ )
+) t WHERE cnt > 0;
+
--
2.50.1 (Apple Git-155)