Re: Row pattern recognition - Mailing list pgsql-hackers

While looking into EXPLAIN VERBOSE ANALYZE output of a RPR defined
query, I noticed that the "Output" row of the explain command includes
columns from DEFINE clause (price). This is because columns referenced
in the DEFINE clause must appear on the target list. It is the same
situation as a target list which does not include a column used by
ORDER BY clause like "SELECT price FROM stock ORDER by company".  See
the discussion:
https://www.postgresql.org/message-id/13494.1250901451%40sss.pgh.pa.us

So I think it's ok for now. Opinions?

explain analyze verbose
SELECT company, tdate, price, count(*) OVER w
 FROM stock
 WINDOW w AS (
 PARTITION BY company
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 INITIAL
 PATTERN (A{,2} )
 DEFINE
  A AS price = 200 OR price = 140,
  B AS price = 150
);
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=83.46..113.37 rows=1200 width=50) (actual time=0.017..0.031 rows=10.00 loops=1)
   Output: company, tdate, price, count(*) OVER w, ((price = 200) OR (price = 140)), (price = 150)
   Window: w AS (PARTITION BY stock.company ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
   Pattern: a{0,2}
   Storage: Memory  Maximum Storage: 17kB
   NFA States: 3 peak, 13 total, 0 merged
   NFA Contexts: 3 peak, 11 total, 8 pruned
   NFA: 2 matched (len 1/1/1), 0 mismatched
   Buffers: shared hit=1
   ->  Sort  (cost=83.37..86.37 rows=1200 width=40) (actual time=0.008..0.009 rows=10.00 loops=1)
         Output: company, tdate, price
         Sort Key: stock.company
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1
         ->  Seq Scan on public.stock  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.003..0.003 rows=10.00 loops=1)
               Output: company, tdate, price
               Buffers: shared hit=1
 Planning Time: 0.023 ms
 Execution Time: 0.050 ms
(19 rows)

SQL: DEFINE A AS price = 200

    [PARSE]
    ResTarget { name: "A", val: "price = 200" }
        ↓ findTargetlistEntrySQL99()
    Added to query targetlist
        ↓
    [PLAN]
    WindowAgg.defineClause = List<TargetEntry>
        ↓
    [RUNTIME]
    ExecEvalExpr() called for EACH ROW
        ↓
    varMatched[A] = true/false → used by NFA pattern matching