-- a join to a large btree table on a range of the scan's keys, probed in its btree for each key the scan meets, with the rows of an all-visible page taken from the entries
-- label: {rows} * 1.5 events of 3,000 parents, a links table of 5 * {rows} rows (about 333 per parent), `JOIN links ON parent_id = e.foreign_id AND status = 0 AND weight = 1` for 60 parents of a range, btree on `(parent_id, status) INCLUDE (label, weight)`
-- ordered: yes
--@ setup
DO $do$ BEGIN
	IF to_regclass('pj_events') IS NULL THEN
		CREATE TABLE pj_events (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, foreign_id int NOT NULL, kind int NOT NULL, deleted_at timestamptz);
		INSERT INTO pj_events (foreign_id, kind) SELECT 1 + (random() * 2999)::int, g % 7 FROM generate_series(1, {rows} * 3 / 2) g;
		CREATE TABLE pj_links (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, parent_id int NOT NULL, label text NOT NULL, status smallint NOT NULL, weight int NOT NULL);
		INSERT INTO pj_links (parent_id, label, status, weight) SELECT 1 + g % 3000, 'lbl_' || g / 3000 % 10, g / 3000 % 5, g % 4 FROM generate_series(1, {rows} * 5) g ORDER BY random();
		CREATE INDEX pj_links_parent_status ON pj_links (parent_id, status) INCLUDE (label, weight);
		CREATE INDEX pj_events_col ON pj_events USING sift_col (id, foreign_id, kind, deleted_at) WITH (key_cut = foreign_id);
	END IF;
END $do$;
VACUUM (FREEZE, ANALYZE) pj_events, pj_links
--@ pg
SELECT l.label, count(*) AS n, sum(e.kind) AS s FROM pj_events e JOIN pj_links l ON l.parent_id = e.foreign_id AND l.status = 0 AND l.weight = 1
WHERE e.foreign_id BETWEEN 100 AND 159 AND e.deleted_at IS NULL GROUP BY 1 ORDER BY 1