rpr_explain.out
application/octet-stream
Filename: rpr_explain.out
Type: application/octet-stream
Part: 0
Message:
Re: Row pattern recognition
--
-- 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;