-- A correlated LIMIT 1 child is probed in its btree for each distinct key of the scan (a run: 6,000 to 8,000 instructions, 12,000 to 13,000 under a collation other than C), or the
-- table it reads is loaded whole (1,000 instructions a row) and the keys looked up in it. The probes win up to about a seventh of the rows of the table as keys
-- (a twelfth under an ICU collation); the statements pin both sides for an integer key and a text key under an ICU collation, and the one number of keys between the two
-- crossovers where the two keys choose differently (calibration table in docs/AGG.md).
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET jit = off;
SET max_parallel_workers_per_gather = 0;
SET sift.agg_force = on;
SET sift.agg_lookup_cache_kb = 0;
SET sift.agg_lookup_shared_kb = 0;
CREATE TABLE ev (id int, fk int, fku text COLLATE "und-x-icu") WITH (autovacuum_enabled = false);
INSERT INTO ev SELECT i, i, 'k' || i FROM generate_series(1, 20000) i;
CREATE INDEX ev_col ON ev USING sift_col (id, fk, fku);
CREATE TABLE dm_i (k int PRIMARY KEY, label text) WITH (autovacuum_enabled = false);
INSERT INTO dm_i SELECT i, 'l' || i % 11 FROM generate_series(1, 20000) i;
CREATE TABLE dm_u (k text COLLATE "und-x-icu" PRIMARY KEY, label text) WITH (autovacuum_enabled = false);
INSERT INTO dm_u SELECT 'k' || i, 'l' || i % 11 FROM generate_series(1, 20000) i;
VACUUM (FREEZE, ANALYZE) ev, dm_i, dm_u;
-- how the scan reads the lookup table for the keys 1 to n of the facts: probes of its btree, or the table whole
CREATE FUNCTION lookup_form(tab text, col text, n int) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; r text := 'none';
BEGIN
	FOR l IN EXECUTE format('EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT d.label, count(*) FROM ev LEFT JOIN LATERAL (SELECT label FROM %s x WHERE x.k = ev.%s LIMIT 1) d ON true WHERE ev.fk <= %s GROUP BY 1', tab, col, n) LOOP
		IF l ~ 'child direct probes' THEN r := 'probes'; ELSIF l ~ 'first row tables' THEN r := 'whole'; END IF;
	END LOOP;
	RETURN r;
END $$;
SELECT n, lookup_form('dm_i', 'fk', n) AS integer_key, lookup_form('dm_u', 'fku', n) AS icu_key
FROM unnest(ARRAY[200, 1000, 2100, 6000, 12000]) n;
   n   | integer_key | icu_key 
-------+-------------+---------
   200 | probes      | probes
  1000 | probes      | probes
  2100 | probes      | whole
  6000 | whole       | whole
 12000 | whole       | whole
(5 rows)

DROP FUNCTION lookup_form(text, text, int);
DROP TABLE ev, dm_i, dm_u;
DROP EXTENSION pg_sift;