-- A plan counts the pre-aggregate sections of the units of an index (reading the cubes of a sample of the units of every segment) only to price an aggregate that
-- a pre-aggregate answers: a statement that does not (a top-N walk, a point lookup) reads no census, a priced one reads it once for each state of the index, and
-- a backend that connects later takes it from the shared ring. sift._test_col_pre_folds says how many the backend folded and how many it took.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET max_parallel_maintenance_workers = 0;
SET cpu_tuple_cost = 0.5;
SET sift.enable_agg = on;
CREATE TABLE pl (id int, cat int, ts timestamp, amt int) WITH (autovacuum_enabled = off);
INSERT INTO pl SELECT g, g % 7, timestamp '2026-01-01' + g * interval '1 minute', g % 100 FROM generate_series(1, 30000) g;
CREATE INDEX pl_idx ON pl USING sift_col (id, cat, ts, amt) WITH (order_columns = 'ts', order_array_columns = 'ts');
VACUUM (FREEZE, ANALYZE) pl;
\connect
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET cpu_tuple_cost = 0.5;
SET sift.enable_agg = on;
-- plans that price no pre-aggregate answer
EXPLAIN (COSTS OFF) SELECT id FROM pl ORDER BY ts LIMIT 5;
         QUERY PLAN          
-----------------------------
 Limit
   ->  Custom Scan (SiftAgg)
         Index: pl_idx
         Top-N: ts (limit 5)
(4 rows)

EXPLAIN (COSTS OFF) SELECT id FROM pl WHERE id = 5;
             QUERY PLAN             
------------------------------------
 Index Only Scan using pl_idx on pl
   Index Cond: (id = 5)
(2 rows)

SELECT built, shared FROM sift._test_col_pre_folds();
 built | shared 
-------+--------
     0 |      0
(1 row)

-- an aggregate that a pre-aggregate answers: counted once, the same plan again counts nothing
EXPLAIN (COSTS OFF) SELECT cat, count(*), sum(amt) FROM pl GROUP BY cat;
               QUERY PLAN                
-----------------------------------------
 Custom Scan (SiftAgg)
   Index: pl_idx
   Group Keys: cat
   Pushed Aggregates: count(*), sum(amt)
(4 rows)

EXPLAIN (COSTS OFF) SELECT cat, count(*), sum(amt) FROM pl GROUP BY cat;
               QUERY PLAN                
-----------------------------------------
 Custom Scan (SiftAgg)
   Index: pl_idx
   Group Keys: cat
   Pushed Aggregates: count(*), sum(amt)
(4 rows)

SELECT built, shared FROM sift._test_col_pre_folds();
 built | shared 
-------+--------
     1 |      0
(1 row)

-- another backend takes the count of the first
\connect
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET cpu_tuple_cost = 0.5;
SET sift.enable_agg = on;
EXPLAIN (COSTS OFF) SELECT cat, count(*), sum(amt) FROM pl GROUP BY cat;
               QUERY PLAN                
-----------------------------------------
 Custom Scan (SiftAgg)
   Index: pl_idx
   Group Keys: cat
   Pushed Aggregates: count(*), sum(amt)
(4 rows)

SELECT built, shared FROM sift._test_col_pre_folds();
 built | shared 
-------+--------
     0 |      1
(1 row)

-- a new segment is a new state of the index: counted again
INSERT INTO pl SELECT g, g % 7, timestamp '2026-01-01' + g * interval '1 minute', g % 100 FROM generate_series(30001, 40000) g;
SELECT sift.col_promote('pl_idx') > 0 AS promoted;
 promoted 
----------
 t
(1 row)

\connect
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET cpu_tuple_cost = 0.5;
SET sift.enable_agg = on;
EXPLAIN (COSTS OFF) SELECT id FROM pl ORDER BY ts LIMIT 5;
         QUERY PLAN          
-----------------------------
 Limit
   ->  Custom Scan (SiftAgg)
         Index: pl_idx
         Top-N: ts (limit 5)
(4 rows)

SELECT built, shared FROM sift._test_col_pre_folds();
 built | shared 
-------+--------
     0 |      0
(1 row)

EXPLAIN (COSTS OFF) SELECT cat, count(*), sum(amt) FROM pl GROUP BY cat;
               QUERY PLAN                
-----------------------------------------
 Custom Scan (SiftAgg)
   Index: pl_idx
   Group Keys: cat
   Pushed Aggregates: count(*), sum(amt)
(4 rows)

SELECT built, shared FROM sift._test_col_pre_folds();
 built | shared 
-------+--------
     1 |      0
(1 row)

DROP TABLE pl;
DROP EXTENSION pg_sift_test, pg_sift CASCADE;