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)
