From 6b9b59bc8f229b12d4268859fb8ea049c290e682 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 10 Aug 2026 16:29:49 +0900 Subject: [PATCH] Use # and ~ for the absorption markers and document EXPLAIN's RPR output EXPLAIN annotates the Pattern: line with a marker on each element that takes part in absorption: one on the comparison point, one on the absorbable region. Those markers were the double and single quote, and the double quote is also what quote_identifier() wraps a pattern variable in when the name needs quoting: a variable named "select" under a greedy unbounded quantifier printed as "select"+", where the marker reads as the start of another quoted name rather than as an annotation. Use the number sign and the tilde instead: neither can appear in a bare pattern variable name, so a name containing one is always double-quoted and the marker always lands outside the quotes. The expected output had no quoted name carrying a marker at all, so add the two cases that show it: a keyword name taking the comparison point, and a name quoted for its space taking the branch marker. One comment updated for the new markers also had a stale quantifier, describing (A{2} B{3})+ as printing {2,}; it now matches what EXPLAIN emits. While there, complete what perform.sgml says about the plan. Its marker description was wrong twice over: it presented the markers as opportunities the planner might still take, when they record an analysis already made, and it tied the tilde to alternation branches, though (a~ b~)+# carries two of them with no alternation in the pattern. The Nav Mark lines appear in the example plan with nothing said about them, and neither the counters ANALYZE adds nor the absence of AFTER MATCH SKIP and INITIAL from the plan was mentioned at all. The counters are described as unstable rather than listed one by one, since they exist to diagnose the implementation. --- doc/src/sgml/perform.sgml | 62 +++++-- src/backend/commands/explain.c | 13 +- src/backend/optimizer/plan/rpr.c | 3 + src/test/regress/expected/rpr_base.out | 154 ++++++++++------- src/test/regress/expected/rpr_explain.out | 156 +++++++++--------- src/test/regress/expected/rpr_integration.out | 10 +- src/test/regress/sql/rpr_base.sql | 38 +++-- 7 files changed, 266 insertions(+), 170 deletions(-) diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 01a83ab105d..c285542c726 100644 --- a/doc/src/sgml/perform.sgml +++ b/doc/src/sgml/perform.sgml @@ -703,14 +703,17 @@ FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer); When examining query plans for Row Pattern Recognition with - EXPLAIN, the pattern output may include special - markers that indicate optimization opportunities. A double quote - " marks where pattern absorption can occur, - and a single quote ' marks absorbable elements - within a branch. For example, using the stock + EXPLAIN, the Pattern line may + carry absorption markers. They report an analysis the planner has + already made, not an opportunity left open. A number sign + # marks the comparison point of an absorption, which + is always an element carrying an unbounded quantifier, and a tilde + ~ marks an element that lies within the absorbable + region. Neither marker requires an alternation to be present. + For example, using the stock table from , a query that looks for repeated up-then-down movements reports its pattern as - (up' down')+": + (up~ down~)+#: EXPLAIN (COSTS OFF) @@ -731,18 +734,53 @@ WINDOW w AS ( -------------------------------------------------------------------&zwsp;------------------------------------ WindowAgg Window: w AS (PARTITION BY company ORDER BY tdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (up' down')+" + Pattern: (up~ down~)+# Nav Mark Lookback: 1 -> Sort Sort Key: company, tdate -> Seq Scan on stock - Here the single quotes mark up and - down as absorbable within the group, while the - trailing double quote marks the repeated group itself as absorbable. - These markers are primarily useful for understanding internal - optimization behavior. + Here the tildes mark up and + down as lying within the absorbable region, while + the trailing number sign marks the repeated group as the comparison + point. + + + + The Nav Mark Lookback line reports the row-retention + bound the executor computed for the backward navigations used in + DEFINE (PREV and + LAST); a Nav Mark Lookahead line + appears when DEFINE navigates forward + (FIRST). Each reports a row count when the + navigation offsets are constant; the lookahead is measured from the + match start, so a compound navigation that reaches back past it, such + as PREV(FIRST(val, 1), 2), reports a negative one. + Otherwise Nav Mark Lookback reports + runtime for an offset that is not known until + execution, or retain all when no bound could be + computed and every row of the frame has to be kept; and + Nav Mark Lookahead reports runtime + or infinite. + + + + Under ANALYZE, further lines report counters for the + matcher's internal states and contexts. Those are diagnostics for the + implementation rather than a stable interface; their names and contents + may change. + + + + Neither the AFTER MATCH SKIP nor the + INITIAL subclause is printed. Nothing is lost for + INITIAL, which is the only mode currently supported, + but AFTER MATCH SKIP still shows through: absorption + is analyzed only under SKIP PAST LAST ROW, so the + same pattern prints with the # and + ~ markers in that mode and without them in the + others. diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 0c82a7d57ce..b11dc493fdf 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -2935,14 +2935,19 @@ append_rpr_quantifier(StringInfo buf, RPRPatternElement *elem) appendStringInfoChar(buf, '?'); } - /* Append absorption markers: " for comparison point, ' for branch only */ + /* + * Append absorption markers: # for the comparison point, ~ for the + * absorbable region. Neither character can occur in a bare pattern + * variable name, and a name that does contain one is always double-quoted + * by quote_identifier(), so a marker is never read as part of the name. + */ if (RPRElemIsAbsorbable(elem)) { Assert(elem->max == RPR_QUANTITY_INF); - appendStringInfoChar(buf, '"'); + appendStringInfoChar(buf, '#'); } else if (RPRElemIsAbsorbableBranch(elem)) - appendStringInfoChar(buf, '\''); + appendStringInfoChar(buf, '~'); } /* @@ -2968,7 +2973,7 @@ append_rpr_quantifier(StringInfo buf, RPRPatternElement *elem) * EXPLAIN parenthesizes every ALT on its own, so a top-level "A | B" deparses * as "(a | b)". This self-consistent EXPLAIN form is the correctness oracle * here; pg_get_viewdef differs, as its parens come only from an enclosing - * GROUP. Absorption markers (' ") are orthogonal and handled by + * GROUP. Absorption markers (# ~) are orthogonal and handled by * append_rpr_quantifier(). * * Two compiler invariants hold throughout: {1,1} groups are unwrapped before diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c index 26d0bea6ad7..5a0017d90e2 100644 --- a/src/backend/optimizer/plan/rpr.c +++ b/src/backend/optimizer/plan/rpr.c @@ -22,6 +22,9 @@ * - RPR_ELEM_ABSORBABLE: marks WHERE to compare (comparison point) * - RPR_ELEM_ABSORBABLE_BRANCH: marks the absorbable region * + * EXPLAIN shows both on the Pattern: line, # for the comparison point and + * ~ for the region. + * * See computeAbsorbability() and the detailed comments before * isUnboundedStart() for the full design explanation. * diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index dce0e9447e2..7da38b2384a 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -4852,7 +4852,7 @@ 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+" + Pattern: a+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4868,7 +4868,7 @@ 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,}" + Pattern: a{2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4886,7 +4886,7 @@ 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{2147483646,}" + Pattern: a{2147483646,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4916,7 +4916,7 @@ 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,}" + Pattern: (a~ b~){2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4932,7 +4932,7 @@ 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,}" + Pattern: (a~ b~){3,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4950,7 +4950,7 @@ 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'){2147483646,}" + Pattern: (a~ b~){2147483646,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4965,7 +4965,7 @@ 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,}" + Pattern: (a~ b~){2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -4980,7 +4980,7 @@ 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,}" + Pattern: (a~ b~){3,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5160,7 +5160,7 @@ 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,}" + Pattern: (a{2}~){2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5176,7 +5176,7 @@ 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,}" + Pattern: a{2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5192,7 +5192,7 @@ 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+" + Pattern: a+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5257,7 +5257,7 @@ 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*" + Pattern: a*# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5272,7 +5272,7 @@ 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*" + Pattern: a*# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5287,7 +5287,7 @@ 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+" + Pattern: a+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5303,7 +5303,7 @@ 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,}" + Pattern: a{3,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5318,7 +5318,7 @@ 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,}" + Pattern: a{6,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5334,7 +5334,7 @@ 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,}" + Pattern: a{2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5460,7 +5460,7 @@ 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,}" + Pattern: a{2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5508,7 +5508,7 @@ 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)+ + Pattern: (a~ b~)+# (a b c)+ -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5523,7 +5523,7 @@ 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,}" + Pattern: (a~ b~){2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5538,7 +5538,7 @@ 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,}" + Pattern: (a~ b~){2,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5554,7 +5554,7 @@ 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 + Pattern: (a~ b~){3,}# c -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5621,7 +5621,7 @@ 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,} + Pattern: (a+# b* c?){2,} -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5770,7 +5770,7 @@ 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*" + Pattern: a*# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5873,7 +5873,7 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (6 rows) -- Reluctant optimization bypass: absorption flags --- A+? with SKIP PAST LAST ROW - no absorption markers (greedy A+ gets a+") +-- A+? with SKIP PAST LAST ROW - no absorption markers (greedy A+ gets a+#) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -5912,7 +5912,7 @@ 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+" + Pattern: a+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5927,7 +5927,7 @@ 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,}" + Pattern: a{7,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5943,7 +5943,7 @@ 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,}" + Pattern: (a~ b~){5,}# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -5975,7 +5975,7 @@ 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+" + Pattern: a+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -6026,7 +6026,7 @@ 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) + Pattern: ((a+# b | c) d | a b c) -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -6036,9 +6036,9 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- Absorption Flag Display Tests -- ============================================================ -- Tests absorption marker display in EXPLAIN output --- Markers: ' = branch element, " = comparison point +-- Markers: ~ = branch element, # = comparison point -- Files: explain.c (append_rpr_quantifier, deparse_rpr_pattern) --- Simple VAR: A+ -> a+" (comparison point) +-- Simple VAR: A+ -> a+# (comparison point) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6047,13 +6047,13 @@ 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+" + Pattern: a+# -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- GROUP unbounded: (A B)+ -> (a' b')+" (branch + comparison) +-- GROUP unbounded: (A B)+ -> (a~ b~)+# (branch + comparison) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6062,13 +6062,13 @@ 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')+" + Pattern: (a~ b~)+# -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- ALT both absorbable: A+ | B+ -> (a+" | b+") +-- ALT both absorbable: A+ | B+ -> (a+# | b+#) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6077,13 +6077,13 @@ 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+") + Pattern: (a+# | b+#) -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- ALT one absorbable: A+ | B -> (a+" | b) +-- ALT one absorbable: A+ | B -> (a+# | b) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6092,13 +6092,13 @@ 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) + Pattern: (a+# | b) -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- Sequence with absorbable start: A+ B -> a+" b +-- Sequence with absorbable start: A+ B -> a+# b EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6107,7 +6107,7 @@ 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 + Pattern: a+# b -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -6123,13 +6123,13 @@ 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) + Pattern: ((a+# b | c) d | a b c) -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- ALT branch tail not over-marked: A | (B C)+ (D E)+ -> (a | (b' c')+" (d e)+) +-- ALT branch tail not over-marked: A | (B C)+ (D E)+ -> (a | (b~ c~)+# (d e)+) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6139,13 +6139,13 @@ 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 e)+) + Pattern: (a | (b~ c~)+# (d e)+) -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- Nested unbounded: (A+ | B)+ -> (a+" | b)+ (first iteration absorbable) +-- Nested unbounded: (A+ | B)+ -> (a+# | b)+ (first iteration absorbable) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6155,13 +6155,13 @@ 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)+ + Pattern: (a+# | b)+ -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- ALT inside unbounded GROUP: (A+ B | A B)* -> (a+" b | a b)* (first iteration absorbable) +-- ALT inside unbounded GROUP: (A+ B | A B)* -> (a+# b | a b)* (first iteration absorbable) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -6171,13 +6171,13 @@ 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)* + Pattern: (a+# b | a b)* -> Sort Sort Key: id -> Seq Scan on rpr_plan (6 rows) --- Fixed-length group absorbable: (A{2} B{3})+ -> (a{2}' b{3}'){2,}" +-- Fixed-length group absorbable: (A{2} B{3})+ -> (a{2}~ b{3}~)+# -- All children have min == max, equivalent to unrolling to {1,1} EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan @@ -6188,7 +6188,7 @@ 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}')+" + Pattern: (a{2}~ b{3}~)+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -6204,7 +6204,7 @@ 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')+" + Pattern: (a~ (b~ c~){2}~ d~)+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -6220,7 +6220,7 @@ 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}')+" + Pattern: ((a{2}~ b{3}~){2}~)+# -> Sort Sort Key: id -> Seq Scan on rpr_plan @@ -6318,6 +6318,40 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING -> Seq Scan on rpr_plan (6 rows) +-- A marker on a quoted variable name lands outside the closing quote: a +-- keyword name takes the comparison point, "select"+ -> "select"+# +EXPLAIN (COSTS OFF) +SELECT COUNT(*) OVER w FROM rpr_plan +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW PATTERN ("select"+ B) + DEFINE "select" AS val <= 50, B AS val > 50); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: "select"+# b + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(6 rows) + +-- The same for the branch marker, on a name quoted for its space: +-- ("My Var" A)+ -> ("My Var"~ a~)+# +EXPLAIN (COSTS OFF) +SELECT COUNT(*) OVER w FROM rpr_plan +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW PATTERN (("My Var" A)+ B) + DEFINE "My Var" AS val <= 25, A AS val <= 50, B AS val > 50); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: ("My Var"~ a~)+# b + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(6 rows) + -- Reluctant {1}? quantifier: min == max, so the plan normalizes it away EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM rpr_plan @@ -6961,7 +6995,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a{2000000000,}"){2000000000,} + Pattern: (a{2000000000,}#){2000000000,} -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7004,7 +7038,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{1073741824,}" a{1073741823,} + Pattern: a{1073741824,}# a{1073741823,} -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7026,7 +7060,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{1073741825,}" a{1073741823,} + Pattern: a{1073741825,}# a{1073741823,} -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7102,7 +7136,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{2,}" + Pattern: a{2,}# -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7121,7 +7155,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){1073741824,}" (a b){1073741823,} + Pattern: (a~ b~){1073741824,}# (a b){1073741823,} -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7199,7 +7233,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){2147483646,}" + Pattern: (a~ b~){2147483646,}# -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7254,7 +7288,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){2147483646,}" a b + Pattern: (a~ b~){2147483646,}# a b -> Sort Sort Key: id -> Seq Scan on rpr_fallback @@ -7272,7 +7306,7 @@ WINDOW w AS ( ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){2147483646,}" + Pattern: (a~ b~){2147483646,}# -> Sort Sort Key: id -> Seq Scan on rpr_fallback diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 60b121aa245..00056912e6c 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -334,7 +334,7 @@ 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)+) + Pattern: (a | (b~ c~)+# (d e)+) Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 103 total, 0 merged NFA Contexts: 3 peak, 31 total, 15 pruned @@ -396,7 +396,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 76 total, 0 merged NFA Contexts: 3 peak, 51 total, 25 pruned @@ -483,7 +483,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b* c+ + Pattern: a+# b* c+ Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 235 total, 0 merged NFA Contexts: 3 peak, 101 total, 34 pruned @@ -522,7 +522,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b')+" + Pattern: (a~ b~)+# Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 91 total, 0 merged NFA Contexts: 3 peak, 61 total, 0 pruned @@ -709,7 +709,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){4,}" + Pattern: (a~ b~){4,}# Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 58 total, 0 merged NFA Contexts: 3 peak, 41 total, 3 pruned @@ -734,7 +734,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){4,}" + Pattern: (a~ b~){4,}# Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 58 total, 0 merged NFA Contexts: 3 peak, 41 total, 3 pruned @@ -784,7 +784,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b'){5,}" + Pattern: (a~ b~){5,}# Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 57 total, 0 merged NFA Contexts: 3 peak, 41 total, 4 pruned @@ -941,7 +941,7 @@ WINDOW w AS ( --------------------------------------------------------------------- WindowAgg (actual rows=6.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a{2,}")* + Pattern: (a{2,}#)* Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 18 total, 0 merged NFA Contexts: 3 peak, 7 total, 0 pruned @@ -1024,7 +1024,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned @@ -1064,7 +1064,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=10.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 21 total, 0 merged NFA Contexts: 2 peak, 11 total, 0 pruned @@ -1104,7 +1104,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=10.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a*" + Pattern: a*# Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 32 total, 0 merged NFA Contexts: 2 peak, 11 total, 0 pruned @@ -1221,7 +1221,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b')+" c + Pattern: (a~ b~)+# c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 134 total, 0 merged NFA Contexts: 3 peak, 101 total, 34 pruned @@ -1262,7 +1262,7 @@ 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 + Pattern: (a~ b{2}~)+# c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 4 peak, 71 total, 40 pruned @@ -1306,7 +1306,7 @@ 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 + Pattern: (a~ (b~ c~){2}~ d~)+# e Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 76 total, 0 merged NFA Contexts: 4 peak, 66 total, 50 pruned @@ -1358,7 +1358,7 @@ 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 + Pattern: (a~ ((b~ c{3}~){2}~ d~){2}~ e~)+# f Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 87 total, 0 merged NFA Contexts: 4 peak, 83 total, 76 pruned @@ -1403,7 +1403,7 @@ 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}')+" + Pattern: ((a~ (b~ c~){2}~){2}~)+# Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 47 total, 0 merged NFA Contexts: 5 peak, 43 total, 30 pruned @@ -1487,7 +1487,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged @@ -1563,7 +1563,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" b)+ c + Pattern: (a+# b)+ c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 96 total, 0 merged NFA Contexts: 3 peak, 51 total, 5 pruned @@ -1646,7 +1646,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" b){2} + Pattern: (a+# b){2} Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 111 total, 0 merged NFA Contexts: 3 peak, 51 total, 5 pruned @@ -1684,7 +1684,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" b){2} + Pattern: (a+# b){2} Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 111 total, 0 merged NFA Contexts: 3 peak, 51 total, 5 pruned @@ -1733,7 +1733,7 @@ WINDOW w AS ( ----------------------------------------------------------------------------------------------- WindowAgg (actual rows=6.00 loops=1) Window: w AS (ORDER BY "*VALUES*".column1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" b | c) + Pattern: (a+# b | c) Storage: Memory Maximum Storage: NkB NFA States: 11 peak, 34 total, 0 merged NFA Contexts: 4 peak, 7 total, 0 pruned @@ -1787,7 +1787,7 @@ WINDOW w AS ( ----------------------------------------------------------------------------------------------- WindowAgg (actual rows=9.00 loops=1) Window: w AS (ORDER BY "*VALUES*".column1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" c | b+") + Pattern: (a+# c | b+#) Storage: Memory Maximum Storage: NkB NFA States: 10 peak, 49 total, 0 merged NFA Contexts: 3 peak, 10 total, 0 pruned @@ -1875,7 +1875,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 191 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned @@ -1914,7 +1914,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=200.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 396 total, 0 merged NFA Contexts: 2 peak, 201 total, 4 pruned @@ -1957,7 +1957,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 171 total, 0 merged NFA Contexts: 3 peak, 101 total, 25 pruned @@ -2014,7 +2014,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b+ c + Pattern: a+# b+ c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 151 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned @@ -2077,7 +2077,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b+ c + Pattern: a+# b+ c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 115 total, 0 merged NFA Contexts: 3 peak, 61 total, 15 pruned @@ -2127,7 +2127,7 @@ WINDOW w AS ( "Actual Loops": 1, + "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ - "Pattern": "a+\" b+", + + "Pattern": "a+# b+", + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 3, + @@ -2204,7 +2204,7 @@ WINDOW w AS ( "Actual Loops": 1, + "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ - "Pattern": "a+\" b", + + "Pattern": "a+# b", + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 3, + @@ -2527,7 +2527,7 @@ WINDOW w AS ( 1 + false + w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ - a+" b+ + + a+# b+ + 1 + Memory + 0 + @@ -2694,7 +2694,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 + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 165 total, 0 merged NFA Contexts: 2 peak, 93 total, 0 pruned @@ -2750,7 +2750,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 + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 77 total, 0 merged NFA Contexts: 2 peak, 52 total, 21 pruned @@ -3104,7 +3104,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 101 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned @@ -3146,7 +3146,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b' c')+" + Pattern: (a~ b~ c~)+# Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 81 total, 0 merged NFA Contexts: 4 peak, 61 total, 20 pruned @@ -3348,7 +3348,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: d+" u+ + Pattern: d+# u+ Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 58 total, 0 merged NFA Contexts: 3 peak, 31 total, 3 pruned @@ -3387,7 +3387,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: u+" s* d+ + Pattern: u+# s* d+ Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 3 peak, 31 total, 1 pruned @@ -3426,7 +3426,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{3,}" + Pattern: a{3,}# Nav Mark Lookback: 1 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 99 total, 0 merged @@ -3508,7 +3508,7 @@ WINDOW w AS ( ------------------------------------------------------------------------ WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 1991 total, 0 merged NFA Contexts: 2 peak, 1001 total, 0 pruned @@ -3591,7 +3591,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned @@ -3630,7 +3630,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned @@ -3672,7 +3672,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 71 total, 0 merged NFA Contexts: 3 peak, 41 total, 10 pruned @@ -3711,7 +3711,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a*" b + Pattern: a*# b Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 102 total, 0 merged NFA Contexts: 2 peak, 41 total, 10 pruned @@ -3867,7 +3867,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{3,}" b + Pattern: a{3,}# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 86 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned @@ -3910,7 +3910,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 37 total, 0 merged NFA Contexts: 2 peak, 21 total, 0 pruned @@ -3949,7 +3949,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b c + Pattern: a+# b c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 52 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned @@ -4258,7 +4258,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" | b+") c + Pattern: (a+# | b+#) c Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 223 total, 0 merged NFA Contexts: 3 peak, 61 total, 1 pruned @@ -4407,7 +4407,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: ((a' b')+" | c) + Pattern: ((a~ b~)+# | c) Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 7 pruned @@ -4445,7 +4445,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (c | (a' b')+") + Pattern: (c | (a~ b~)+#) Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 7 pruned @@ -4483,7 +4483,7 @@ 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) + Pattern: (c | (a~ b~)+# | d) Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 88 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned @@ -4521,7 +4521,7 @@ 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) + Pattern: ((a~ b~)+# | c | d) Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 88 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned @@ -4596,7 +4596,7 @@ 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')+") + Pattern: ((a~ b~)+# | (c~ d~)+#) Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 72 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned @@ -4634,7 +4634,7 @@ 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 + Pattern: ((a~ b~)+# | c) d Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 6 pruned @@ -4672,7 +4672,7 @@ 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 + Pattern: (c | (a~ b~)+#) d Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 6 pruned @@ -4785,7 +4785,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a+" | c) + Pattern: (a+# | c) Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 68 total, 0 merged NFA Contexts: 3 peak, 21 total, 10 pruned @@ -4822,7 +4822,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (c | d | (a' b')+") + Pattern: (c | d | (a~ b~)+#) Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 88 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned @@ -4897,7 +4897,7 @@ 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) + Pattern: (c | (a~ b~)+# d) Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 11 pruned @@ -5337,7 +5337,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b')+" + Pattern: (a~ b~)+# Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 61 total, 0 merged NFA Contexts: 3 peak, 41 total, 0 pruned @@ -5415,7 +5415,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: ((a' b'){2}')+" + Pattern: ((a~ b~){2}~)+# Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 4 peak, 61 total, 15 pruned @@ -5526,7 +5526,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: ((a' b')+" c)* + Pattern: ((a~ b~)+# c)* Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 178 total, 0 merged NFA Contexts: 4 peak, 61 total, 0 pruned @@ -5605,7 +5605,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned @@ -5644,7 +5644,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned @@ -5683,7 +5683,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned @@ -5728,7 +5728,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned @@ -5774,7 +5774,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 78 total, 0 merged NFA Contexts: 2 peak, 51 total, 6 pruned @@ -5944,7 +5944,7 @@ WINDOW w AS ( ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b + Pattern: a+# b Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned @@ -5986,7 +5986,7 @@ WINDOW w AS ( ----------------------------------------------------------------------- WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b c + Pattern: a+# b c Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 851 total, 0 merged NFA Contexts: 3 peak, 501 total, 101 pruned @@ -6159,7 +6159,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; Subquery Scan on rpr_ev_opt_mixed -> WindowAgg Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# -> WindowAgg Window: w_normal AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING) -> Sort @@ -6259,7 +6259,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# -> Function Scan on generate_series s (4 rows) @@ -6275,7 +6275,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# -> Function Scan on generate_series s (4 rows) @@ -6291,7 +6291,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 1 -> Function Scan on generate_series s (5 rows) @@ -6308,7 +6308,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 3 -> Function Scan on generate_series s (5 rows) @@ -6326,7 +6326,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 2 -> Function Scan on generate_series s (5 rows) @@ -6343,7 +6343,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 5 -> Function Scan on generate_series s (5 rows) @@ -6354,7 +6354,7 @@ EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 2 -> Function Scan on generate_series s (5 rows) @@ -6366,7 +6366,7 @@ EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: runtime -> Function Scan on generate_series s (5 rows) @@ -6388,7 +6388,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: runtime -> Function Scan on generate_series s (5 rows) @@ -6528,7 +6528,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 1 -> Function Scan on generate_series s (5 rows) @@ -6579,7 +6579,7 @@ WINDOW w AS ( ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 2 -> Function Scan on generate_series s (5 rows) diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 6dccea30ee2..e7639273a21 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -342,7 +342,7 @@ SELECT count(*) FROM ( Aggregate -> WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 1 -> Seq Scan on rpr_integ (6 rows) @@ -376,7 +376,7 @@ SELECT count(*), sum(c) FROM ( Aggregate -> WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: 1 -> Seq Scan on rpr_integ (6 rows) @@ -409,7 +409,7 @@ SELECT count(*), sum(c) FROM ( Aggregate -> WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# -> Seq Scan on rpr_integ (5 rows) @@ -1629,7 +1629,7 @@ SELECT cnt FROM ( -> WindowAgg Output: rpr_over1.a, NULL::integer, count(*) OVER w Window: w AS (ORDER BY rpr_over1.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: x+" + Pattern: x+# -> Sort Output: rpr_over1.a Sort Key: rpr_over1.a @@ -1670,7 +1670,7 @@ GROUP BY g.n ORDER BY g.n; -> Values Scan on "*VALUES*" -> WindowAgg Window: w AS (ORDER BY rpr_srf.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" + Pattern: a+# Nav Mark Lookback: runtime -> Sort Sort Key: rpr_srf.v diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 49a9e41f117..1ecd329dbfd 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -3580,7 +3580,7 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN ((A | (B | C))+?) DEFINE A AS val <= 30, B AS val <= 60, C AS val > 60); -- Reluctant optimization bypass: absorption flags --- A+? with SKIP PAST LAST ROW - no absorption markers (greedy A+ gets a+") +-- A+? with SKIP PAST LAST ROW - no absorption markers (greedy A+ gets a+#) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -3653,34 +3653,34 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- Absorption Flag Display Tests -- ============================================================ -- Tests absorption marker display in EXPLAIN output --- Markers: ' = branch element, " = comparison point +-- Markers: ~ = branch element, # = comparison point -- Files: explain.c (append_rpr_quantifier, deparse_rpr_pattern) --- Simple VAR: A+ -> a+" (comparison point) +-- Simple VAR: A+ -> a+# (comparison point) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS val > 0); --- GROUP unbounded: (A B)+ -> (a' b')+" (branch + comparison) +-- GROUP unbounded: (A B)+ -> (a~ b~)+# (branch + comparison) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+) DEFINE A AS val <= 50, B AS val > 50); --- ALT both absorbable: A+ | B+ -> (a+" | b+") +-- ALT both absorbable: A+ | B+ -> (a+# | b+#) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ | B+) DEFINE A AS val <= 50, B AS val > 50); --- ALT one absorbable: A+ | B -> (a+" | b) +-- ALT one absorbable: A+ | B -> (a+# | b) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ | B) DEFINE A AS val <= 50, B AS val > 50); --- Sequence with absorbable start: A+ B -> a+" b +-- Sequence with absorbable start: A+ B -> a+# b EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -3693,28 +3693,28 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (((A+ B) | C) D | A B C) DEFINE A AS val <= 30, B AS val <= 60, C AS val <= 80, D AS val > 80); --- ALT branch tail not over-marked: A | (B C)+ (D E)+ -> (a | (b' c')+" (d e)+) +-- ALT branch tail not over-marked: A | (B C)+ (D E)+ -> (a | (b~ c~)+# (d e)+) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A | (B C)+ (D E)+) DEFINE A AS val <= 20, B AS val <= 40, C AS val <= 60, D AS val <= 80, E AS val > 80); --- Nested unbounded: (A+ | B)+ -> (a+" | b)+ (first iteration absorbable) +-- Nested unbounded: (A+ | B)+ -> (a+# | b)+ (first iteration absorbable) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A+ | B)+) DEFINE A AS val <= 50, B AS val > 50); --- ALT inside unbounded GROUP: (A+ B | A B)* -> (a+" b | a b)* (first iteration absorbable) +-- ALT inside unbounded GROUP: (A+ B | A B)* -> (a+# b | a b)* (first iteration absorbable) EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A+ B | A B)*) DEFINE A AS val <= 50, B AS val > 50); --- Fixed-length group absorbable: (A{2} B{3})+ -> (a{2}' b{3}'){2,}" +-- Fixed-length group absorbable: (A{2} B{3})+ -> (a{2}~ b{3}~)+# -- All children have min == max, equivalent to unrolling to {1,1} EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan @@ -3774,6 +3774,22 @@ SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS val > 0); +-- A marker on a quoted variable name lands outside the closing quote: a +-- keyword name takes the comparison point, "select"+ -> "select"+# +EXPLAIN (COSTS OFF) +SELECT COUNT(*) OVER w FROM rpr_plan +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW PATTERN ("select"+ B) + DEFINE "select" AS val <= 50, B AS val > 50); + +-- The same for the branch marker, on a name quoted for its space: +-- ("My Var" A)+ -> ("My Var"~ a~)+# +EXPLAIN (COSTS OFF) +SELECT COUNT(*) OVER w FROM rpr_plan +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW PATTERN (("My Var" A)+ B) + DEFINE "My Var" AS val <= 25, A AS val <= 50, B AS val > 50); + -- Reluctant {1}? quantifier: min == max, so the plan normalizes it away EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM rpr_plan