-- A table filtered to a few rows and joined to a grouped sub-select on the key of its groups wants those groups only. The sub-select asks for them: its keys are looked up in
-- the filtered table first (a semi join), where the groups of the whole table cost a scan or a full index pass. The join above stays as written. The plan of the two forms
-- is set by cost; the rows must be the same as with the scan off, for keys that are NULL, that repeat in the filtered table and that have no row in it, with no worker and
-- with three.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE tenants (id int PRIMARY KEY, name text NOT NULL, set_id int);
CREATE UNIQUE INDEX tenants_name ON tenants (name);
INSERT INTO tenants SELECT g, 'tenant_' || lpad(g::text, 5, '0'), CASE WHEN g % 10 = 0 THEN NULL ELSE 1 + g % 5 END FROM generate_series(1, 4000) g;
-- a table whose key repeats: the filtered rows of a key come twice
CREATE TABLE marks (tenant_id int, label text);
INSERT INTO marks SELECT 1 + (g * 13) % 4000, CASE WHEN g % 2 = 0 THEN 'even' ELSE 'odd' END FROM generate_series(1, 6000) g;
CREATE INDEX marks_label ON marks (label);
-- link_id: NULL for a tenth of the rows and 1 to 400 for the rest
CREATE TABLE events (id int PRIMARY KEY, tenant_id int NOT NULL, category_id int NOT NULL, value_num numeric(10,2), link_id int) WITH (autovacuum_enabled = false);
INSERT INTO events SELECT g, 1 + (g * 7919) % 4000, 1 + g % 19, CASE WHEN g % 13 = 0 THEN NULL ELSE (g % 500) / 5.0 END, CASE WHEN g % 10 = 0 THEN NULL ELSE 1 + g % 400 END
       FROM generate_series(1, 60000) g;
-- tenants 3901 to 4000 have no event
DELETE FROM events WHERE tenant_id > 3900;
CREATE INDEX events_tenant ON events (tenant_id, category_id);
CREATE INDEX events_link ON events (link_id);
CREATE INDEX events_col ON events USING sift_col (id, tenant_id, category_id, value_num, link_id) WITH (key_cut = tenant_id);
VACUUM (FREEZE, ANALYZE) events, tenants, marks;

CREATE TABLE lookup_q (n int, label text, q text);
INSERT INTO lookup_q VALUES
	(1, 'one tenant by its name',            'SELECT t.name, a.n FROM (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a JOIN tenants t ON t.id = a.tenant_id WHERE t.name = ''tenant_01234'''),
	(2, 'a tenant without events',           'SELECT t.name, a.n FROM (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a JOIN tenants t ON t.id = a.tenant_id WHERE t.name = ''tenant_03950'''),
	(3, 'a range of names',                  'SELECT t.name, a.n, a.s FROM (SELECT tenant_id, count(*) AS n, sum(value_num) AS s FROM events GROUP BY 1) a JOIN tenants t ON t.id = a.tenant_id WHERE t.name BETWEEN ''tenant_00100'' AND ''tenant_00130'' ORDER BY 1'),
	(4, 'a fifth of the tenants',            'SELECT t.name, a.n FROM (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a JOIN tenants t ON t.id = a.tenant_id WHERE t.set_id = 3 ORDER BY 1'),
	(5, 'a key that repeats in the table',   'SELECT m.label, a.tenant_id, a.n FROM (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a JOIN marks m ON m.tenant_id = a.tenant_id WHERE m.label = ''odd'' AND m.tenant_id < 40 ORDER BY 2, 1'),
	(6, 'NULL keys, equal groups',           'SELECT t.name, a.n FROM (SELECT link_id, count(*) AS n FROM events GROUP BY 1) a JOIN tenants t ON t.id = a.link_id WHERE t.name < ''tenant_00005'' ORDER BY 1'),
	(7, 'a key that is an expression',       'SELECT t.name, a.n FROM (SELECT tenant_id + 0 AS k, count(*) AS n FROM events GROUP BY 1) a JOIN tenants t ON t.id = a.k WHERE t.name = ''tenant_02500'''),
	(8, 'two tables filtered',               'SELECT t.name, a.n, b.s FROM (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a JOIN (SELECT tenant_id, sum(value_num) AS s FROM events GROUP BY 1) b ON b.tenant_id = a.tenant_id JOIN tenants t ON t.id = a.tenant_id WHERE t.name = ''tenant_00777'''),
	(9, 'a having and an order with a limit', 'SELECT t.name, a.n FROM (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1 HAVING count(*) > 10) a JOIN tenants t ON t.id = a.tenant_id WHERE t.set_id = 2 ORDER BY a.n DESC, t.name LIMIT 5'),
	(10, 'a left join keeps the groups',     'SELECT t.name, a.n FROM tenants t LEFT JOIN (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a ON t.id = a.tenant_id WHERE t.name = ''tenant_03950'''),
	(11, 'the table on the other side',      'SELECT t.name, a.n FROM tenants t JOIN (SELECT tenant_id, count(*) AS n FROM events GROUP BY 1) a ON a.tenant_id = t.id WHERE t.name IN (''tenant_00042'', ''tenant_03999'', ''tenant_00043'')  ORDER BY 1');
-- the plan of each statement: the keys looked up first (the table events is read by its indexes, no scan), the scan (SiftAgg), or the normal plan; the rows of the two equal
CREATE FUNCTION lookup_plan(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; scans int := 0; probes int := 0;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		scans := scans + (l LIKE '%Custom Scan (SiftAgg)%')::int;
		probes := probes + (l LIKE '%Index%events_tenant%' AND l LIKE '%Cond%' OR l LIKE '%Index%Scan%events_tenant%')::int;
	END LOOP;
	RETURN CASE WHEN scans > 0 THEN 'scan x' || scans WHEN probes > 0 THEN 'index lookups' ELSE 'normal' END;
END $$;
CREATE FUNCTION lookup_rows(q text, scan bool, workers int) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.rows_agg_with_workers_reset(q, scan, workers) $$;
SELECT n, label, lookup_plan(q) AS plan, lookup_rows(q, false, 0) <> '' AS has_rows, lookup_rows(q, true, 0) = lookup_rows(q, false, 0) AS same, lookup_rows(q, true, 3) = lookup_rows(q, false, 0) AS same_workers FROM lookup_q ORDER BY n;

DROP FUNCTION lookup_plan(text);
DROP FUNCTION lookup_rows(text, bool, int);
DROP TABLE lookup_q, events, marks, tenants;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;