nocfbot-0069-Move-RPR-EXPLAIN-marker-description-and-add-a-wor.txt

From 418cb3e61e0421dc3b0601523cde51fe69c94ff7 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Wed, 10 Jun 2026 16:21:08 +0900
Subject: [PATCH 69/77] Move RPR EXPLAIN marker description and add a worked
 example

The paragraph describing the pattern absorption markers ("a+\"" and
"(a' b')+\"") that EXPLAIN may print for Row Pattern Recognition was
located in the Window Functions tutorial, which is not where readers
look for EXPLAIN output details.  Move it to the "Using EXPLAIN"
section and illustrate it with an actual EXPLAIN plan over the stock
table, so the markers are shown in real output rather than described
in the abstract.
---
 doc/src/sgml/advanced.sgml | 14 ------------
 doc/src/sgml/perform.sgml  | 44 ++++++++++++++++++++++++++++++++++++++
 2 files changed, 44 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 1410a443609..bdc552ad75f 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -676,20 +676,6 @@ FROM stock
     O(n<superscript>2</superscript>) to O(n) for many common patterns.
    </para>
 
-   <para>
-    When examining query plans for Row Pattern Recognition with
-    <command>EXPLAIN</command>, the pattern output may include special
-    markers that indicate optimization opportunities. A double quote
-    <literal>"</literal> marks where pattern absorption can occur,
-    and a single quote <literal>'</literal> marks absorbable elements
-    within a branch. For example, <literal>a+"</literal> indicates that
-    repeated matches of <literal>a</literal> can be absorbed, while
-    <literal>(a' b')+"</literal> shows that both <literal>a</literal>
-    and <literal>b</literal> within the group are absorbable.
-    These markers are primarily useful for understanding internal
-    optimization behavior.
-   </para>
-
    <para>
     When a query involves multiple window functions, it is possible to write
     out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 604e8578a8d..01a83ab105d 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -701,6 +701,50 @@ FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);
     happen without the sub-<literal>SELECT</literal> construct.
    </para>
 
+   <para>
+    When examining query plans for Row Pattern Recognition with
+    <command>EXPLAIN</command>, the pattern output may include special
+    markers that indicate optimization opportunities. A double quote
+    <literal>"</literal> marks where pattern absorption can occur,
+    and a single quote <literal>'</literal> marks absorbable elements
+    within a branch. For example, using the <structname>stock</structname>
+    table from <xref linkend="tutorial-window"/>, a query that looks for
+    repeated up-then-down movements reports its pattern as
+    <literal>(up' down')+"</literal>:
+
+<screen>
+EXPLAIN (COSTS OFF)
+SELECT company, tdate, price, count(price) OVER w
+FROM stock
+WINDOW w AS (
+    PARTITION BY company
+    ORDER BY tdate
+    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    AFTER MATCH SKIP PAST LAST ROW
+    INITIAL
+    PATTERN ((UP DOWN)+)
+    DEFINE UP AS price &gt; PREV(price),
+           DOWN AS price &lt; PREV(price)
+);
+
+                                              QUERY PLAN
+-------------------------------------------------------------------&zwsp;------------------------------------
+ WindowAgg
+   Window: w AS (PARTITION BY company ORDER BY tdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+   Pattern: (up' down')+"
+   Nav Mark Lookback: 1
+   -&gt;  Sort
+         Sort Key: company, tdate
+         -&gt;  Seq Scan on stock
+</screen>
+
+    Here the single quotes mark <literal>up</literal> and
+    <literal>down</literal> as absorbable within the group, while the
+    trailing double quote marks the repeated group itself as absorbable.
+    These markers are primarily useful for understanding internal
+    optimization behavior.
+   </para>
+
   </sect2>
 
   <sect2 id="using-explain-analyze">
-- 
2.50.1 (Apple Git-155)