-- The aggregates of a parent left-joined to its children, made for each key of the children first and added up over the parents
-- label: `SELECT grp, count(iv_events.id), sum(iv_events.kind) FROM iv_dims LEFT JOIN iv_events ON iv_events.dim_id = iv_dims.id GROUP BY grp ORDER BY 1` over the rows of the dataset and a tenth as many parents joined by a foreign key, the index of the children connected to the parent and filled; `join` is the same statement with sift.agg_child_first off (the parents are the rows scanned and the children a table loaded for the left join)
-- ordered: yes
-- set: sift.agg_force = on; sift.agg_lookup_cache_kb = 0; sift.agg_lookup_shared_kb = 0
--@ setup
CREATE TABLE iv_dims (id int PRIMARY KEY, grp int);
INSERT INTO iv_dims SELECT g, g % 50 FROM generate_series(1, {rows} / 10) g;
CREATE INDEX iv_dims_col ON iv_dims USING sift_col (id, grp);
CREATE TABLE iv_events (id int PRIMARY KEY, dim_id int REFERENCES iv_dims (id), kind int);
INSERT INTO iv_events SELECT g, CASE WHEN g % 100 = 0 THEN NULL ELSE 1 + (g::bigint * 7919) % ({rows} / 10) END, g % 1000 FROM generate_series(1, {rows}) g;
CREATE INDEX iv_events_col ON iv_events USING sift_col (id, dim_id, kind);
VACUUM (FREEZE, ANALYZE) iv_dims, iv_events;
SELECT sift.col_maintain('iv_events_col');
VACUUM ANALYZE iv_dims
--@ pg
SELECT grp, count(iv_events.id), sum(iv_events.kind) FROM iv_dims LEFT JOIN iv_events ON iv_events.dim_id = iv_dims.id GROUP BY grp ORDER BY 1