From 8b1ef0227cb3e353a93b38a07602d51caa2a26d6 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 6 Apr 2026 10:46:38 +0900 Subject: [PATCH] Add Nav Mark Lookback to EXPLAIN and fix compute_nav_max_offset() Show the planner-computed navMaxOffset in EXPLAIN output as "Nav Mark Lookback" for RPR windows, so that tuplestore trim behavior is visible in query plans. Displayed as an integer for constant offsets, "runtime" for non-constant (Param) offsets, and "retain all" when the entire partition must be preserved. Fix compute_nav_max_offset() to return the walker-set value instead of unconditionally returning RPR_NAV_OFFSET_NEEDS_EVAL when the walker stops early. Add regression tests covering constant offsets, NEXT-only, multiple PREV offsets, and host variable offsets under both custom and generic plans. --- src/backend/commands/explain.c | 9 + src/backend/optimizer/plan/createplan.c | 2 +- src/test/regress/expected/rpr_base.out | 231 ++++++---- src/test/regress/expected/rpr_explain.out | 397 ++++++++++++++---- src/test/regress/expected/rpr_integration.out | 25 +- src/test/regress/sql/rpr_explain.sql | 71 ++++ 6 files changed, 560 insertions(+), 175 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 933eadab71e..1848de9de7a 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -3254,6 +3254,15 @@ show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es) ExplainPropertyText("Pattern", patternStr, es); pfree(patternStr); } + + /* Show navigation offsets for tuplestore trim */ + if (wagg->navMaxOffset == RPR_NAV_OFFSET_RETAIN_ALL) + ExplainPropertyText("Nav Mark Lookback", "retain all", es); + else if (wagg->navMaxOffset == RPR_NAV_OFFSET_NEEDS_EVAL) + ExplainPropertyText("Nav Mark Lookback", "runtime", es); + else + ExplainPropertyInteger("Nav Mark Lookback", NULL, + wagg->navMaxOffset, es); } } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index ee2d53b5924..8ee3ccf6d0d 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -2553,7 +2553,7 @@ compute_nav_max_offset(List *defineClause) TargetEntry *te = (TargetEntry *) lfirst(lc); if (nav_max_offset_walker((Node *) te->expr, &maxOffset)) - return RPR_NAV_OFFSET_NEEDS_EVAL; + return maxOffset; /* NEEDS_EVAL or RETAIN_ALL */ } return maxOffset; diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 37aa81ebdea..1e450a07ced 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -3065,10 +3065,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive VAR merge: A{2} A{3} -> a{5} EXPLAIN (COSTS OFF) @@ -3080,10 +3081,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{5} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive VAR merge: A+ A* -> a+ EXPLAIN (COSTS OFF) @@ -3095,10 +3097,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive VAR merge: A A+ -> a{2,} -- Tests line 251: child->max == RPR_QUANTITY_INF branch in mergeConsecutiveVars @@ -3112,10 +3115,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive GROUP merge with finite quantifiers: ((A B){5}) ((A B){10}) -> merged EXPLAIN (COSTS OFF) @@ -3127,10 +3131,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){15} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive GROUP merge with unbounded: (A B)+ (A B)+ -> (a b){2,} EXPLAIN (COSTS OFF) @@ -3142,10 +3147,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive GROUP merge: (A B){2} (A B)+ -> (a b){3,} -- Tests line 325: child->max == RPR_QUANTITY_INF branch in mergeConsecutiveGroups @@ -3159,10 +3165,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){3,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX merge: A B (A B)+ -> (a b){2,} EXPLAIN (COSTS OFF) @@ -3174,10 +3181,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX and SUFFIX merge: A B (A B)+ A B -> (a b){3,} EXPLAIN (COSTS OFF) @@ -3189,10 +3197,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){3,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Flatten nested: A ((B) (C)) -> a b c EXPLAIN (COSTS OFF) @@ -3204,10 +3213,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Data execution: SEQ flatten produces correct results SELECT id, val, count(*) OVER w AS cnt @@ -3239,10 +3249,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- ALT deduplicate: (A | B | A) -> (a | b) EXPLAIN (COSTS OFF) @@ -3254,10 +3265,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Data execution: ALT dedup produces correct results SELECT id, val, count(*) OVER w AS cnt @@ -3289,10 +3301,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{6} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier multiply with child range: (A{2,3}){3} -> a{6,9} -- outer exact, child range - optimization applies @@ -3305,10 +3318,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{6,9} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier NO multiply: (A{2}){2,3} stays as (a{2}){2,3} -- outer range - gaps would occur (4,6 not 4,5,6), no optimization @@ -3321,10 +3335,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}){2,3} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier NO multiply: (A{2}){2,} stays as (a{2}){2,} -- outer unbounded - gaps would occur (4,6,8,... not 4,5,6,...), no optimization @@ -3337,10 +3352,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}'){2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier multiply: (A){2,} -> a{2,} -- child exact 1 - no gaps, optimization applies @@ -3353,10 +3369,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier multiply: (A)+ -> a+ -- child exact 1 - no gaps, optimization applies @@ -3369,10 +3386,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier NO multiply: (A{2}){3,5} stays as (a{2}){3,5} -- outer range, child exact > 1 - gaps would occur (6,8,10 not 6,7,8,9,10) @@ -3385,10 +3403,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}){3,5} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Quantifier NO multiply: (A{2,3}){2,3} stays as (a{2,3}){2,3} -- outer range, child range - gaps possible (e.g., (A{4,5}){2,3} misses 11) @@ -3401,10 +3420,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2,3}){2,3} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested unbounded: (A*)* -> a* EXPLAIN (COSTS OFF) @@ -3416,10 +3436,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested unbounded: (A+)* -> a* EXPLAIN (COSTS OFF) @@ -3431,10 +3452,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested unbounded: (A+)+ -> a+ EXPLAIN (COSTS OFF) @@ -3446,10 +3468,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Unwrap GROUP{1,1}: (A) -> a EXPLAIN (COSTS OFF) @@ -3461,10 +3484,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Unwrap GROUP{1,1}: (A B) -> a b EXPLAIN (COSTS OFF) @@ -3476,10 +3500,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Combined optimization: A A (B B)+ B B C C C -> a{2} (b{2}){2,} c{3} EXPLAIN (COSTS OFF) @@ -3492,10 +3517,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2} (b{2}){2,} c{3} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive GROUP merge with unbounded: (A+) (A+) -> a{2,} -- Tests mergeConsecutiveGroups with child->max == INF @@ -3508,10 +3534,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive GROUP merge finite: (A{10}){20} -> a{200} -- Tests mergeConsecutiveGroups with both finite @@ -3524,10 +3551,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{200} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Different GROUP prevents merge: (A B){2} (C D){3} -- Tests mergeConsecutiveGroups flush previous @@ -3542,10 +3570,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){2} (c d){3} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Different children count prevents merge: (A B)+ (A B C)+ -- Tests rprPatternChildrenEqual length check @@ -3559,10 +3588,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" (a b c)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX only merge: A B (A B)+ -> (a b){2,} -- Tests mergeGroupPrefixSuffix: absorb preceding elements into GROUP min @@ -3575,10 +3605,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- SUFFIX only merge: (A B)+ A B -> (a b){2,} -- Tests mergeGroupPrefixSuffix: absorb following elements into GROUP min @@ -3591,10 +3622,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Multiple SUFFIX absorption with skipUntil: (A B)+ A B A B C -- Tests mergeGroupPrefixSuffix: skip absorbed suffix elements @@ -3608,10 +3640,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){3,}" c + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX merge with remaining prefix: A B C D (C D)+ -- Tests mergeGroupPrefixSuffix: trimmed list reconstruction @@ -3626,10 +3659,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (c d){2,} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX merge with quantifiers: A B* (A B*)+ -> (a b*){2,} -- Tests mergeGroupPrefixSuffix: quantifier comparison in rprPatternEqual @@ -3643,10 +3677,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b*){2,} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX merge with multiple quantifiers: A+ B* C? (A+ B* C?)+ -> (a+ b* c?){2,} -- Tests mergeGroupPrefixSuffix: complex quantifier patterns @@ -3660,10 +3695,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" b* c?){2,} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- SUFFIX merge with quantifiers: (A B*)+ A B* -> (a b*){2,} -- Tests mergeGroupPrefixSuffix: suffix with quantifiers @@ -3677,10 +3713,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b*){2,} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Unwrap GROUP{1,1}: ((A | B | C)) -> (a | b | c) -- Tests tryUnwrapGroup removing redundant outer GROUP @@ -3693,10 +3730,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c) + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Data execution: GROUP unwrap produces correct results SELECT id, val, count(*) OVER w AS cnt @@ -3729,10 +3767,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+? a + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant optimization bypass: GROUP merge -- (A B)+? (A B) stays separate (greedy merges to (a b){2,}) @@ -3745,10 +3784,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b)+? a b + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant optimization bypass: quantifier multiply (outer reluctant) -- (A{2}){3}? stays as (a{2}){3}? (greedy merges to a{6}) @@ -3761,10 +3801,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}){3}? + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant optimization bypass: quantifier multiply (inner reluctant) -- (A{2}?){3} stays as (a{2}?){3} (greedy merges to a{6}) @@ -3777,10 +3818,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}?){3} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant optimization bypass: PREFIX merge -- A B (A B)+? stays separate (greedy merges to (a b){2,}) @@ -3793,10 +3835,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (a b)+? + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant optimization bypass: SUFFIX merge -- (A B)+? A B stays separate (greedy merges to (a b){2,}) @@ -3809,10 +3852,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b)+? a b + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- GROUP unwrap with quantifier propagation: (A)?? B -> a?? b -- Single VAR child {1,1} receives GROUP's quantifier and reluctant @@ -3825,10 +3869,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a?? b + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant preserved through ALT flatten -- (A | (B | C))+? flattens to (a | b | c)+? - inner ALT flattened, reluctant kept @@ -3841,10 +3886,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+? + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant optimization bypass: absorption flags -- A+? with SKIP PAST LAST ROW - no absorption markers (greedy A+ gets a+") @@ -3857,10 +3903,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+? + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Duplicate GROUP removal: ((A | B)+ | (A | B)+) -> (a | b)+ EXPLAIN (COSTS OFF) @@ -3872,10 +3919,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive VAR merge with zero-min: A* A+ -> a+ EXPLAIN (COSTS OFF) @@ -3887,10 +3935,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Consecutive VAR merge (4-element): A A{2} A+ A{3} -> a{7,} EXPLAIN (COSTS OFF) @@ -3902,10 +3951,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{7,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- PREFIX+SUFFIX merge (5-way): A B A B (A B)+ A B A B -> (a b){5,} EXPLAIN (COSTS OFF) @@ -3918,10 +3968,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){5,}" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Unwrap single-item ALT after dedup: (A | A)+ -> a+ -- ALT dedup reduces to single-item, then GROUP unwrap @@ -3934,10 +3985,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- GROUP{1,1} to SEQ with flatten: ((A B)(C D)) -> a b c d EXPLAIN (COSTS OFF) @@ -3951,10 +4003,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested ALT pattern: ((A B) | C) D | A B C EXPLAIN (COSTS OFF) @@ -3968,10 +4021,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a b | c) d | a b c) + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested ALT with unbounded: ((A+ B) | C) D | A B C EXPLAIN (COSTS OFF) @@ -3985,10 +4039,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a+" b | c) d | a b c) + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- ============================================================ -- Absorption Flag Display Tests @@ -4006,10 +4061,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- GROUP unbounded: (A B)+ -> (a' b')+" (branch + judgment) EXPLAIN (COSTS OFF) @@ -4021,10 +4077,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- ALT both absorbable: A+ | B+ -> (a+" | b+") EXPLAIN (COSTS OFF) @@ -4036,10 +4093,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b+") + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- ALT one absorbable: A+ | B -> (a+" | b) EXPLAIN (COSTS OFF) @@ -4051,10 +4109,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b) + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Sequence with absorbable start: A+ B -> a+" b EXPLAIN (COSTS OFF) @@ -4066,10 +4125,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Complex nested: ((A+ B) | C) D | A B C - deeply nested ALT EXPLAIN (COSTS OFF) @@ -4082,10 +4142,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a+" b | c) d | a b c) + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested unbounded: (A+ | B)+ -> (a+" | b)+ (first iteration absorbable) EXPLAIN (COSTS OFF) @@ -4098,10 +4159,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- ALT inside unbounded GROUP: (A+ B | A B)* -> (a+" b | a b)* (first iteration absorbable) EXPLAIN (COSTS OFF) @@ -4114,10 +4176,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" b | a b)* + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Fixed-length group absorbable: (A{2} B{3})+ -> (a{2}' b{3}'){2,}" -- All children have min == max, equivalent to unrolling to {1,1} @@ -4131,10 +4194,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}' b{3}')+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested fixed-length group: (A (B C){2} D)+ -> absorbable EXPLAIN (COSTS OFF) @@ -4147,10 +4211,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' (b' c'){2}' d')+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Nested fixed-length with inner quantifier: ((A{2} B{3}){2})+ -> absorbable EXPLAIN (COSTS OFF) @@ -4163,10 +4228,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a{2}' b{3}'){2}')+" + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Non-absorbable fixed-length: (A B{2,5})+ -> no markers (min != max) EXPLAIN (COSTS OFF) @@ -4179,10 +4245,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b{2,5})+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Non-absorbable fixed-length: (A B?)+ -> no markers (min != max) EXPLAIN (COSTS OFF) @@ -4195,10 +4262,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b?)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Non-absorbable (unbounded not at start): A B+ -> a b+ (no markers) EXPLAIN (COSTS OFF) @@ -4210,10 +4278,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Non-absorbable (no unbounded branch): (A | B){2,} -> (a | b){2,} (no markers) EXPLAIN (COSTS OFF) @@ -4225,10 +4294,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2,} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Non-absorbable (SKIP TO NEXT ROW): A+ -> a+ (no markers) EXPLAIN (COSTS OFF) @@ -4240,10 +4310,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Non-absorbable (limited frame): A+ -> a+ (no markers) EXPLAIN (COSTS OFF) @@ -4255,10 +4326,11 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND '10'::bigint FOLLOWING) Pattern: a+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- Reluctant {1}? quantifier deparse -- A{1}? is a reluctant {1,1} quantifier. The deparse code must @@ -4277,10 +4349,11 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY val ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{1}? b + Nav Mark Lookback: 0 -> Sort Sort Key: val -> Seq Scan on rpr_plan -(6 rows) +(7 rows) -- ============================================================ -- Absorption Analysis Tests @@ -4775,10 +4848,11 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2000000000}){2} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(6 rows) +(7 rows) -- Expected: Fallback - pattern not merged due to min overflow (4000000000 > INT32_MAX) -- Test: max-only quantifier overflow causes optimization fallback @@ -4795,10 +4869,11 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{1,2000000000}){2} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(6 rows) +(7 rows) -- Expected: Fallback - min OK (2*1=2), but max overflow (2*2000000000 > INT32_MAX) -- Test: max quantifier exceeds valid range (2147483647 = INT_MAX, limit is 2147483646) @@ -4828,10 +4903,11 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2000000000,}"){2000000000,} + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(6 rows) +(7 rows) -- Expected: Fallback - min overflow (2000000000 * 2000000000 > INT32_MAX) -- Test: prefix mismatch causes optimization fallback @@ -4848,10 +4924,11 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (c d)+ + Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(6 rows) +(7 rows) -- Expected: Fallback - prefix elements don't match GROUP content DROP TABLE rpr_fallback; diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index dc3075e6bd3..77ab25a2289 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -36,6 +36,7 @@ -- Window Function Combinations -- DEFINE Expression Variations -- Large Scale Statistics Verification +-- Nav Mark Lookback (tuplestore trim) -- ============================================================ -- Filter function to normalize platform-dependent memory values (not NFA statistics). -- NFA statistics should not change between platforms; if they do, it could @@ -141,13 +142,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 60 pruned NFA: 20 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Pattern with no matches - 0 matched CREATE VIEW rpr_ev_basic_nomatch AS @@ -180,12 +182,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: x y z + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 1 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 100 pruned NFA: 0 matched, 0 mismatched -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(8 rows) +(9 rows) -- Pattern matching every row - high match count CREATE VIEW rpr_ev_basic_allrows AS @@ -218,12 +221,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: r + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 100 matched (len 1/1/1.0), 0 mismatched -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(8 rows) +(9 rows) -- Regression test: Space before parenthesis in pattern deparse -- Verifies that "A (B | C)" correctly outputs as "a (b | c)" with space @@ -255,13 +259,14 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a (b | c) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 35 total, 0 merged NFA Contexts: 2 peak, 21 total, 6 pruned NFA: 7 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 7 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(10 rows) -- Regression test: Sequential alternations at same depth -- Verifies that "((B | C) (D | E))" correctly outputs as "(b | c) (d | e)" @@ -294,12 +299,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a ((b | c) (d | e))* + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 61 total, 0 merged NFA Contexts: 3 peak, 31 total, 24 pruned NFA: 6 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(8 rows) +(9 rows) -- Regression test: Quoted identifiers in EXPLAIN pattern deparse -- Mixed case names must be quoted to preserve round-trip safety @@ -317,8 +323,9 @@ WINDOW w AS ( WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: "Start" "Up"+ + Nav Mark Lookback: 1 -> Function Scan on generate_series s -(4 rows) +(5 rows) -- ============================================================ -- State Statistics Tests (peak, total, merged) @@ -354,12 +361,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 76 total, 0 merged NFA Contexts: 3 peak, 51 total, 25 pruned NFA: 25 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(8 rows) +(9 rows) -- Alternation pattern - multiple state branches CREATE VIEW rpr_ev_state_alt AS @@ -396,13 +404,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c) (d | e) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 524 total, 0 merged NFA Contexts: 3 peak, 101 total, 20 pruned NFA: 20 matched (len 2/2/2.0), 40 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Complex pattern with high state count CREATE VIEW rpr_ev_state_complex AS @@ -441,13 +450,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b* c+ + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 235 total, 0 merged NFA Contexts: 3 peak, 101 total, 34 pruned NFA: 33 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 33 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Grouped pattern with quantifier - state count with grouping CREATE VIEW rpr_ev_state_group_quant AS @@ -480,13 +490,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 91 total, 0 merged NFA Contexts: 3 peak, 61 total, 0 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched NFA: 29 absorbed (len 2/2/2.0), 30 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- State explosion pattern - many alternations -- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states @@ -520,13 +531,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){8} + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 17 peak, 995 total, 0 merged NFA Contexts: 8 peak, 101 total, 1 pruned NFA: 12 matched (len 8/8/8.0), 3 mismatched (len 2/4/3.0) NFA: 0 absorbed, 84 skipped (len 1/7/4.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Consecutive ALT merge followed by different ALT -- Tests mergeConsecutiveAlts flush on ALT change: (A|B){2} (C|D) @@ -560,13 +572,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2} (c | d) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 181 total, 0 merged NFA Contexts: 3 peak, 41 total, 12 pruned NFA: 9 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 18 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Consecutive ALT merge followed by non-ALT element -- Tests mergeConsecutiveAlts flush on non-ALT: (A|B){2} c @@ -600,13 +613,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2} c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 177 total, 0 merged NFA Contexts: 3 peak, 41 total, 2 pruned NFA: 12 matched (len 3/3/3.0), 2 mismatched (len 2/2/2.0) NFA: 0 absorbed, 24 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- ALT prefix/suffix absorbed into GROUP: (A|B) (A|B)+ (A|B) -> (A|B){3,} CREATE VIEW rpr_ev_state_alt_absorb_group AS @@ -639,13 +653,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){3,} + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 243 total, 0 merged NFA Contexts: 3 peak, 41 total, 0 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched NFA: 0 absorbed, 39 skipped (len 1/2/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- High state count - alternation with plus quantifier CREATE VIEW rpr_ev_state_alt_plus AS @@ -678,13 +693,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+ d + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 16 peak, 1004 total, 0 merged NFA Contexts: 4 peak, 101 total, 0 pruned NFA: 25 matched (len 4/4/4.0), 0 mismatched NFA: 0 absorbed, 75 skipped (len 1/3/2.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Early termination: first ALT branch (A) reaches FIN immediately, -- pruning second branch (A B+) before it can accumulate B repetitions. @@ -718,12 +734,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | a b)+ + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 306 total, 0 merged NFA Contexts: 3 peak, 101 total, 99 pruned NFA: 1 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(8 rows) +(9 rows) -- Nested quantifiers causing state growth CREATE VIEW rpr_ev_state_nested_quant AS @@ -756,13 +773,14 @@ WINDOW w AS ( WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 5004 total, 0 merged NFA Contexts: 3 peak, 1001 total, 333 pruned NFA: 334 matched (len 1/2/2.0), 0 mismatched NFA: 0 absorbed, 333 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Context Statistics Tests (peak, total, pruned + absorbed/skipped) @@ -798,13 +816,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- No absorption - bounded quantifier CREATE VIEW rpr_ev_ctx_no_absorb AS @@ -837,13 +856,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,4} b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 101 total, 0 merged NFA Contexts: 5 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 40 skipped (len 1/4/2.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- Contexts skipped by SKIP PAST LAST ROW CREATE VIEW rpr_ev_ctx_skip AS @@ -876,13 +896,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 80 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- High context absorption - unbounded group CREATE VIEW rpr_ev_ctx_absorb_group AS @@ -915,13 +936,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 134 total, 0 merged NFA Contexts: 3 peak, 101 total, 34 pruned NFA: 33 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 33 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Fixed-length group absorption: (A B B)+ C -- B B merged to B{2}; absorbable with fixed-length check @@ -956,13 +978,14 @@ WINDOW w AS ( WindowAgg (actual rows=70.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b{2}')+" c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 4 peak, 71 total, 40 pruned NFA: 10 matched (len 7/7/7.0), 0 mismatched NFA: 10 absorbed (len 3/3/3.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=70.00 loops=1) -(9 rows) +(10 rows) -- Nested fixed-length group absorption: (A (B C){2} D)+ E -- step_size = 1 + (1+1)*2 + 1 = 6; v % 13 cycle gives 2 iterations + E @@ -1000,13 +1023,14 @@ WINDOW w AS ( WindowAgg (actual rows=65.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' (b' c'){2}' d')+" e + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 76 total, 0 merged NFA Contexts: 4 peak, 66 total, 50 pruned NFA: 5 matched (len 13/13/13.0), 0 mismatched NFA: 5 absorbed (len 6/6/6.0), 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=65.00 loops=1) -(9 rows) +(10 rows) -- Doubly nested fixed-length group absorption: (A ((B C{3}){2} D){2} E)+ F -- step_size = 1 + ((1+3)*2+1)*2 + 1 = 20; v % 41 cycle gives 2 iterations + F @@ -1052,13 +1076,14 @@ WINDOW w AS ( WindowAgg (actual rows=82.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' ((b' c{3}'){2}' d'){2}' e')+" f + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 87 total, 0 merged NFA Contexts: 4 peak, 83 total, 76 pruned NFA: 2 matched (len 41/41/41.0), 0 mismatched NFA: 2 absorbed (len 20/20/20.0), 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=82.00 loops=1) -(9 rows) +(10 rows) -- 3-level END chain absorption: ((A (B C){2}){2})+ -- step_size = (1 + (1+1)*2) * 2 = 10; v % 21 cycle gives 2 iterations @@ -1097,13 +1122,14 @@ WINDOW w AS ( WindowAgg (actual rows=42.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' (b' c'){2}'){2}')+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 47 total, 0 merged NFA Contexts: 5 peak, 43 total, 30 pruned NFA: 2 matched (len 20/20/20.0), 0 mismatched NFA: 2 absorbed (len 10/10/10.0), 8 skipped (len 1/5/3.0) -> Function Scan on generate_series s (actual rows=42.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Match Length Statistics Tests @@ -1143,13 +1169,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 20 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Variable length matches - min/max/avg differ CREATE VIEW rpr_ev_mlen_variable AS @@ -1182,13 +1209,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 191 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 10 matched (len 10/10/10.0), 0 mismatched NFA: 80 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Very long matches CREATE VIEW rpr_ev_mlen_long AS @@ -1221,13 +1249,14 @@ WINDOW w AS ( WindowAgg (actual rows=200.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 396 total, 0 merged NFA Contexts: 2 peak, 201 total, 4 pruned NFA: 1 matched (len 196/196/196.0), 0 mismatched NFA: 194 absorbed (len 1/1/1.0), 1 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=200.00 loops=1) -(9 rows) +(10 rows) -- Uniform match length with mismatches from gap rows (v%20 = 11..15) CREATE VIEW rpr_ev_mlen_with_mismatch AS @@ -1264,13 +1293,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 171 total, 0 merged NFA Contexts: 3 peak, 101 total, 25 pruned NFA: 5 matched (len 5/5/5.0), 5 mismatched (len 11/11/11.0) NFA: 60 absorbed (len 1/1/1.0), 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Mismatch Length Statistics Tests @@ -1321,13 +1351,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b+ c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 151 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 10 matched (len 6/6/6.0), 0 mismatched NFA: 20 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Long partial matches that fail CREATE VIEW rpr_ev_mlen_long_partial AS @@ -1384,13 +1415,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b+ c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 115 total, 0 merged NFA Contexts: 3 peak, 61 total, 15 pruned NFA: 1 matched (len 30/30/30.0), 1 mismatched (len 26/26/26.0) NFA: 42 absorbed (len 1/1/1.0), 1 skipped (len 1/1/1.0) -> Function Scan on generate_series i (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- JSON Format Tests @@ -1434,6 +1466,7 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a+\" b+", + + "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 3, + @@ -1511,6 +1544,7 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a+\" b", + + "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 3, + @@ -1592,6 +1626,7 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a b c", + + "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 2, + @@ -1672,6 +1707,7 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "(a | b){8}", + + "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 17, + @@ -1755,6 +1791,7 @@ WINDOW w AS ( false + w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ a b + + 0 + Memory + 0 + 2 + @@ -1837,6 +1874,7 @@ WINDOW w AS ( WindowAgg (actual rows=90.00 loops=1) Window: w AS (PARTITION BY p.p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 165 total, 0 merged NFA Contexts: 2 peak, 93 total, 0 pruned @@ -1848,7 +1886,7 @@ WINDOW w AS ( -> Nested Loop (actual rows=90.00 loops=1) -> Function Scan on generate_series p (actual rows=3.00 loops=1) -> Function Scan on generate_series v (actual rows=30.00 loops=3) -(14 rows) +(15 rows) -- Different pattern behavior per partition CREATE VIEW rpr_ev_part_diff AS @@ -1893,6 +1931,7 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (PARTITION BY (CASE WHEN (v.v <= 25) THEN 1 ELSE 2 END) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 77 total, 0 merged NFA Contexts: 2 peak, 52 total, 21 pruned @@ -1902,7 +1941,7 @@ WINDOW w AS ( Sort Key: (CASE WHEN (v.v <= 25) THEN 1 ELSE 2 END) Sort Method: quicksort Memory: NkB -> Function Scan on generate_series v (actual rows=50.00 loops=1) -(12 rows) +(13 rows) -- ============================================================ -- Edge Cases @@ -1938,8 +1977,9 @@ WINDOW w AS ( WindowAgg (actual rows=0.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b + Nav Mark Lookback: 0 -> Function Scan on generate_series s (actual rows=0.00 loops=1) -(4 rows) +(5 rows) -- Single row CREATE VIEW rpr_ev_edge_single_row AS @@ -1972,12 +2012,13 @@ WINDOW w AS ( WindowAgg (actual rows=1.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 2 total, 0 merged NFA Contexts: 2 peak, 2 total, 0 pruned NFA: 1 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=1.00 loops=1) -(8 rows) +(9 rows) -- Pattern longer than data CREATE VIEW rpr_ev_edge_pattern_longer AS @@ -2014,12 +2055,13 @@ WINDOW w AS ( WindowAgg (actual rows=5.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e f g h i j + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 6 total, 0 merged NFA Contexts: 3 peak, 6 total, 4 pruned NFA: 0 matched, 1 mismatched (len 5/5/5.0) -> Function Scan on generate_series s (actual rows=5.00 loops=1) -(8 rows) +(9 rows) -- All rows match as single match CREATE VIEW rpr_ev_edge_single_match AS @@ -2052,13 +2094,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 101 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 1 matched (len 50/50/50.0), 0 mismatched NFA: 49 absorbed (len 1/1/1.0), 0 skipped -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Complex Pattern Tests @@ -2094,13 +2137,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b' c')+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 81 total, 0 merged NFA Contexts: 4 peak, 61 total, 20 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched NFA: 19 absorbed (len 3/3/3.0), 20 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Multiple alternations CREATE VIEW rpr_ev_cpx_multi_alt AS @@ -2137,13 +2181,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) (c | d | e) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 423 total, 0 merged NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Optional elements CREATE VIEW rpr_ev_cpx_optional AS @@ -2176,13 +2221,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b? c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 64 total, 0 merged NFA Contexts: 3 peak, 51 total, 25 pruned NFA: 12 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 12 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- Bounded quantifiers CREATE VIEW rpr_ev_cpx_bounded AS @@ -2215,13 +2261,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,5} b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 311 total, 0 merged NFA Contexts: 7 peak, 101 total, 0 pruned NFA: 10 matched (len 6/6/6.0), 40 mismatched (len 6/6/6.0) NFA: 0 absorbed, 50 skipped (len 1/5/3.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Star quantifier CREATE VIEW rpr_ev_cpx_star AS @@ -2254,13 +2301,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b* c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 51 total, 40 pruned NFA: 5 matched (len 9/9/9.0), 0 mismatched NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Real-world Pattern Examples @@ -2296,13 +2344,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: d+" u+ + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 58 total, 0 merged NFA Contexts: 3 peak, 31 total, 3 pruned NFA: 3 matched (len 3/14/8.0), 1 mismatched (len 3/3/3.0) NFA: 9 absorbed (len 1/1/1.0), 14 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_complex (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- Stock price pattern - peak (up, stable, down) CREATE VIEW rpr_ev_real_peak AS @@ -2335,13 +2384,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: u+" s* d+ + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 3 peak, 31 total, 1 pruned NFA: 4 matched (len 3/11/7.2), 0 mismatched NFA: 12 absorbed (len 1/1/1.0), 13 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_complex (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- Consecutive increasing values (using PREV) CREATE VIEW rpr_ev_real_increasing AS @@ -2374,13 +2424,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3,}" + Nav Mark Lookback: 1 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 99 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 1 matched (len 50/50/50.0), 0 mismatched NFA: 49 absorbed (len 1/1/1.0), 0 skipped -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Performance-oriented Tests @@ -2416,13 +2467,14 @@ WINDOW w AS ( WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 1001 total, 0 merged NFA Contexts: 2 peak, 1001 total, 0 pruned NFA: 500 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 500 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(9 rows) +(10 rows) -- Large dataset with absorption CREATE VIEW rpr_ev_perf_large_absorb AS @@ -2455,13 +2507,14 @@ WINDOW w AS ( WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 1991 total, 0 merged NFA Contexts: 2 peak, 1001 total, 0 pruned NFA: 10 matched (len 100/100/100.0), 0 mismatched NFA: 980 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(9 rows) +(10 rows) -- High state merge ratio CREATE VIEW rpr_ev_perf_high_merge AS @@ -2494,13 +2547,14 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 3006 total, 0 merged NFA Contexts: 3 peak, 501 total, 1 pruned NFA: 166 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 332 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- INITIAL vs no INITIAL comparison @@ -2538,13 +2592,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- Without INITIAL keyword (same behavior currently) CREATE VIEW rpr_ev_initial_without AS @@ -2577,13 +2632,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Quantifier Variations @@ -2619,13 +2675,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 71 total, 0 merged NFA Contexts: 3 peak, 41 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 20 absorbed (len 1/1/1.0), 0 skipped -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Star quantifier (zero or more) CREATE VIEW rpr_ev_quant_star AS @@ -2658,13 +2715,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 102 total, 0 merged NFA Contexts: 2 peak, 41 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 10 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Question mark (zero or one) CREATE VIEW rpr_ev_quant_question AS @@ -2697,13 +2755,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a? b c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 82 total, 0 merged NFA Contexts: 3 peak, 41 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Exact count {n} CREATE VIEW rpr_ev_quant_exact AS @@ -2736,13 +2795,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3} b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 51 total, 0 merged NFA Contexts: 5 peak, 51 total, 0 pruned NFA: 10 matched (len 4/4/4.0), 10 mismatched (len 4/4/4.0) NFA: 0 absorbed, 30 skipped (len 1/3/2.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- Range {n,m} CREATE VIEW rpr_ev_quant_range AS @@ -2775,13 +2835,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,4} b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 101 total, 0 merged NFA Contexts: 5 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 40 skipped (len 1/4/2.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- At least {n,} CREATE VIEW rpr_ev_quant_atleast AS @@ -2814,13 +2875,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3,}" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 86 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 5 matched (len 10/10/10.0), 0 mismatched NFA: 40 absorbed (len 1/1/1.0), 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Regression Tests for Statistics Accuracy @@ -2857,13 +2919,14 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 37 total, 0 merged NFA Contexts: 2 peak, 21 total, 0 pruned NFA: 4 matched (len 5/5/5.0), 0 mismatched NFA: 12 absorbed (len 1/1/1.0), 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(10 rows) -- Verify context count with known absorption CREATE VIEW rpr_ev_reg_ctx_absorb AS @@ -2896,13 +2959,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 52 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 3 matched (len 9/9/9.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 3 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- Verify match length with fixed-length pattern CREATE VIEW rpr_ev_reg_matchlen AS @@ -2935,13 +2999,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 31 total, 0 merged NFA Contexts: 3 peak, 31 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Alternation Pattern Tests @@ -2977,13 +3042,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 303 total, 0 merged NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Multiple items in alternation CREATE VIEW rpr_ev_alt_multi_item AS @@ -3020,13 +3086,14 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c | d) e + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 505 total, 0 merged NFA Contexts: 3 peak, 101 total, 0 pruned NFA: 20 matched (len 2/2/2.0), 60 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(10 rows) -- Alternation with quantifiers CREATE VIEW rpr_ev_alt_with_quant AS @@ -3059,13 +3126,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 306 total, 0 merged NFA Contexts: 3 peak, 51 total, 1 pruned NFA: 16 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 32 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- Multiple alternatives (4+) CREATE VIEW rpr_ev_alt_four_plus AS @@ -3096,12 +3164,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c | d | e) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 606 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 100 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(8 rows) +(9 rows) -- Alternation at start CREATE VIEW rpr_ev_alt_at_start AS @@ -3132,13 +3201,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c d + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 183 total, 0 merged NFA Contexts: 3 peak, 61 total, 16 pruned NFA: 15 matched (len 3/3/3.0), 14 mismatched (len 2/2/2.0) NFA: 0 absorbed, 15 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Multiple sequential alternations CREATE VIEW rpr_ev_alt_sequential AS @@ -3169,12 +3239,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c (d | e) f + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 337 total, 0 merged NFA Contexts: 3 peak, 101 total, 67 pruned NFA: 0 matched, 33 mismatched (len 2/4/3.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(8 rows) +(9 rows) -- Quantified alternatives CREATE VIEW rpr_ev_alt_quantified AS @@ -3205,13 +3276,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b+") c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 223 total, 0 merged NFA Contexts: 3 peak, 61 total, 1 pruned NFA: 20 matched (len 2/2/2.0), 19 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Alternation at end CREATE VIEW rpr_ev_alt_at_end AS @@ -3242,13 +3314,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (c | d) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 89 total, 0 merged NFA Contexts: 3 peak, 61 total, 32 pruned NFA: 14 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 14 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Nested ALT at start of branch inside outer ALT -- Pattern: (A ((B | C) D | E)) - preceding VAR + inner ALT as first branch element @@ -3280,12 +3353,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a ((b | c) d | e) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 37 total, 0 merged NFA Contexts: 3 peak, 21 total, 17 pruned NFA: 0 matched, 3 mismatched (len 3/3/3.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(8 rows) +(9 rows) -- Nested ALT at end of branch inside outer ALT -- Pattern: (C (A | B) | D) - inner ALT is last element in outer branch @@ -3317,12 +3391,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c (a | b) | d) + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 73 total, 0 merged NFA Contexts: 3 peak, 21 total, 10 pruned NFA: 5 matched (len 1/1/1.0), 5 mismatched (len 2/2/2.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(8 rows) +(9 rows) -- ============================================================ -- Group Pattern Tests @@ -3358,13 +3433,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 61 total, 0 merged NFA Contexts: 3 peak, 41 total, 0 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched NFA: 19 absorbed (len 2/2/2.0), 20 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Group with bounded quantifier CREATE VIEW rpr_ev_grp_bounded AS @@ -3397,13 +3473,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){2,4} + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 51 total, 0 merged NFA Contexts: 3 peak, 41 total, 5 pruned NFA: 5 matched (len 8/8/8.0), 0 mismatched NFA: 0 absorbed, 30 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Nested groups CREATE VIEW rpr_ev_grp_nested AS @@ -3436,13 +3513,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b'){2}')+" + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 4 peak, 61 total, 15 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched NFA: 14 absorbed (len 4/4/4.0), 30 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Deep nesting (3+ levels) CREATE VIEW rpr_ev_grp_deep AS @@ -3473,13 +3551,14 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 243 total, 0 merged NFA Contexts: 2 peak, 41 total, 0 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched NFA: 0 absorbed, 39 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) +(10 rows) -- Bounded quantifier on alternation CREATE VIEW rpr_ev_grp_bounded_alt AS @@ -3510,13 +3589,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2,3} c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 8 peak, 320 total, 0 merged NFA Contexts: 3 peak, 61 total, 2 pruned NFA: 19 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 38 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Nested groups with quantifiers CREATE VIEW rpr_ev_grp_nested_quant AS @@ -3547,13 +3627,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b')+" c)* + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 178 total, 0 merged NFA Contexts: 4 peak, 61 total, 20 pruned NFA: 3 matched (len 0/57/19.0), 0 mismatched NFA: 0 absorbed, 37 skipped (len 1/3/2.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- Partial nested quantification CREATE VIEW rpr_ev_grp_partial_quant AS @@ -3584,13 +3665,14 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a (b c)+)* + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 160 total, 0 merged NFA Contexts: 4 peak, 61 total, 20 pruned NFA: 3 matched (len 0/57/19.0), 0 mismatched NFA: 0 absorbed, 37 skipped (len 1/3/2.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Window Function Combinations @@ -3626,13 +3708,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- first_value with pattern CREATE VIEW rpr_ev_wfn_first_value AS @@ -3665,13 +3748,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- last_value with pattern CREATE VIEW rpr_ev_wfn_last_value AS @@ -3704,13 +3788,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- Multiple window functions CREATE VIEW rpr_ev_wfn_multi AS @@ -3749,13 +3834,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- DEFINE Expression Variations @@ -3795,13 +3881,14 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 78 total, 0 merged NFA Contexts: 2 peak, 51 total, 6 pruned NFA: 17 matched (len 2/3/2.6), 0 mismatched NFA: 10 absorbed (len 1/1/1.0), 17 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(10 rows) -- Using PREV function CREATE VIEW rpr_ev_def_prev AS @@ -3840,12 +3927,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: s u+ d+ + Nav Mark Lookback: 1 Storage: Memory Maximum Storage: NkB NFA States: 60 peak, 466 total, 0 merged NFA Contexts: 31 peak, 31 total, 1 pruned NFA: 0 matched, 29 mismatched (len 2/30/16.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(8 rows) +(9 rows) -- Using 1-arg PREV (implicit offset 1) CREATE VIEW rpr_ev_nav_prev1 AS @@ -3964,13 +4052,14 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series v (actual rows=30.00 loops=1) -(9 rows) +(10 rows) -- ============================================================ -- Large Scale Statistics Verification @@ -4006,13 +4095,14 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b c + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 851 total, 0 merged NFA Contexts: 3 peak, 501 total, 101 pruned NFA: 50 matched (len 8/9/9.0), 0 mismatched NFA: 299 absorbed (len 1/1/1.0), 50 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(9 rows) +(10 rows) -- High match count scenario CREATE VIEW rpr_ev_scale_high_match AS @@ -4045,13 +4135,14 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 501 total, 0 merged NFA Contexts: 2 peak, 501 total, 0 pruned NFA: 250 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 250 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(9 rows) +(10 rows) -- High skip count scenario CREATE VIEW rpr_ev_scale_high_skip AS @@ -4094,13 +4185,14 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e + Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 501 total, 0 merged NFA Contexts: 3 peak, 501 total, 490 pruned NFA: 5 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(9 rows) +(10 rows) -- -- Planner optimization: optimize_window_clauses must not alter RPR frame @@ -4149,10 +4241,11 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_with_rpr; -> WindowAgg Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: s.v -> Function Scan on generate_series s -(7 rows) +(8 rows) -- -- Planner optimization: non-RPR and RPR windows that share the same base frame @@ -4178,12 +4271,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; -> WindowAgg Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" + Nav Mark Lookback: 0 -> 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) +(10 rows) -- -- Planner optimization: find_window_run_conditions must not push down @@ -4242,8 +4336,133 @@ SELECT * FROM ( -> WindowAgg Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: s.v -> Function Scan on generate_series s -(8 rows) +(9 rows) + +-- ============================================================ +-- Nav Mark Lookback Tests +-- Verifies planner-computed navigation offset for tuplestore trim. +-- Lookback: how far back from currentpos (PREV/LAST). +-- ============================================================ +-- Prepare statement for host variable offset test below +PREPARE rpr_nav_offset_prep(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, $1) +); +-- No navigation function: offset 0 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 0 + -> Function Scan on generate_series s +(5 rows) + +-- NEXT only: no backward navigation, offset 0 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v < NEXT(v) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 0 + -> Function Scan on generate_series s +(5 rows) + +-- PREV(v): implicit offset 1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 1 + -> Function Scan on generate_series s +(5 rows) + +-- PREV(v, 3): explicit constant offset 3 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, 3) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 3 + -> Function Scan on generate_series s +(5 rows) + +-- Two PREV with different offsets: max(1, 5) = 5 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, 1) < v AND PREV(v, 5) < v +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 5 + -> Function Scan on generate_series s +(5 rows) + +-- Host variable offset: custom plan resolves $1=2 to constant 2 +EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 2 + -> Function Scan on generate_series s +(5 rows) + +-- Force generic plan: offset becomes "runtime" (Param node) +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: runtime + -> Function Scan on generate_series s +(5 rows) +RESET plan_cache_mode; +DEALLOCATE rpr_nav_offset_prep; diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index a21ac5a8588..20c9fe89cec 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -66,10 +66,11 @@ WINDOW w AS (ORDER BY id WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: id -> Seq Scan on rpr_integ -(6 rows) +(7 rows) -- ============================================================ -- A2. Run condition pushdown bypass @@ -112,10 +113,11 @@ SELECT * FROM ( -> WindowAgg Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: rpr_integ.id -> Seq Scan on rpr_integ -(8 rows) +(9 rows) -- Verify results are correct SELECT * FROM ( @@ -160,10 +162,11 @@ WINDOW -> WindowAgg Window: w_rpr AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: id -> Seq Scan on rpr_integ -(8 rows) +(9 rows) -- Verify both produce different results SELECT @@ -218,13 +221,15 @@ WINDOW WindowAgg Window: w2 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> WindowAgg Window: w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: id -> Seq Scan on rpr_integ -(9 rows) +(11 rows) -- Verify results differ SELECT @@ -383,10 +388,11 @@ WHERE cnt > 0; -> WindowAgg Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: rpr_integ.id -> Seq Scan on rpr_integ -(8 rows) +(9 rows) -- ============================================================ -- A9. DEFINE expression non-propagation @@ -415,12 +421,13 @@ WINDOW Output: id, val, count(*) OVER w_rpr Window: w_rpr AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Output: id, val Sort Key: rpr_integ.id -> Seq Scan on public.rpr_integ Output: id, val -(12 rows) +(13 rows) -- ============================================================ -- A10. RPR + LIMIT @@ -712,12 +719,13 @@ WINDOW w AS (ORDER BY id WindowAgg Window: w AS (ORDER BY rpr_part.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Sort Sort Key: rpr_part.id -> Append -> Seq Scan on rpr_part_1 -> Seq Scan on rpr_part_2 -(8 rows) +(9 rows) DROP TABLE rpr_part; -- ============================================================ @@ -798,8 +806,9 @@ WINDOW w AS (ORDER BY id WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ + Nav Mark Lookback: 1 -> Index Scan using rpr_integ_id_idx on rpr_integ -(4 rows) +(5 rows) SELECT id, val, count(*) OVER w AS cnt FROM rpr_integ diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index e339edd7e91..5082cc2b5de 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -36,6 +36,7 @@ -- Window Function Combinations -- DEFINE Expression Variations -- Large Scale Statistics Verification +-- Nav Mark Lookback (tuplestore trim) -- ============================================================ -- Filter function to normalize platform-dependent memory values (not NFA statistics). @@ -2476,3 +2477,73 @@ SELECT * FROM ( ) ) t WHERE cnt > 0; +-- ============================================================ +-- Nav Mark Lookback Tests +-- Verifies planner-computed navigation offset for tuplestore trim. +-- Lookback: how far back from currentpos (PREV/LAST). +-- ============================================================ + +-- Prepare statement for host variable offset test below +PREPARE rpr_nav_offset_prep(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, $1) +); + +-- No navigation function: offset 0 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > 0 +); + +-- NEXT only: no backward navigation, offset 0 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v < NEXT(v) +); + +-- PREV(v): implicit offset 1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v) +); + +-- PREV(v, 3): explicit constant offset 3 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, 3) +); + +-- Two PREV with different offsets: max(1, 5) = 5 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, 1) < v AND PREV(v, 5) < v +); + +-- Host variable offset: custom plan resolves $1=2 to constant 2 +EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); + +-- Force generic plan: offset becomes "runtime" (Param node) +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); +RESET plan_cache_mode; +DEALLOCATE rpr_nav_offset_prep; + -- 2.50.1 (Apple Git-155)