public inbox for [email protected]
help / color / mirror / Atom feedFrom: Henson Choi <[email protected]>
To: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Thu, 5 Feb 2026 23:28:37 +0900
Message-ID: <CAAAe_zDKsfzrLwvrMTwdHwzq9v38g3Tn1UC=excb0RFntqqh3w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAAAe_zC95wT9EdBu_nBn6mWVB7-xNNuJ4N8wPr0LrWJBdwmxiQ@mail.gmail.com>
<[email protected]>
<CAAAe_zA=BDm=e04aNFYe3Az0CzPipK+HHzu1KGHiLOg+r_z8XQ@mail.gmail.com>
<[email protected]>
Hi Tatsuo,
> Done. Please see the attached patch.
>
> Looks good to me.
>
Thank you for reviewing.
> I changed
> DEFINE A AS v > COALESCE(PREV(v), 0)
> to
> DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
> and get following result.
>
> 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 > PREV(v) OR PREV(v) IS NULL
> );
> 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)
>
> Probably we can restore 11.3 test in v43?
>
Yes, I've already restored Test 11.3 with the original pattern:
DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
Additionally, I've implemented cross-platform normalization for Storage
memory values
to address the platform differences (18kB vs 19kB, 23kB vs 24kB, etc.)
observed between
macOS and Rocky Linux 10.
The attached patch includes:
1. Test 11.3 restoration with original PREV(v) IS NULL pattern
2. Cross-platform Storage normalization using rpr_explain_filter() PL/pgSQL
function
- Normalizes text format: "Maximum Storage: 18kB" -> "Maximum Storage:
NkB"
- Normalizes JSON format: "Maximum Storage": 17 -> "Maximum Storage": 0
- Normalizes XML format: <Maximum-Storage>17</Maximum-Storage> ->
<Maximum-Storage>0</Maximum-Storage>
- Preserves NFA statistics unchanged (they are test assertions)
3. All EXPLAIN statements wrapped with rpr_explain_filter()
4. Removed rpr_explain_1.out (no longer needed)
5. Added rpr_explain to parallel_schedule test suite
Patch statistics:
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
Best regards,
Henson
From f3097f9c82b25cb5ed60caa3241e58935e797535 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
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: <Maximum-Storage>17</Maximum-Storage> (number in kB units)
+ if ln ~ '<Maximum-Storage>' then
+ ln := regexp_replace(ln, '<Maximum-Storage>\d+</Maximum-Storage>', '<Maximum-Storage>0</Maximum-Storage>', '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
--------------------------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain"; +
<Query> +
@@ -682,7 +737,7 @@ WINDOW w AS (
<Window>w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)</Window>+
<Pattern>a b</Pattern> +
<Storage>Memory</Storage> +
- <Maximum-Storage>17</Maximum-Storage> +
+ <Maximum-Storage>0</Maximum-Storage> +
<NFA-States-Peak>2</NFA-States-Peak> +
<NFA-States-Total>31</NFA-States-Total> +
<NFA-States-Merged>0</NFA-States-Merged> +
@@ -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
---------------------------------------------------------------------------------
- <explain xmlns="http://www.postgresql.org/2009/explain"; +
- <Query> +
- <Plan> +
- <Node-Type>WindowAgg</Node-Type> +
- <Parallel-Aware>false</Parallel-Aware> +
- <Async-Capable>false</Async-Capable> +
- <Actual-Rows>30.00</Actual-Rows> +
- <Actual-Loops>1</Actual-Loops> +
- <Disabled>false</Disabled> +
- <Window>w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)</Window>+
- <Pattern>a b</Pattern> +
- <Storage>Memory</Storage> +
- <Maximum-Storage>17</Maximum-Storage> +
- <NFA-States-Peak>2</NFA-States-Peak> +
- <NFA-States-Total>31</NFA-States-Total> +
- <NFA-States-Merged>0</NFA-States-Merged> +
- <NFA-Contexts-Peak>3</NFA-Contexts-Peak> +
- <NFA-Contexts-Total>31</NFA-Contexts-Total> +
- <NFA-Contexts-Absorbed>0</NFA-Contexts-Absorbed> +
- <NFA-Contexts-Skipped>0</NFA-Contexts-Skipped> +
- <NFA-Contexts-Pruned>15</NFA-Contexts-Pruned> +
- <NFA-Matched>15</NFA-Matched> +
- <NFA-Mismatched>0</NFA-Mismatched> +
- <NFA-Match-Length-Min>2</NFA-Match-Length-Min> +
- <NFA-Match-Length-Max>2</NFA-Match-Length-Max> +
- <NFA-Match-Length-Avg>2.0</NFA-Match-Length-Avg> +
- <Plans> +
- <Plan> +
- <Node-Type>Function Scan</Node-Type> +
- <Parent-Relationship>Outer</Parent-Relationship> +
- <Parallel-Aware>false</Parallel-Aware> +
- <Async-Capable>false</Async-Capable> +
- <Function-Name>generate_series</Function-Name> +
- <Alias>s</Alias> +
- <Actual-Rows>30.00</Actual-Rows> +
- <Actual-Loops>1</Actual-Loops> +
- <Disabled>false</Disabled> +
- </Plan> +
- </Plans> +
- </Plan> +
- <Triggers> +
- </Triggers> +
- </Query> +
- </explain>
-(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: <Maximum-Storage>17</Maximum-Storage> (number in kB units)
+ if ln ~ '<Maximum-Storage>' then
+ ln := regexp_replace(ln, '<Maximum-Storage>\d+</Maximum-Storage>', '<Maximum-Storage>0</Maximum-Storage>', '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)
Attachments:
[text/plain] fixme-and-storage.txt (175.1K, 3-fixme-and-storage.txt)
download
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Row pattern recognition
In-Reply-To: <CAAAe_zDKsfzrLwvrMTwdHwzq9v38g3Tn1UC=excb0RFntqqh3w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox