From f3097f9c82b25cb5ed60caa3241e58935e797535 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 5 Feb 2026 22:31:14 +0900 Subject: [PATCH] Remove FIXME and normalize Storage values --- src/test/regress/expected/rpr_explain.out | 559 +++--- src/test/regress/expected/rpr_explain_1.out | 1803 ------------------- src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/rpr_explain.sql | 294 ++- 4 files changed, 521 insertions(+), 2137 deletions(-) delete mode 100644 src/test/regress/expected/rpr_explain_1.out diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index d8804911c93..b5ceaae53b5 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -6,6 +6,38 @@ -- - NFA Contexts: peak, total, absorbed, skipped -- - NFA: matched (len min/max/avg), mismatched (len min/max/avg) -- +-- Filter function to normalize Storage memory values only (not NFA statistics) +-- Works for text, JSON, and XML formats +create function rpr_explain_filter(text) returns setof text +language plpgsql as +$$ +declare + ln text; +begin + for ln in execute $1 + loop + -- Normalize memory size in Storage line only (platform-dependent) + -- Keep NFA statistics numbers unchanged (they are test assertions) + + -- Text format: "Storage: Memory Maximum Storage: 18kB" + if ln ~ 'Storage:.*Maximum Storage:' then + ln := regexp_replace(ln, '\m\d+kB', 'NkB', 'g'); + end if; + + -- JSON format: "Maximum Storage": 17 (number in kB units) + if ln ~ '"Maximum Storage":' then + ln := regexp_replace(ln, '"Maximum Storage": \d+', '"Maximum Storage": 0', 'g'); + end if; + + -- XML format: 17 (number in kB units) + if ln ~ '' then + ln := regexp_replace(ln, '\d+', '0', 'g'); + end if; + + return next ln; + end loop; +end; +$$; -- Setup: Create test tables CREATE TEMP TABLE nfa_test ( id serial, @@ -41,6 +73,7 @@ VALUES -- Section 1: Basic NFA Statistics Tests -- -- Test 1.1: Simple pattern - should show basic statistics +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -48,14 +81,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) - DEFINE A AS cat = 'A', B AS cat = 'B' -); - QUERY PLAN + DEFINE A AS cat = ''A'', B AS cat = ''B'' +)'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 80 pruned NFA: 20 matched (len 2/2/2.0), 0 mismatched @@ -63,6 +96,7 @@ WINDOW w AS ( (8 rows) -- Test 1.2: Pattern with no matches - 0 matched +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -70,14 +104,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (X Y Z) - DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z' -); - QUERY PLAN + DEFINE X AS cat = ''X'', Y AS cat = ''Y'', Z AS cat = ''Z'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: x y z - Storage: Memory Maximum Storage: 17kB + 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 @@ -85,6 +119,7 @@ WINDOW w AS ( (8 rows) -- Test 1.3: Pattern matching every row - high match count +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -93,13 +128,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (R) DEFINE R AS TRUE -); - QUERY PLAN +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: r - Storage: Memory Maximum Storage: 17kB + 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 @@ -110,6 +145,7 @@ WINDOW w AS ( -- Section 2: State Statistics Tests (peak, total, merged) -- -- Test 2.1: Simple quantifier pattern - A+ with short matches (no merging) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -118,13 +154,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS v % 2 = 1 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Storage: Memory Maximum Storage: 17kB + 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 @@ -132,6 +168,7 @@ WINDOW w AS ( (8 rows) -- Test 2.2: Alternation pattern - multiple state branches +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -140,15 +177,15 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B | C) (D | E)) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c d | e) - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 363 total, 0 merged NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 40 mismatched (len 2/2/2.0) @@ -156,6 +193,7 @@ WINDOW w AS ( (8 rows) -- Test 2.3: Complex pattern with high state count +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -167,13 +205,13 @@ WINDOW w AS ( A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b* c+ - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 235 total, 0 merged NFA Contexts: 3 peak, 101 total, 67 pruned NFA: 33 matched (len 3/3/3.0), 0 mismatched @@ -181,6 +219,7 @@ WINDOW w AS ( (8 rows) -- Test 2.4: Grouped pattern with quantifier - state merging +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) @@ -189,13 +228,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" - Storage: Memory Maximum Storage: 18kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 61 total, 30 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched @@ -205,6 +244,7 @@ WINDOW w AS ( -- Test 2.5: State explosion pattern - many alternations -- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -213,13 +253,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b a | b a | b a | b a | b a | b a | b a | b) - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 17 peak, 632 total, 0 merged NFA Contexts: 9 peak, 101 total, 1 pruned NFA: 12 matched (len 8/8/8.0), 3 mismatched (len 2/4/3.0) @@ -228,6 +268,7 @@ WINDOW w AS ( (9 rows) -- Test 2.6: High state merging - alternation with plus quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -236,13 +277,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B | C)+ D) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+ d - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 15 peak, 753 total, 0 merged NFA Contexts: 5 peak, 101 total, 25 pruned NFA: 25 matched (len 4/4/4.0), 0 mismatched @@ -251,6 +292,7 @@ WINDOW w AS ( (9 rows) -- Test 2.7: Nested quantifiers causing state growth +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) @@ -259,13 +301,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (((A | B)+)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 -); - QUERY PLAN +);'); + rpr_explain_filter ------------------------------------------------------------------------ WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a | b)+)+ - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 16 peak, 7334 total, 0 merged NFA Contexts: 4 peak, 1001 total, 333 pruned NFA: 334 matched (len 1/2/2.0), 0 mismatched @@ -277,6 +319,7 @@ WINDOW w AS ( -- Section 3: Context Statistics Tests (peak, total, absorbed, skipped) -- -- Test 3.1: Context absorption with unbounded quantifier at start +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -285,13 +328,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 51 total, 10 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched @@ -300,6 +343,7 @@ WINDOW w AS ( (9 rows) -- Test 3.2: No absorption - bounded quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -308,13 +352,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,4} b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 101 total, 0 merged NFA Contexts: 6 peak, 51 total, 10 pruned NFA: 10 matched (len 5/5/5.0), 10 mismatched (len 2/2/2.0) @@ -323,6 +367,7 @@ WINDOW w AS ( (9 rows) -- Test 3.3: Contexts skipped by SKIP PAST LAST ROW +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -331,13 +376,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C) DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 90 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched @@ -345,6 +390,7 @@ WINDOW w AS ( (8 rows) -- Test 3.4: High context absorption - unbounded group +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -353,13 +399,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 134 total, 0 merged NFA Contexts: 3 peak, 101 total, 67 pruned NFA: 33 matched (len 3/3/3.0), 0 mismatched @@ -370,6 +416,7 @@ WINDOW w AS ( -- Section 4: Match Length Statistics Tests -- -- Test 4.1: Fixed length matches - all same length +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -378,15 +425,15 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C D E) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 80 pruned NFA: 20 matched (len 5/5/5.0), 0 mismatched @@ -394,6 +441,7 @@ WINDOW w AS ( (8 rows) -- Test 4.2: Variable length matches - min/max/avg differ +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -402,13 +450,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 191 total, 0 merged NFA Contexts: 3 peak, 101 total, 10 pruned NFA: 10 matched (len 10/10/10.0), 0 mismatched @@ -417,6 +465,7 @@ WINDOW w AS ( (9 rows) -- Test 4.3: Very long matches +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 200) AS s(v) @@ -425,13 +474,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v <= 195, B AS v > 195 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=200.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 23kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 396 total, 0 merged NFA Contexts: 3 peak, 201 total, 5 pruned NFA: 1 matched (len 196/196/196.0), 0 mismatched @@ -440,6 +489,7 @@ WINDOW w AS ( (9 rows) -- Test 4.4: Mix of short and long matches +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -450,13 +500,13 @@ WINDOW w AS ( DEFINE A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15), B AS v % 20 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 171 total, 0 merged NFA Contexts: 3 peak, 101 total, 30 pruned NFA: 5 matched (len 5/5/5.0), 5 mismatched (len 11/11/11.0) @@ -469,28 +519,29 @@ WINDOW w AS ( -- -- Test 5.1: Pattern that causes mismatches with length > 1 -- Mismatch happens when partial match fails after processing multiple rows +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( SELECT v, - CASE WHEN v % 10 IN (1,2,3) THEN 'A' - WHEN v % 10 IN (4,5) THEN 'B' - WHEN v % 10 = 6 THEN 'C' - ELSE 'X' END AS cat + CASE WHEN v % 10 IN (1,2,3) THEN ''A'' + WHEN v % 10 IN (4,5) THEN ''B'' + WHEN v % 10 = 6 THEN ''C'' + ELSE ''X'' END AS cat FROM generate_series(1, 100) AS s(v) ) t WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B+ C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); - QUERY PLAN + DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b+ c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 151 total, 0 merged NFA Contexts: 3 peak, 101 total, 70 pruned NFA: 10 matched (len 6/6/6.0), 0 mismatched @@ -499,18 +550,19 @@ WINDOW w AS ( (9 rows) -- Test 5.2: Long partial matches that fail +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( SELECT i AS v, CASE - WHEN i <= 20 THEN 'A' - WHEN i <= 25 THEN 'B' - WHEN i = 26 THEN 'X' -- breaks the pattern - WHEN i <= 50 THEN 'A' - WHEN i <= 55 THEN 'B' - WHEN i = 56 THEN 'C' -- completes pattern - ELSE 'Y' + WHEN i <= 20 THEN ''A'' + WHEN i <= 25 THEN ''B'' + WHEN i = 26 THEN ''X'' -- breaks the pattern + WHEN i <= 50 THEN ''A'' + WHEN i <= 55 THEN ''B'' + WHEN i = 56 THEN ''C'' -- completes pattern + ELSE ''Y'' END AS cat FROM generate_series(1, 60) i ) t @@ -518,14 +570,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B+ C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); - QUERY PLAN + DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b+ c - Storage: Memory Maximum Storage: 18kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 115 total, 0 merged NFA Contexts: 3 peak, 61 total, 16 pruned NFA: 1 matched (len 30/30/30.0), 1 mismatched (len 26/26/26.0) @@ -537,6 +589,7 @@ WINDOW w AS ( -- Section 6: JSON Format Tests -- -- Test 6.1: JSON format output with all statistics +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -545,8 +598,8 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 -); - QUERY PLAN +)'); + rpr_explain_filter ---------------------------------------------------------------------------- [ + { + @@ -560,7 +613,7 @@ WINDOW w AS ( "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a+\" b+", + "Storage": "Memory", + - "Maximum Storage": 17, + + "Maximum Storage": 0, + "NFA States Peak": 3, + "NFA States Total": 85, + "NFA States Merged": 0, + @@ -595,6 +648,7 @@ WINDOW w AS ( (1 row) -- Test 6.2: JSON format with match length statistics +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -603,8 +657,8 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN +)'); + rpr_explain_filter ---------------------------------------------------------------------------- [ + { + @@ -618,7 +672,7 @@ WINDOW w AS ( "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a+\" b", + "Storage": "Memory", + - "Maximum Storage": 17, + + "Maximum Storage": 0, + "NFA States Peak": 3, + "NFA States Total": 191, + "NFA States Merged": 0, + @@ -659,6 +713,7 @@ WINDOW w AS ( -- Section 7: XML Format Tests -- -- Test 7.1: XML format output +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -667,8 +722,8 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +)'); + rpr_explain_filter -------------------------------------------------------------------------------- + + @@ -682,7 +737,7 @@ WINDOW w AS ( w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ a b + Memory + - 17 + + 0 + 2 + 31 + 0 + @@ -720,6 +775,7 @@ WINDOW w AS ( -- Section 8: Multiple Partitions Tests -- -- Test 8.1: Statistics across multiple partitions +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( @@ -733,13 +789,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ------------------------------------------------------------------------------------ WindowAgg (actual rows=90.00 loops=1) Window: w AS (PARTITION BY p.p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 165 total, 0 merged NFA Contexts: 3 peak, 93 total, 18 pruned NFA: 18 matched (len 5/5/5.0), 0 mismatched @@ -753,6 +809,7 @@ WINDOW w AS ( (14 rows) -- Test 8.2: Different pattern behavior per partition +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( @@ -767,13 +824,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS val < 5, B AS val >= 5 -); - QUERY PLAN +);'); + rpr_explain_filter -------------------------------------------------------------------------------------------------------------------------- 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 - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 77 total, 0 merged NFA Contexts: 3 peak, 52 total, 26 pruned NFA: 5 matched (len 5/6/5.8), 0 mismatched @@ -788,6 +845,7 @@ WINDOW w AS ( -- Section 9: Edge Cases -- -- Test 9.1: Empty result set +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 0) AS s(v) @@ -796,8 +854,8 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v = 1, B AS v = 2 -); - QUERY PLAN +);'); + rpr_explain_filter --------------------------------------------------------------------- WindowAgg (actual rows=0.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) @@ -806,6 +864,7 @@ WINDOW w AS ( (4 rows) -- Test 9.2: Single row +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1) AS s(v) @@ -814,13 +873,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A) DEFINE A AS TRUE -); - QUERY PLAN +);'); + rpr_explain_filter --------------------------------------------------------------------- WindowAgg (actual rows=1.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a - Storage: Memory Maximum Storage: 17kB + 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 @@ -828,6 +887,7 @@ WINDOW w AS ( (8 rows) -- Test 9.3: Pattern longer than data +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 5) AS s(v) @@ -838,13 +898,13 @@ WINDOW w AS ( DEFINE A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5, F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 -); - QUERY PLAN +);'); + rpr_explain_filter --------------------------------------------------------------------- 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 - Storage: Memory Maximum Storage: 17kB + 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) @@ -852,6 +912,7 @@ WINDOW w AS ( (8 rows) -- Test 9.4: All rows match as single match +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -860,13 +921,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS TRUE -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Storage: Memory Maximum Storage: 18kB + 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 @@ -878,6 +939,7 @@ WINDOW w AS ( -- Section 10: Complex Pattern Tests -- -- Test 10.1: Nested groups +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) @@ -886,13 +948,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (((A B) C)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b' c')+" - Storage: Memory Maximum Storage: 18kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 81 total, 0 merged NFA Contexts: 3 peak, 61 total, 40 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched @@ -901,6 +963,7 @@ WINDOW w AS ( (9 rows) -- Test 10.2: Multiple alternations +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -909,15 +972,15 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) (C | D | E)) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b c | d | e) - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 282 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) @@ -925,6 +988,7 @@ WINDOW w AS ( (8 rows) -- Test 10.3: Optional elements +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -933,13 +997,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B? C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b? c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 64 total, 0 merged NFA Contexts: 3 peak, 51 total, 37 pruned NFA: 12 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) @@ -947,6 +1011,7 @@ WINDOW w AS ( (8 rows) -- Test 10.4: Bounded quantifiers +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -955,13 +1020,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{2,5} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,5} b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 311 total, 0 merged NFA Contexts: 7 peak, 101 total, 10 pruned NFA: 10 matched (len 6/6/6.0), 50 mismatched (len 2/6/5.2) @@ -970,6 +1035,7 @@ WINDOW w AS ( (9 rows) -- Test 10.5: Star quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -978,13 +1044,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B* C) DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b* c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 51 total, 45 pruned NFA: 5 matched (len 9/9/9.0), 0 mismatched @@ -995,6 +1061,7 @@ WINDOW w AS ( -- Section 11: Real-world Pattern Examples -- -- Test 11.1: Stock price pattern - V-shape (down then up) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_complex @@ -1002,14 +1069,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (D+ U+) - DEFINE D AS trend = 'D', U AS trend = 'U' -); - QUERY PLAN + DEFINE D AS trend = ''D'', U AS trend = ''U'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: d+" u+ - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 58 total, 0 merged NFA Contexts: 3 peak, 31 total, 17 pruned NFA: 3 matched (len 3/14/8.0), 1 mismatched (len 3/3/3.0) @@ -1018,6 +1085,7 @@ WINDOW w AS ( (9 rows) -- Test 11.2: Stock price pattern - peak (up, stable, down) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_complex @@ -1025,14 +1093,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (U+ S* D+) - DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D' -); - QUERY PLAN + DEFINE U AS trend = ''U'', S AS trend = ''S'', D AS trend = ''D'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: u+" s* d+ - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 3 peak, 31 total, 14 pruned NFA: 4 matched (len 3/11/7.2), 0 mismatched @@ -1041,12 +1109,7 @@ WINDOW w AS ( (9 rows) -- Test 11.3: Consecutive increasing values (using PREV) --- FIXME: The original pattern was: --- DEFINE A AS v > PREV(v) OR PREV(v) IS NULL --- This causes "ERROR: unrecognized node type: 15" (T_FuncExpr) because --- NullTest(FuncExpr(PREV)) is not properly handled somewhere in the planner. --- The expression v > PREV(v) works fine, but PREV(v) IS NULL fails. --- Using COALESCE(PREV(v), 0) as a workaround until the bug is fixed. +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1054,14 +1117,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A{3,}) - DEFINE A AS v > COALESCE(PREV(v), 0) -); - QUERY PLAN + DEFINE A AS v > PREV(v) OR PREV(v) IS NULL +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3,}" - Storage: Memory Maximum Storage: 18kB + 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 @@ -1073,6 +1136,7 @@ WINDOW w AS ( -- Section 12: Performance-oriented Tests -- -- Test 12.1: Large dataset with simple pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) @@ -1081,13 +1145,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ------------------------------------------------------------------------ WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 1001 total, 0 merged NFA Contexts: 3 peak, 1001 total, 500 pruned NFA: 500 matched (len 2/2/2.0), 0 mismatched @@ -1095,6 +1159,7 @@ WINDOW w AS ( (8 rows) -- Test 12.2: Large dataset with absorption +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) @@ -1103,13 +1168,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ------------------------------------------------------------------------ WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 20kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 1991 total, 0 merged NFA Contexts: 3 peak, 1001 total, 10 pruned NFA: 10 matched (len 100/100/100.0), 0 mismatched @@ -1118,6 +1183,7 @@ WINDOW w AS ( (9 rows) -- Test 12.3: High state merge ratio +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -1126,13 +1192,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 8 peak, 2004 total, 0 merged NFA Contexts: 4 peak, 501 total, 167 pruned NFA: 166 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) @@ -1144,6 +1210,7 @@ WINDOW w AS ( -- Section 13: INITIAL vs no INITIAL comparison -- -- Test 13.1: With INITIAL keyword +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1153,13 +1220,13 @@ WINDOW w AS ( INITIAL PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 51 total, 10 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched @@ -1168,6 +1235,7 @@ WINDOW w AS ( (9 rows) -- Test 13.2: Without INITIAL keyword (same behavior currently) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1176,13 +1244,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 51 total, 10 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched @@ -1194,6 +1262,7 @@ WINDOW w AS ( -- Section 14: Quantifier Variations -- -- Test 14.1: Plus quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -1202,13 +1271,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS v % 4 <> 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Storage: Memory Maximum Storage: 17kB + 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 @@ -1217,6 +1286,7 @@ WINDOW w AS ( (9 rows) -- Test 14.2: Star quantifier (zero or more) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -1225,13 +1295,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A* B) DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" b - Storage: Memory Maximum Storage: 17kB + 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 @@ -1240,6 +1310,7 @@ WINDOW w AS ( (9 rows) -- Test 14.3: Question mark (zero or one) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -1248,13 +1319,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A? B C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a? b c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 82 total, 0 merged NFA Contexts: 4 peak, 41 total, 20 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched @@ -1263,6 +1334,7 @@ WINDOW w AS ( (9 rows) -- Test 14.4: Exact count {n} +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1271,13 +1343,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{3} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3} b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 51 total, 0 merged NFA Contexts: 5 peak, 51 total, 10 pruned NFA: 10 matched (len 4/4/4.0), 30 mismatched (len 2/4/3.0) @@ -1285,6 +1357,7 @@ WINDOW w AS ( (8 rows) -- Test 14.5: Range {n,m} +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1293,13 +1366,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,4} b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 101 total, 0 merged NFA Contexts: 6 peak, 51 total, 10 pruned NFA: 10 matched (len 5/5/5.0), 10 mismatched (len 2/2/2.0) @@ -1308,6 +1381,7 @@ WINDOW w AS ( (9 rows) -- Test 14.6: At least {n,} +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1316,13 +1390,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{3,} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3,}" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 86 total, 0 merged NFA Contexts: 3 peak, 51 total, 5 pruned NFA: 5 matched (len 10/10/10.0), 0 mismatched @@ -1335,6 +1409,7 @@ WINDOW w AS ( -- -- Test 15.1: Verify state count accuracy -- Pattern A+ B with 20 rows should show predictable state behavior +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) @@ -1343,13 +1418,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 37 total, 0 merged NFA Contexts: 3 peak, 21 total, 4 pruned NFA: 4 matched (len 5/5/5.0), 0 mismatched @@ -1358,6 +1433,7 @@ WINDOW w AS ( (9 rows) -- Test 15.2: Verify context count with known absorption +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -1366,13 +1442,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B C) DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 52 total, 0 merged NFA Contexts: 3 peak, 31 total, 9 pruned NFA: 3 matched (len 9/9/9.0), 0 mismatched @@ -1381,6 +1457,7 @@ WINDOW w AS ( (9 rows) -- Test 15.3: Verify match length with fixed-length pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -1389,13 +1466,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 31 total, 0 merged NFA Contexts: 3 peak, 31 total, 20 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched @@ -1406,6 +1483,7 @@ WINDOW w AS ( -- Section 16: Alternation Pattern Tests -- -- Test 16.1: Simple alternation +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -1413,14 +1491,14 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); - QUERY PLAN + DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 202 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) @@ -1428,6 +1506,7 @@ WINDOW w AS ( (8 rows) -- Test 16.2: Multiple items in alternation +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -1436,15 +1515,15 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B | C | D) E) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); + rpr_explain_filter ------------------------------------------------------------------- WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c | d) e - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 404 total, 0 merged NFA Contexts: 3 peak, 101 total, 20 pruned NFA: 20 matched (len 2/2/2.0), 60 mismatched (len 2/2/2.0) @@ -1452,6 +1531,7 @@ WINDOW w AS ( (8 rows) -- Test 16.3: Alternation with quantifiers +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1460,13 +1540,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 8 peak, 204 total, 0 merged NFA Contexts: 4 peak, 51 total, 17 pruned NFA: 16 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) @@ -1478,6 +1558,7 @@ WINDOW w AS ( -- Section 17: Group Pattern Tests -- -- Test 17.1: Simple group +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -1486,13 +1567,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" - Storage: Memory Maximum Storage: 18kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 61 total, 0 merged NFA Contexts: 3 peak, 41 total, 20 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched @@ -1501,6 +1582,7 @@ WINDOW w AS ( (9 rows) -- Test 17.2: Group with bounded quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -1509,13 +1591,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B){2,4}) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){2,4} - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 66 total, 0 merged NFA Contexts: 6 peak, 41 total, 20 pruned NFA: 5 matched (len 8/8/8.0), 0 mismatched @@ -1524,6 +1606,7 @@ WINDOW w AS ( (9 rows) -- Test 17.3: Nested groups +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) @@ -1532,13 +1615,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (((A B){2})+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a b){2})+ - Storage: Memory Maximum Storage: 18kB + Storage: Memory Maximum Storage: NkB NFA States: 60 peak, 286 total, 0 merged NFA Contexts: 32 peak, 61 total, 30 pruned NFA: 1 matched (len 60/60/60.0), 1 mismatched (len 2/2/2.0) @@ -1550,6 +1633,7 @@ WINDOW w AS ( -- Section 18: Window Function Combinations -- -- Test 18.1: count(*) with pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -1558,13 +1642,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched @@ -1573,6 +1657,7 @@ WINDOW w AS ( (9 rows) -- Test 18.2: first_value with pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT first_value(v) OVER w FROM generate_series(1, 30) AS s(v) @@ -1581,13 +1666,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched @@ -1596,6 +1681,7 @@ WINDOW w AS ( (9 rows) -- Test 18.3: last_value with pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT last_value(v) OVER w FROM generate_series(1, 30) AS s(v) @@ -1604,13 +1690,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched @@ -1619,6 +1705,7 @@ WINDOW w AS ( (9 rows) -- Test 18.4: Multiple window functions +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w, @@ -1630,13 +1717,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched @@ -1648,6 +1735,7 @@ WINDOW w AS ( -- Section 19: DEFINE Expression Variations -- -- Test 19.1: Complex boolean expressions +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -1658,13 +1746,13 @@ WINDOW w AS ( DEFINE A AS (v % 5 <> 0) AND (v % 3 <> 0), B AS (v % 5 = 0) OR (v % 3 = 0) -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 78 total, 0 merged NFA Contexts: 3 peak, 51 total, 23 pruned NFA: 17 matched (len 2/3/2.6), 0 mismatched @@ -1673,6 +1761,7 @@ WINDOW w AS ( (9 rows) -- Test 19.2: Using PREV function +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -1684,13 +1773,13 @@ WINDOW w AS ( S AS TRUE, U AS v > PREV(v), D AS v < PREV(v) -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: s u+ d+ - Storage: Memory Maximum Storage: 17kB + 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) @@ -1698,6 +1787,7 @@ WINDOW w AS ( (8 rows) -- Test 19.3: Using NULL comparisons +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( @@ -1709,13 +1799,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v IS NOT NULL, B AS v IS NULL -); - QUERY PLAN +);'); + rpr_explain_filter ---------------------------------------------------------------------- WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched @@ -1727,6 +1817,7 @@ WINDOW w AS ( -- Section 20: Large Scale Statistics Verification -- -- Test 20.1: 500 rows - verify statistics scale correctly +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -1735,13 +1826,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B C) DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b c - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 851 total, 0 merged NFA Contexts: 3 peak, 501 total, 151 pruned NFA: 50 matched (len 8/9/9.0), 0 mismatched @@ -1750,6 +1841,7 @@ WINDOW w AS ( (9 rows) -- Test 20.2: High match count scenario +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -1758,13 +1850,13 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 501 total, 0 merged NFA Contexts: 3 peak, 501 total, 250 pruned NFA: 250 matched (len 2/2/2.0), 0 mismatched @@ -1772,6 +1864,7 @@ WINDOW w AS ( (8 rows) -- Test 20.3: High skip count scenario +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -1785,13 +1878,13 @@ WINDOW w AS ( C AS v % 100 = 3, D AS v % 100 = 4, E AS v % 100 = 5 -); - QUERY PLAN +);'); + rpr_explain_filter ----------------------------------------------------------------------- WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e - Storage: Memory Maximum Storage: 17kB + Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 501 total, 0 merged NFA Contexts: 3 peak, 501 total, 495 pruned NFA: 5 matched (len 5/5/5.0), 0 mismatched diff --git a/src/test/regress/expected/rpr_explain_1.out b/src/test/regress/expected/rpr_explain_1.out deleted file mode 100644 index 1c1bbda42b2..00000000000 --- a/src/test/regress/expected/rpr_explain_1.out +++ /dev/null @@ -1,1803 +0,0 @@ --- --- Test: EXPLAIN ANALYZE output for Row Pattern Recognition NFA statistics --- --- This file tests the NFA statistics shown in EXPLAIN ANALYZE output: --- - NFA States: peak, total, merged --- - NFA Contexts: peak, total, absorbed, skipped --- - NFA: matched (len min/max/avg), mismatched (len min/max/avg) --- --- Setup: Create test tables -CREATE TEMP TABLE nfa_test ( - id serial, - v int, - cat char(1) -); --- Insert test data: 100 rows with predictable pattern -INSERT INTO nfa_test (v, cat) -SELECT i, - CASE - WHEN i % 5 = 1 THEN 'A' - WHEN i % 5 = 2 THEN 'B' - WHEN i % 5 = 3 THEN 'C' - WHEN i % 5 = 4 THEN 'D' - ELSE 'E' - END -FROM generate_series(1, 100) i; --- Additional test table with more complex patterns -CREATE TEMP TABLE nfa_complex ( - id serial, - price int, - trend char(1) -- U=up, D=down, S=stable -); -INSERT INTO nfa_complex (price, trend) -VALUES - (100, 'S'), (105, 'U'), (110, 'U'), (108, 'D'), (112, 'U'), - (115, 'U'), (113, 'D'), (111, 'D'), (109, 'D'), (110, 'U'), - (120, 'U'), (125, 'U'), (130, 'U'), (128, 'D'), (126, 'D'), - (124, 'D'), (122, 'D'), (120, 'D'), (118, 'D'), (119, 'U'), - (121, 'U'), (123, 'U'), (125, 'U'), (127, 'U'), (129, 'U'), - (131, 'U'), (133, 'U'), (130, 'D'), (127, 'D'), (124, 'D'); --- --- Section 1: Basic NFA Statistics Tests --- --- Test 1.1: Simple pattern - should show basic statistics -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B) - DEFINE A AS cat = 'A', B AS cat = 'B' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 101 total, 0 merged - NFA Contexts: 3 peak, 101 total, 80 pruned - NFA: 20 matched (len 2/2/2.0), 0 mismatched - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 1.2: Pattern with no matches - 0 matched -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (X Y Z) - DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: x y z - Storage: Memory Maximum Storage: 17kB - NFA States: 1 peak, 101 total, 0 merged - NFA Contexts: 2 peak, 101 total, 100 pruned - NFA: 0 matched, 0 mismatched - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 1.3: Pattern matching every row - high match count -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (R) - DEFINE R AS TRUE -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: r - Storage: Memory Maximum Storage: 17kB - 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 nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- --- Section 2: State Statistics Tests (peak, total, merged) --- --- Test 2.1: Simple quantifier pattern - A+ with short matches (no merging) -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+) - DEFINE A AS v % 2 = 1 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" - Storage: Memory Maximum Storage: 17kB - 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) - --- Test 2.2: Alternation pattern - multiple state branches -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B | C) (D | E)) - DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b | c d | e) - Storage: Memory Maximum Storage: 17kB - NFA States: 5 peak, 363 total, 0 merged - NFA Contexts: 3 peak, 101 total, 40 pruned - NFA: 20 matched (len 2/2/2.0), 40 mismatched (len 2/2/2.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 2.3: Complex pattern with high state count -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B* C+) - DEFINE - A AS v % 3 = 1, - B AS v % 3 = 2, - C AS v % 3 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b* c+ - Storage: Memory Maximum Storage: 17kB - NFA States: 5 peak, 235 total, 0 merged - NFA Contexts: 3 peak, 101 total, 67 pruned - NFA: 33 matched (len 3/3/3.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(8 rows) - --- Test 2.4: Grouped pattern with quantifier - state merging -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 60) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A B)+) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=60.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b')+" - Storage: Memory Maximum Storage: 19kB - NFA States: 3 peak, 91 total, 0 merged - NFA Contexts: 3 peak, 61 total, 30 pruned - NFA: 1 matched (len 60/60/60.0), 0 mismatched - NFA: 29 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) - --- Test 2.5: State explosion pattern - many alternations --- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b a | b a | b a | b a | b a | b a | b a | b) - Storage: Memory Maximum Storage: 17kB - NFA States: 17 peak, 632 total, 0 merged - NFA Contexts: 9 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) - --- Test 2.6: High state merging - alternation with plus quantifier -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B | C)+ D) - DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b | c)+ d - Storage: Memory Maximum Storage: 17kB - NFA States: 15 peak, 753 total, 0 merged - NFA Contexts: 5 peak, 101 total, 25 pruned - NFA: 25 matched (len 4/4/4.0), 0 mismatched - NFA: 0 absorbed, 50 skipped (len 2/3/2.5) - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) - --- Test 2.7: Nested quantifiers causing state growth -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 1000) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (((A | B)+)+) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2 -); - QUERY PLAN ------------------------------------------------------------------------- - WindowAgg (actual rows=1000.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: ((a | b)+)+ - Storage: Memory Maximum Storage: 17kB - NFA States: 16 peak, 7334 total, 0 merged - NFA Contexts: 4 peak, 1001 total, 333 pruned - NFA: 334 matched (len 1/2/2.0), 0 mismatched - NFA: 0 absorbed, 333 skipped (len 2/2/2.0) - -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(9 rows) - --- --- Section 3: Context Statistics Tests (peak, total, absorbed, skipped) --- --- Test 3.1: Context absorption with unbounded quantifier at start -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 91 total, 0 merged - NFA Contexts: 3 peak, 51 total, 10 pruned - NFA: 10 matched (len 5/5/5.0), 0 mismatched - NFA: 30 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- Test 3.2: No absorption - bounded quantifier -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A{2,4} B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{2,4} b - Storage: Memory Maximum Storage: 17kB - NFA States: 7 peak, 101 total, 0 merged - NFA Contexts: 6 peak, 51 total, 10 pruned - NFA: 10 matched (len 5/5/5.0), 10 mismatched (len 2/2/2.0) - NFA: 0 absorbed, 20 skipped (len 3/4/3.5) - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- Test 3.3: Contexts skipped by SKIP PAST LAST ROW -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B C) - DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b c - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 101 total, 0 merged - NFA Contexts: 3 peak, 101 total, 90 pruned - NFA: 10 matched (len 3/3/3.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(8 rows) - --- Test 3.4: High context absorption - unbounded group -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A B)+ C) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b')+" c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 134 total, 0 merged - NFA Contexts: 3 peak, 101 total, 67 pruned - NFA: 33 matched (len 3/3/3.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(8 rows) - --- --- Section 4: Match Length Statistics Tests --- --- Test 4.1: Fixed length matches - all same length -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B C D E) - DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b c d e - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 101 total, 0 merged - NFA Contexts: 3 peak, 101 total, 80 pruned - NFA: 20 matched (len 5/5/5.0), 0 mismatched - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 4.2: Variable length matches - min/max/avg differ -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 191 total, 0 merged - NFA Contexts: 3 peak, 101 total, 10 pruned - NFA: 10 matched (len 10/10/10.0), 0 mismatched - NFA: 80 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) - --- Test 4.3: Very long matches -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 200) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v <= 195, B AS v > 195 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=200.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 24kB - NFA States: 3 peak, 396 total, 0 merged - NFA Contexts: 3 peak, 201 total, 5 pruned - NFA: 1 matched (len 196/196/196.0), 0 mismatched - NFA: 194 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=200.00 loops=1) -(9 rows) - --- Test 4.4: Mix of short and long matches -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE - A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15), - B AS v % 20 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 171 total, 0 merged - NFA Contexts: 3 peak, 101 total, 30 pruned - NFA: 5 matched (len 5/5/5.0), 5 mismatched (len 11/11/11.0) - NFA: 60 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) - --- --- Section 5: Mismatch Length Statistics Tests --- --- Test 5.1: Pattern that causes mismatches with length > 1 --- Mismatch happens when partial match fails after processing multiple rows -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM ( - SELECT v, - CASE WHEN v % 10 IN (1,2,3) THEN 'A' - WHEN v % 10 IN (4,5) THEN 'B' - WHEN v % 10 = 6 THEN 'C' - ELSE 'X' END AS cat - FROM generate_series(1, 100) AS s(v) -) t -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B+ C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b+ c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 151 total, 0 merged - NFA Contexts: 3 peak, 101 total, 70 pruned - NFA: 10 matched (len 6/6/6.0), 0 mismatched - NFA: 20 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) - --- Test 5.2: Long partial matches that fail -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM ( - SELECT i AS v, - CASE - WHEN i <= 20 THEN 'A' - WHEN i <= 25 THEN 'B' - WHEN i = 26 THEN 'X' -- breaks the pattern - WHEN i <= 50 THEN 'A' - WHEN i <= 55 THEN 'B' - WHEN i = 56 THEN 'C' -- completes pattern - ELSE 'Y' - END AS cat - FROM generate_series(1, 60) i -) t -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B+ C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=60.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b+ c - Storage: Memory Maximum Storage: 18kB - NFA States: 3 peak, 115 total, 0 merged - NFA Contexts: 3 peak, 61 total, 16 pruned - NFA: 1 matched (len 30/30/30.0), 1 mismatched (len 26/26/26.0) - NFA: 42 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series i (actual rows=60.00 loops=1) -(9 rows) - --- --- Section 6: JSON Format Tests --- --- Test 6.1: JSON format output with all statistics -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B+) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2 -); - QUERY PLAN ----------------------------------------------------------------------------- - [ + - { + - "Plan": { + - "Node Type": "WindowAgg", + - "Parallel Aware": false, + - "Async Capable": false, + - "Actual Rows": 50.00, + - "Actual Loops": 1, + - "Disabled": false, + - "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ - "Pattern": "a+\" b+", + - "Storage": "Memory", + - "Maximum Storage": 17, + - "NFA States Peak": 3, + - "NFA States Total": 85, + - "NFA States Merged": 0, + - "NFA Contexts Peak": 3, + - "NFA Contexts Total": 51, + - "NFA Contexts Absorbed": 0, + - "NFA Contexts Skipped": 0, + - "NFA Contexts Pruned": 33, + - "NFA Matched": 17, + - "NFA Mismatched": 0, + - "NFA Match Length Min": 2, + - "NFA Match Length Max": 2, + - "NFA Match Length Avg": 2.0, + - "Plans": [ + - { + - "Node Type": "Function Scan", + - "Parent Relationship": "Outer", + - "Parallel Aware": false, + - "Async Capable": false, + - "Function Name": "generate_series", + - "Alias": "s", + - "Actual Rows": 50.00, + - "Actual Loops": 1, + - "Disabled": false + - } + - ] + - }, + - "Triggers": [ + - ] + - } + - ] -(1 row) - --- Test 6.2: JSON format with match length statistics -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN ----------------------------------------------------------------------------- - [ + - { + - "Plan": { + - "Node Type": "WindowAgg", + - "Parallel Aware": false, + - "Async Capable": false, + - "Actual Rows": 100.00, + - "Actual Loops": 1, + - "Disabled": false, + - "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ - "Pattern": "a+\" b", + - "Storage": "Memory", + - "Maximum Storage": 17, + - "NFA States Peak": 3, + - "NFA States Total": 191, + - "NFA States Merged": 0, + - "NFA Contexts Peak": 3, + - "NFA Contexts Total": 101, + - "NFA Contexts Absorbed": 80, + - "NFA Contexts Skipped": 0, + - "NFA Contexts Pruned": 10, + - "NFA Matched": 10, + - "NFA Mismatched": 0, + - "NFA Match Length Min": 10, + - "NFA Match Length Max": 10, + - "NFA Match Length Avg": 10.0, + - "NFA Absorbed Length Min": 1, + - "NFA Absorbed Length Max": 1, + - "NFA Absorbed Length Avg": 1.0, + - "Plans": [ + - { + - "Node Type": "Function Scan", + - "Parent Relationship": "Outer", + - "Parallel Aware": false, + - "Async Capable": false, + - "Function Name": "generate_series", + - "Alias": "s", + - "Actual Rows": 100.00, + - "Actual Loops": 1, + - "Disabled": false + - } + - ] + - }, + - "Triggers": [ + - ] + - } + - ] -(1 row) - --- --- Section 7: XML Format Tests --- --- Test 7.1: XML format output -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) -SELECT count(*) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN --------------------------------------------------------------------------------- - + - + - + - WindowAgg + - false + - false + - 30.00 + - 1 + - false + - w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ - a b + - Memory + - 17 + - 2 + - 31 + - 0 + - 3 + - 31 + - 0 + - 0 + - 15 + - 15 + - 0 + - 2 + - 2 + - 2.0 + - + - + - Function Scan + - Outer + - false + - false + - generate_series + - s + - 30.00 + - 1 + - false + - + - + - + - + - + - + - -(1 row) - --- --- Section 8: Multiple Partitions Tests --- --- Test 8.1: Statistics across multiple partitions -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM ( - SELECT p, v - FROM generate_series(1, 3) p, - generate_series(1, 30) v -) t -WINDOW w AS ( - PARTITION BY p - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ------------------------------------------------------------------------------------- - WindowAgg (actual rows=90.00 loops=1) - Window: w AS (PARTITION BY p.p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 165 total, 0 merged - NFA Contexts: 3 peak, 93 total, 18 pruned - NFA: 18 matched (len 5/5/5.0), 0 mismatched - NFA: 54 absorbed (len 1/1/1.0), 0 skipped - -> Sort (actual rows=90.00 loops=1) - Sort Key: p.p - Sort Method: quicksort Memory: 27kB - -> 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) - --- Test 8.2: Different pattern behavior per partition -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM ( - SELECT - CASE WHEN v <= 25 THEN 1 ELSE 2 END AS p, - v % 10 AS val - FROM generate_series(1, 50) v -) t -WINDOW w AS ( - PARTITION BY p - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS val < 5, B AS val >= 5 -); - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- - 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 - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 77 total, 0 merged - NFA Contexts: 3 peak, 52 total, 26 pruned - NFA: 5 matched (len 5/6/5.8), 0 mismatched - NFA: 19 absorbed (len 1/1/1.0), 0 skipped - -> Sort (actual rows=50.00 loops=1) - Sort Key: (CASE WHEN (v.v <= 25) THEN 1 ELSE 2 END) - Sort Method: quicksort Memory: 26kB - -> Function Scan on generate_series v (actual rows=50.00 loops=1) -(12 rows) - --- --- Section 9: Edge Cases --- --- Test 9.1: Empty result set -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 0) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B) - DEFINE A AS v = 1, B AS v = 2 -); - QUERY PLAN ---------------------------------------------------------------------- - WindowAgg (actual rows=0.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b - -> Function Scan on generate_series s (actual rows=0.00 loops=1) -(4 rows) - --- Test 9.2: Single row -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 1) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A) - DEFINE A AS TRUE -); - QUERY PLAN ---------------------------------------------------------------------- - WindowAgg (actual rows=1.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a - Storage: Memory Maximum Storage: 17kB - 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) - --- Test 9.3: Pattern longer than data -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 5) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B C D E F G H I J) - DEFINE - A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5, - F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 -); - QUERY PLAN ---------------------------------------------------------------------- - 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 - Storage: Memory Maximum Storage: 17kB - 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) - --- Test 9.4: All rows match as single match -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+) - DEFINE A AS TRUE -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" - Storage: Memory Maximum Storage: 18kB - 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) - --- --- Section 10: Complex Pattern Tests --- --- Test 10.1: Nested groups -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 60) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (((A B) C)+) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=60.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b' c')+" - Storage: Memory Maximum Storage: 19kB - NFA States: 3 peak, 81 total, 0 merged - NFA Contexts: 3 peak, 61 total, 40 pruned - NFA: 1 matched (len 60/60/60.0), 0 mismatched - NFA: 19 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) - --- Test 10.2: Multiple alternations -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B) (C | D | E)) - DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b c | d | e) - Storage: Memory Maximum Storage: 17kB - NFA States: 5 peak, 282 total, 0 merged - NFA Contexts: 3 peak, 101 total, 60 pruned - NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 10.3: Optional elements -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B? C) - DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b? c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 64 total, 0 merged - NFA Contexts: 3 peak, 51 total, 37 pruned - NFA: 12 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(8 rows) - --- Test 10.4: Bounded quantifiers -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 100) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A{2,5} B) - DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{2,5} b - Storage: Memory Maximum Storage: 17kB - NFA States: 9 peak, 311 total, 0 merged - NFA Contexts: 7 peak, 101 total, 10 pruned - NFA: 10 matched (len 6/6/6.0), 50 mismatched (len 2/6/5.2) - NFA: 0 absorbed, 30 skipped (len 3/5/4.0) - -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) - --- Test 10.5: Star quantifier -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B* C) - DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b* c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 91 total, 0 merged - NFA Contexts: 3 peak, 51 total, 45 pruned - NFA: 5 matched (len 9/9/9.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(8 rows) - --- --- Section 11: Real-world Pattern Examples --- --- Test 11.1: Stock price pattern - V-shape (down then up) -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_complex -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (D+ U+) - DEFINE D AS trend = 'D', U AS trend = 'U' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: d+" u+ - Storage: Memory Maximum Storage: 17kB - NFA States: 4 peak, 58 total, 0 merged - NFA Contexts: 3 peak, 31 total, 17 pruned - NFA: 3 matched (len 3/14/8.0), 1 mismatched (len 3/3/3.0) - NFA: 9 absorbed (len 1/1/1.0), 0 skipped - -> Seq Scan on nfa_complex (actual rows=30.00 loops=1) -(9 rows) - --- Test 11.2: Stock price pattern - peak (up, stable, down) -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_complex -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (U+ S* D+) - DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: u+" s* d+ - Storage: Memory Maximum Storage: 17kB - NFA States: 5 peak, 76 total, 0 merged - NFA Contexts: 3 peak, 31 total, 14 pruned - NFA: 4 matched (len 3/11/7.2), 0 mismatched - NFA: 12 absorbed (len 1/1/1.0), 0 skipped - -> Seq Scan on nfa_complex (actual rows=30.00 loops=1) -(9 rows) - --- Test 11.3: Consecutive increasing values (using PREV) --- FIXME: The original pattern was: --- DEFINE A AS v > PREV(v) OR PREV(v) IS NULL --- This causes "ERROR: unrecognized node type: 15" (T_FuncExpr) because --- NullTest(FuncExpr(PREV)) is not properly handled somewhere in the planner. --- The expression v > PREV(v) works fine, but PREV(v) IS NULL fails. --- Using COALESCE(PREV(v), 0) as a workaround until the bug is fixed. -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A{3,}) - DEFINE A AS v > COALESCE(PREV(v), 0) -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{3,}" - Storage: Memory Maximum Storage: 18kB - 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) - --- --- Section 12: Performance-oriented Tests --- --- Test 12.1: Large dataset with simple pattern -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 1000) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ------------------------------------------------------------------------- - WindowAgg (actual rows=1000.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 1001 total, 0 merged - NFA Contexts: 3 peak, 1001 total, 500 pruned - NFA: 500 matched (len 2/2/2.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(8 rows) - --- Test 12.2: Large dataset with absorption -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 1000) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 -); - QUERY PLAN ------------------------------------------------------------------------- - WindowAgg (actual rows=1000.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 21kB - NFA States: 3 peak, 1991 total, 0 merged - NFA Contexts: 3 peak, 1001 total, 10 pruned - NFA: 10 matched (len 100/100/100.0), 0 mismatched - NFA: 980 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(9 rows) - --- Test 12.3: High state merge ratio -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 500) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B)+ C) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=500.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b)+ c - Storage: Memory Maximum Storage: 17kB - NFA States: 8 peak, 2004 total, 0 merged - NFA Contexts: 4 peak, 501 total, 167 pruned - NFA: 166 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) - NFA: 0 absorbed, 166 skipped (len 2/2/2.0) - -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(9 rows) - --- --- Section 13: INITIAL vs no INITIAL comparison --- --- Test 13.1: With INITIAL keyword -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 91 total, 0 merged - NFA Contexts: 3 peak, 51 total, 10 pruned - NFA: 10 matched (len 5/5/5.0), 0 mismatched - NFA: 30 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- Test 13.2: Without INITIAL keyword (same behavior currently) -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 91 total, 0 merged - NFA Contexts: 3 peak, 51 total, 10 pruned - NFA: 10 matched (len 5/5/5.0), 0 mismatched - NFA: 30 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- --- Section 14: Quantifier Variations --- --- Test 14.1: Plus quantifier -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 40) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+) - DEFINE A AS v % 4 <> 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=40.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" - Storage: Memory Maximum Storage: 17kB - 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) - --- Test 14.2: Star quantifier (zero or more) -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 40) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A* B) - DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=40.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a*" b - Storage: Memory Maximum Storage: 17kB - 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: 20 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) - --- Test 14.3: Question mark (zero or one) -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 40) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A? B C) - DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=40.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a? b c - Storage: Memory Maximum Storage: 17kB - NFA States: 4 peak, 82 total, 0 merged - NFA Contexts: 4 peak, 41 total, 20 pruned - NFA: 10 matched (len 3/3/3.0), 0 mismatched - NFA: 0 absorbed, 10 skipped (len 2/2/2.0) - -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) - --- Test 14.4: Exact count {n} -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A{3} B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{3} b - Storage: Memory Maximum Storage: 17kB - NFA States: 4 peak, 51 total, 0 merged - NFA Contexts: 5 peak, 51 total, 10 pruned - NFA: 10 matched (len 4/4/4.0), 30 mismatched (len 2/4/3.0) - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(8 rows) - --- Test 14.5: Range {n,m} -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A{2,4} B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{2,4} b - Storage: Memory Maximum Storage: 17kB - NFA States: 7 peak, 101 total, 0 merged - NFA Contexts: 6 peak, 51 total, 10 pruned - NFA: 10 matched (len 5/5/5.0), 10 mismatched (len 2/2/2.0) - NFA: 0 absorbed, 20 skipped (len 3/4/3.5) - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- Test 14.6: At least {n,} -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A{3,} B) - DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a{3,}" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 86 total, 0 merged - NFA Contexts: 3 peak, 51 total, 5 pruned - NFA: 5 matched (len 10/10/10.0), 0 mismatched - NFA: 40 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- --- Section 15: Regression Tests for Statistics Accuracy --- --- Test 15.1: Verify state count accuracy --- Pattern A+ B with 20 rows should show predictable state behavior -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 20) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=20.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 37 total, 0 merged - NFA Contexts: 3 peak, 21 total, 4 pruned - NFA: 4 matched (len 5/5/5.0), 0 mismatched - NFA: 12 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) - --- Test 15.2: Verify context count with known absorption -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B C) - DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 52 total, 0 merged - NFA Contexts: 3 peak, 31 total, 9 pruned - NFA: 3 matched (len 9/9/9.0), 0 mismatched - NFA: 18 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) - --- Test 15.3: Verify match length with fixed-length pattern -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B C) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b c - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 31 total, 0 merged - NFA Contexts: 3 peak, 31 total, 20 pruned - NFA: 10 matched (len 3/3/3.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(8 rows) - --- --- Section 16: Alternation Pattern Tests --- --- Test 16.1: Simple alternation -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B) C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b) c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 202 total, 0 merged - NFA Contexts: 3 peak, 101 total, 60 pruned - NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 16.2: Multiple items in alternation -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM nfa_test -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B | C | D) E) - DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); - QUERY PLAN -------------------------------------------------------------------- - WindowAgg (actual rows=100.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b | c | d) e - Storage: Memory Maximum Storage: 17kB - NFA States: 5 peak, 404 total, 0 merged - NFA Contexts: 3 peak, 101 total, 20 pruned - NFA: 20 matched (len 2/2/2.0), 60 mismatched (len 2/2/2.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) -(8 rows) - --- Test 16.3: Alternation with quantifiers -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A | B)+ C) - DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a | b)+ c - Storage: Memory Maximum Storage: 17kB - NFA States: 8 peak, 204 total, 0 merged - NFA Contexts: 4 peak, 51 total, 17 pruned - NFA: 16 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) - NFA: 0 absorbed, 16 skipped (len 2/2/2.0) - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- --- Section 17: Group Pattern Tests --- --- Test 17.1: Simple group -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 40) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A B)+) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=40.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a' b')+" - Storage: Memory Maximum Storage: 18kB - NFA States: 3 peak, 61 total, 0 merged - NFA Contexts: 3 peak, 41 total, 20 pruned - NFA: 1 matched (len 40/40/40.0), 0 mismatched - NFA: 19 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) - --- Test 17.2: Group with bounded quantifier -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 40) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN ((A B){2,4}) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=40.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a b){2,4} - Storage: Memory Maximum Storage: 17kB - NFA States: 7 peak, 66 total, 0 merged - NFA Contexts: 6 peak, 41 total, 20 pruned - NFA: 5 matched (len 8/8/8.0), 0 mismatched - NFA: 0 absorbed, 15 skipped (len 2/6/4.0) - -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(9 rows) - --- Test 17.3: Nested groups -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 60) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (((A B){2})+) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=60.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: ((a b){2})+ - Storage: Memory Maximum Storage: 19kB - NFA States: 60 peak, 286 total, 0 merged - NFA Contexts: 32 peak, 61 total, 30 pruned - NFA: 1 matched (len 60/60/60.0), 1 mismatched (len 2/2/2.0) - NFA: 0 absorbed, 28 skipped (len 4/58/31.0) - -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(9 rows) - --- --- Section 18: Window Function Combinations --- --- Test 18.1: count(*) with pattern -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 55 total, 0 merged - NFA Contexts: 3 peak, 31 total, 6 pruned - NFA: 6 matched (len 5/5/5.0), 0 mismatched - NFA: 18 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) - --- Test 18.2: first_value with pattern -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT first_value(v) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 55 total, 0 merged - NFA Contexts: 3 peak, 31 total, 6 pruned - NFA: 6 matched (len 5/5/5.0), 0 mismatched - NFA: 18 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) - --- Test 18.3: last_value with pattern -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT last_value(v) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 18kB - NFA States: 3 peak, 55 total, 0 merged - NFA Contexts: 3 peak, 31 total, 6 pruned - NFA: 6 matched (len 5/5/5.0), 0 mismatched - NFA: 18 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) - --- Test 18.4: Multiple window functions -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT - count(*) OVER w, - first_value(v) OVER w, - last_value(v) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 18kB - NFA States: 3 peak, 55 total, 0 merged - NFA Contexts: 3 peak, 31 total, 6 pruned - NFA: 6 matched (len 5/5/5.0), 0 mismatched - NFA: 18 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) - --- --- Section 19: DEFINE Expression Variations --- --- Test 19.1: Complex boolean expressions -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 50) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE - A AS (v % 5 <> 0) AND (v % 3 <> 0), - B AS (v % 5 = 0) OR (v % 3 = 0) -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=50.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 78 total, 0 merged - NFA Contexts: 3 peak, 51 total, 23 pruned - NFA: 17 matched (len 2/3/2.6), 0 mismatched - NFA: 10 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) - --- Test 19.2: Using PREV function -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 30) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (S U+ D+) - DEFINE - S AS TRUE, - U AS v > PREV(v), - D AS v < PREV(v) -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: s u+ d+ - Storage: Memory Maximum Storage: 18kB - 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) - --- Test 19.3: Using NULL comparisons -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM ( - SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v - FROM generate_series(1, 30) v -) t -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B) - DEFINE A AS v IS NOT NULL, B AS v IS NULL -); - QUERY PLAN ----------------------------------------------------------------------- - WindowAgg (actual rows=30.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 55 total, 0 merged - NFA Contexts: 3 peak, 31 total, 6 pruned - NFA: 6 matched (len 5/5/5.0), 0 mismatched - NFA: 18 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series v (actual rows=30.00 loops=1) -(9 rows) - --- --- Section 20: Large Scale Statistics Verification --- --- Test 20.1: 500 rows - verify statistics scale correctly -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 500) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A+ B C) - DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=500.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" b c - Storage: Memory Maximum Storage: 17kB - NFA States: 3 peak, 851 total, 0 merged - NFA Contexts: 3 peak, 501 total, 151 pruned - NFA: 50 matched (len 8/9/9.0), 0 mismatched - NFA: 299 absorbed (len 1/1/1.0), 0 skipped - -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(9 rows) - --- Test 20.2: High match count scenario -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 500) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B) - DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=500.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 501 total, 0 merged - NFA Contexts: 3 peak, 501 total, 250 pruned - NFA: 250 matched (len 2/2/2.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(8 rows) - --- Test 20.3: High skip count scenario -EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) -SELECT count(*) OVER w -FROM generate_series(1, 500) AS s(v) -WINDOW w AS ( - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - PATTERN (A B C D E) - DEFINE - A AS v % 100 = 1, - B AS v % 100 = 2, - C AS v % 100 = 3, - D AS v % 100 = 4, - E AS v % 100 = 5 -); - QUERY PLAN ------------------------------------------------------------------------ - WindowAgg (actual rows=500.00 loops=1) - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b c d e - Storage: Memory Maximum Storage: 17kB - NFA States: 2 peak, 501 total, 0 merged - NFA Contexts: 3 peak, 501 total, 495 pruned - NFA: 5 matched (len 5/5/5.0), 0 mismatched - -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(8 rows) - --- Cleanup -DROP TABLE nfa_test; -DROP TABLE nfa_complex; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4a839d827a2..fc61d90ebaf 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -107,7 +107,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # ---------- # Row Pattern Recognition tests # ---------- -test: rpr rpr_base +test: rpr rpr_base rpr_explain # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index f070f05a497..8156121b3cd 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -7,6 +7,39 @@ -- - NFA: matched (len min/max/avg), mismatched (len min/max/avg) -- +-- Filter function to normalize Storage memory values only (not NFA statistics) +-- Works for text, JSON, and XML formats +create function rpr_explain_filter(text) returns setof text +language plpgsql as +$$ +declare + ln text; +begin + for ln in execute $1 + loop + -- Normalize memory size in Storage line only (platform-dependent) + -- Keep NFA statistics numbers unchanged (they are test assertions) + + -- Text format: "Storage: Memory Maximum Storage: 18kB" + if ln ~ 'Storage:.*Maximum Storage:' then + ln := regexp_replace(ln, '\m\d+kB', 'NkB', 'g'); + end if; + + -- JSON format: "Maximum Storage": 17 (number in kB units) + if ln ~ '"Maximum Storage":' then + ln := regexp_replace(ln, '"Maximum Storage": \d+', '"Maximum Storage": 0', 'g'); + end if; + + -- XML format: 17 (number in kB units) + if ln ~ '' then + ln := regexp_replace(ln, '\d+', '0', 'g'); + end if; + + return next ln; + end loop; +end; +$$; + -- Setup: Create test tables CREATE TEMP TABLE nfa_test ( id serial, @@ -47,6 +80,7 @@ VALUES -- -- Test 1.1: Simple pattern - should show basic statistics +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -54,10 +88,11 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) - DEFINE A AS cat = 'A', B AS cat = 'B' -); + DEFINE A AS cat = ''A'', B AS cat = ''B'' +)'); -- Test 1.2: Pattern with no matches - 0 matched +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -65,10 +100,11 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (X Y Z) - DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z' -); + DEFINE X AS cat = ''X'', Y AS cat = ''Y'', Z AS cat = ''Z'' +);'); -- Test 1.3: Pattern matching every row - high match count +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -77,13 +113,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (R) DEFINE R AS TRUE -); +);'); -- -- Section 2: State Statistics Tests (peak, total, merged) -- -- Test 2.1: Simple quantifier pattern - A+ with short matches (no merging) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -92,9 +129,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS v % 2 = 1 -); +);'); -- Test 2.2: Alternation pattern - multiple state branches +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -103,11 +141,12 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B | C) (D | E)) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); -- Test 2.3: Complex pattern with high state count +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -119,9 +158,10 @@ WINDOW w AS ( A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); +);'); -- Test 2.4: Grouped pattern with quantifier - state merging +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) @@ -130,10 +170,11 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- Test 2.5: State explosion pattern - many alternations -- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -142,9 +183,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- Test 2.6: High state merging - alternation with plus quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -153,9 +195,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B | C)+ D) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 -); +);'); -- Test 2.7: Nested quantifiers causing state growth +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) @@ -164,13 +207,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (((A | B)+)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 -); +);'); -- -- Section 3: Context Statistics Tests (peak, total, absorbed, skipped) -- -- Test 3.1: Context absorption with unbounded quantifier at start +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -179,9 +223,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 3.2: No absorption - bounded quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -190,9 +235,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 3.3: Contexts skipped by SKIP PAST LAST ROW +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -201,9 +247,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C) DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 -); +);'); -- Test 3.4: High context absorption - unbounded group +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -212,13 +259,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); +);'); -- -- Section 4: Match Length Statistics Tests -- -- Test 4.1: Fixed length matches - all same length +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -227,11 +275,12 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C D E) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); -- Test 4.2: Variable length matches - min/max/avg differ +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -240,9 +289,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); +);'); -- Test 4.3: Very long matches +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 200) AS s(v) @@ -251,9 +301,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v <= 195, B AS v > 195 -); +);'); -- Test 4.4: Mix of short and long matches +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -264,7 +315,7 @@ WINDOW w AS ( DEFINE A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15), B AS v % 20 = 0 -); +);'); -- -- Section 5: Mismatch Length Statistics Tests @@ -272,36 +323,38 @@ WINDOW w AS ( -- Test 5.1: Pattern that causes mismatches with length > 1 -- Mismatch happens when partial match fails after processing multiple rows +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( SELECT v, - CASE WHEN v % 10 IN (1,2,3) THEN 'A' - WHEN v % 10 IN (4,5) THEN 'B' - WHEN v % 10 = 6 THEN 'C' - ELSE 'X' END AS cat + CASE WHEN v % 10 IN (1,2,3) THEN ''A'' + WHEN v % 10 IN (4,5) THEN ''B'' + WHEN v % 10 = 6 THEN ''C'' + ELSE ''X'' END AS cat FROM generate_series(1, 100) AS s(v) ) t WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B+ C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); + DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' +);'); -- Test 5.2: Long partial matches that fail +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( SELECT i AS v, CASE - WHEN i <= 20 THEN 'A' - WHEN i <= 25 THEN 'B' - WHEN i = 26 THEN 'X' -- breaks the pattern - WHEN i <= 50 THEN 'A' - WHEN i <= 55 THEN 'B' - WHEN i = 56 THEN 'C' -- completes pattern - ELSE 'Y' + WHEN i <= 20 THEN ''A'' + WHEN i <= 25 THEN ''B'' + WHEN i = 26 THEN ''X'' -- breaks the pattern + WHEN i <= 50 THEN ''A'' + WHEN i <= 55 THEN ''B'' + WHEN i = 56 THEN ''C'' -- completes pattern + ELSE ''Y'' END AS cat FROM generate_series(1, 60) i ) t @@ -309,14 +362,15 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B+ C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); + DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' +);'); -- -- Section 6: JSON Format Tests -- -- Test 6.1: JSON format output with all statistics +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -325,9 +379,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 -); +)'); -- Test 6.2: JSON format with match length statistics +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -336,13 +391,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); +)'); -- -- Section 7: XML Format Tests -- -- Test 7.1: XML format output +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -351,13 +407,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +)'); -- -- Section 8: Multiple Partitions Tests -- -- Test 8.1: Statistics across multiple partitions +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( @@ -371,9 +428,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 8.2: Different pattern behavior per partition +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( @@ -388,13 +446,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS val < 5, B AS val >= 5 -); +);'); -- -- Section 9: Edge Cases -- -- Test 9.1: Empty result set +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 0) AS s(v) @@ -403,9 +462,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v = 1, B AS v = 2 -); +);'); -- Test 9.2: Single row +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1) AS s(v) @@ -414,9 +474,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A) DEFINE A AS TRUE -); +);'); -- Test 9.3: Pattern longer than data +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 5) AS s(v) @@ -427,9 +488,10 @@ WINDOW w AS ( DEFINE A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5, F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 -); +);'); -- Test 9.4: All rows match as single match +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -438,13 +500,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS TRUE -); +);'); -- -- Section 10: Complex Pattern Tests -- -- Test 10.1: Nested groups +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) @@ -453,9 +516,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (((A B) C)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); +);'); -- Test 10.2: Multiple alternations +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -464,11 +528,12 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) (C | D | E)) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); -- Test 10.3: Optional elements +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -477,9 +542,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B? C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 -); +);'); -- Test 10.4: Bounded quantifiers +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) @@ -488,9 +554,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{2,5} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); +);'); -- Test 10.5: Star quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -499,13 +566,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B* C) DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 -); +);'); -- -- Section 11: Real-world Pattern Examples -- -- Test 11.1: Stock price pattern - V-shape (down then up) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_complex @@ -513,10 +581,11 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (D+ U+) - DEFINE D AS trend = 'D', U AS trend = 'U' -); + DEFINE D AS trend = ''D'', U AS trend = ''U'' +);'); -- Test 11.2: Stock price pattern - peak (up, stable, down) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_complex @@ -524,16 +593,11 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (U+ S* D+) - DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D' -); + DEFINE U AS trend = ''U'', S AS trend = ''S'', D AS trend = ''D'' +);'); -- Test 11.3: Consecutive increasing values (using PREV) --- FIXME: The original pattern was: --- DEFINE A AS v > PREV(v) OR PREV(v) IS NULL --- This causes "ERROR: unrecognized node type: 15" (T_FuncExpr) because --- NullTest(FuncExpr(PREV)) is not properly handled somewhere in the planner. --- The expression v > PREV(v) works fine, but PREV(v) IS NULL fails. --- Using COALESCE(PREV(v), 0) as a workaround until the bug is fixed. +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -541,14 +605,15 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A{3,}) - DEFINE A AS v > COALESCE(PREV(v), 0) -); + DEFINE A AS v > PREV(v) OR PREV(v) IS NULL +);'); -- -- Section 12: Performance-oriented Tests -- -- Test 12.1: Large dataset with simple pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) @@ -557,9 +622,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- Test 12.2: Large dataset with absorption +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) @@ -568,9 +634,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 -); +);'); -- Test 12.3: High state merge ratio +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -579,13 +646,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); +);'); -- -- Section 13: INITIAL vs no INITIAL comparison -- -- Test 13.1: With INITIAL keyword +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -595,9 +663,10 @@ WINDOW w AS ( INITIAL PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 13.2: Without INITIAL keyword (same behavior currently) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -606,13 +675,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- -- Section 14: Quantifier Variations -- -- Test 14.1: Plus quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -621,9 +691,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+) DEFINE A AS v % 4 <> 0 -); +);'); -- Test 14.2: Star quantifier (zero or more) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -632,9 +703,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A* B) DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 -); +);'); -- Test 14.3: Question mark (zero or one) +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -643,9 +715,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A? B C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 -); +);'); -- Test 14.4: Exact count {n} +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -654,9 +727,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{3} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 14.5: Range {n,m} +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -665,9 +739,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 14.6: At least {n,} +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -676,7 +751,7 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A{3,} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 -); +);'); -- -- Section 15: Regression Tests for Statistics Accuracy @@ -684,6 +759,7 @@ WINDOW w AS ( -- Test 15.1: Verify state count accuracy -- Pattern A+ B with 20 rows should show predictable state behavior +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) @@ -692,9 +768,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 15.2: Verify context count with known absorption +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -703,9 +780,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B C) DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 -); +);'); -- Test 15.3: Verify match length with fixed-length pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -714,13 +792,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); +);'); -- -- Section 16: Alternation Pattern Tests -- -- Test 16.1: Simple alternation +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -728,10 +807,11 @@ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) C) - DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' -); + DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' +);'); -- Test 16.2: Multiple items in alternation +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM nfa_test @@ -740,11 +820,12 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B | C | D) E) DEFINE - A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', - D AS cat = 'D', E AS cat = 'E' -); + A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', + D AS cat = ''D'', E AS cat = ''E'' +);'); -- Test 16.3: Alternation with quantifiers +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -753,13 +834,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 -); +);'); -- -- Section 17: Group Pattern Tests -- -- Test 17.1: Simple group +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -768,9 +850,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- Test 17.2: Group with bounded quantifier +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) @@ -779,9 +862,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN ((A B){2,4}) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- Test 17.3: Nested groups +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) @@ -790,13 +874,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (((A B){2})+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- -- Section 18: Window Function Combinations -- -- Test 18.1: count(*) with pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -805,9 +890,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 18.2: first_value with pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT first_value(v) OVER w FROM generate_series(1, 30) AS s(v) @@ -816,9 +902,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 18.3: last_value with pattern +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT last_value(v) OVER w FROM generate_series(1, 30) AS s(v) @@ -827,9 +914,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- Test 18.4: Multiple window functions +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w, @@ -841,13 +929,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 -); +);'); -- -- Section 19: DEFINE Expression Variations -- -- Test 19.1: Complex boolean expressions +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) @@ -858,9 +947,10 @@ WINDOW w AS ( DEFINE A AS (v % 5 <> 0) AND (v % 3 <> 0), B AS (v % 5 = 0) OR (v % 3 = 0) -); +);'); -- Test 19.2: Using PREV function +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) @@ -872,9 +962,10 @@ WINDOW w AS ( S AS TRUE, U AS v > PREV(v), D AS v < PREV(v) -); +);'); -- Test 19.3: Using NULL comparisons +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM ( @@ -886,13 +977,14 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B) DEFINE A AS v IS NOT NULL, B AS v IS NULL -); +);'); -- -- Section 20: Large Scale Statistics Verification -- -- Test 20.1: 500 rows - verify statistics scale correctly +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -901,9 +993,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A+ B C) DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 -); +);'); -- Test 20.2: High match count scenario +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -912,9 +1005,10 @@ WINDOW w AS ( AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 -); +);'); -- Test 20.3: High skip count scenario +SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) @@ -928,7 +1022,7 @@ WINDOW w AS ( C AS v % 100 = 3, D AS v % 100 = 4, E AS v % 100 = 5 -); +);'); -- Cleanup DROP TABLE nfa_test; -- 2.50.1 (Apple Git-155)