-- A CTE that three aggregates read (the arms of a UNION ALL, each over the elements of a jsonb array, the rows then counted DISTINCT above a spine) is planned
-- inline when the scans take over the work of the shared CTE for less than it costs. The plan keeps the three SiftAgg arms below one SiftAggRows node, which
-- costs far less than the arms added up (the node reads the table once), so the choice takes the cost of the node, not the sum of the arms it fused.
-- It compares the scans with what the shared plan spends on the same work, not the totals of the two plans: the plan above them (a join of the arms to the
-- spine on an expression key, a window) is priced from the rows each plan expects of the arms, and the inline plan expects more. At 100,000 rows the
-- totals alone keep the shared CTE, which takes three times as long.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
\set rows 100000
\ir test/agg_cte_inline_fixture.sql
-- The events, dims and maps tables, the statement of three arms over a shared CTE and the check of its rows against the normal plan, shared by
-- agg_cte_inline_rows, _band and _horizon; psql variable rows is the number of events.
\set ECHO none
-- the nodes of the scan in the plan, and the rows against the normal plan
CREATE FUNCTION scan_nodes(q text) RETURNS int LANGUAGE plpgsql AS $$
DECLARE l text; n int := 0;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP IF l ~ 'Custom Scan \(SiftAgg' THEN n := n + 1; END IF; END LOOP;
	RETURN n;
END $$;
-- the shared CTE takes the scan (the arms and the SiftAggRows node above them), and the rows are the normal plan's
SELECT scan_nodes(series_query()) AS scan_nodes, differences(series_query()) AS differences;
 scan_nodes | differences 
------------+-------------
          4 |           0
(1 row)

DROP FUNCTION differences, scan_nodes, series_query;
DROP TABLE events, dims, maps;
DROP EXTENSION pg_sift;