-- The decisions CREATE INDEX makes before it reads the table (key_cut, key_order, the point directory, the arrays, the paths) read the row estimate of pg_class:
-- a table of 250 million, a billion, four billion rows and the most a float holds must decide as it does, with no failure, no allocation by rows and no product
-- that wraps. The estimate is set on a small table (the build writes the real one back when it ends); the sampled rows are the same at every scale.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers_per_gather = 0;

-- ds: 100 tenants scattered over the heap; ref_id is unique and in no order (not the primary key); the dates are scattered; tags are arrays that differ
CREATE TABLE ds (id bigint PRIMARY KEY, tenant_id int, ref_id int, d date, tags int[], v int, pad text) WITH (fillfactor = 10, autovacuum_enabled = false);
SELECT setseed(0.5);
INSERT INTO ds SELECT i, i % 101, (i * 7919) % 60013, date '2020-01-01' + (random() * 1500)::int, ARRAY[i, i % 7, (random() * 100000)::int], (random() * 1000)::int, repeat('x', 100)
FROM generate_series(1, 60000) i;
VACUUM (FREEZE, ANALYZE) ds;
UPDATE pg_statistic SET stadistinct = -1 WHERE starelid = 'ds'::regclass AND staattnum = (SELECT attnum FROM pg_attribute WHERE attrelid = 'ds'::regclass AND attname = 'ref_id');
-- The 101 values of tenant_id have one share each, so which of them ANALYZE lists as most common is sampling noise; one listed at about 1.1% is, of 3.4e38 rows, a value no
-- segment holds, and the key is left for another column (the hot value rule). The statistics here list none, so that the decision at every scale reads the sample, which is fixed.
UPDATE pg_statistic SET stakind1 = CASE WHEN stakind1 = 1 THEN 0 ELSE stakind1 END, stakind2 = CASE WHEN stakind2 = 1 THEN 0 ELSE stakind2 END,
	stakind3 = CASE WHEN stakind3 = 1 THEN 0 ELSE stakind3 END, stakind4 = CASE WHEN stakind4 = 1 THEN 0 ELSE stakind4 END, stakind5 = CASE WHEN stakind5 = 1 THEN 0 ELSE stakind5 END
	WHERE starelid = 'ds'::regclass AND staattnum = (SELECT attnum FROM pg_attribute WHERE attrelid = 'ds'::regclass AND attname = 'tenant_id');

-- dj: the documents are maps of 100 keys out of 5,000: a sample of them names thousands of keys
CREATE TABLE dj (id int PRIMARY KEY, doc jsonb, pad text) WITH (fillfactor = 10, autovacuum_enabled = false);
INSERT INTO dj SELECT i, (SELECT jsonb_object_agg('f' || ((i * 37 + j * 51) % 5000), j) FROM generate_series(1, 100) j), repeat('x', 100) FROM generate_series(1, 6000) i;
VACUUM (FREEZE, ANALYZE) dj;

CREATE FUNCTION kscale(tbl regclass, cols text, estimate float4) RETURNS TABLE (key_cut text, key_order text, point_tid text, paths text) LANGUAGE plpgsql AS $$
DECLARE ix text := 'kscale_ix';
BEGIN
	UPDATE pg_class SET reltuples = estimate WHERE oid = tbl;
	EXECUTE format('CREATE INDEX %I ON %s USING sift_col (%s)', ix, tbl, cols);
	RETURN QUERY SELECT coalesce(max(substring(o from '^key_cut=(.*)$')), ''), coalesce(max(substring(o from '^key_order=(.*)$')), ''),
		coalesce(max(substring(o from '^point_tid_columns=(.*)$')), ''), coalesce(max(substring(o from '^paths=(.*)$')), '')
		FROM pg_class c, unnest(c.reloptions) o WHERE c.relname = ix;
	EXECUTE format('DROP INDEX %I', ix);
END $$;

-- the same choices at every scale of the estimate (the real one is 60,000 rows)
SELECT e AS estimate, k.* FROM unnest(ARRAY[60000, 257000000, 1000000000, 4300000000, 3.4e38]::float4[]) e,
	LATERAL kscale('ds', 'id, tenant_id, ref_id, d, tags, v', e) k ORDER BY e;
SELECT e AS estimate, k.paths FROM unnest(ARRAY[6000, 257000000, 1000000000]::float4[]) e, LATERAL kscale('dj', 'id, doc', e) k ORDER BY e;

-- a heap of an estimate no run count holds is refused without key_cut, whether or not the estimate fits 64 bits (PostgreSQL 17 stores the rows a CREATE INDEX saw in
-- reltuples, so the estimate is set again after the builds of kscale)
UPDATE pg_class SET reltuples = 3.4e38 WHERE relname = 'ds';
CREATE INDEX ds_big ON ds USING sift_col (id, tenant_id) WITH (key_cut = 'off');
UPDATE pg_class SET reltuples = 60000 WHERE relname = 'ds';

-- sift.key_cut_decision reads the estimate too: the sample is the same, the verdict is
CREATE INDEX ds_ix ON ds USING sift_col (id, tenant_id, ref_id, d, tags, v) WITH (key_cut = 'off');
CREATE FUNCTION kdecide(estimate float4) RETURNS TABLE (leaf text, sampled float8, scattered boolean, decision text) LANGUAGE plpgsql AS $$
BEGIN
	UPDATE pg_class SET reltuples = estimate WHERE relname = 'ds';
	RETURN QUERY SELECT d.leaf, d.sampled, d.reads > d.ideal, d.decision FROM sift.key_cut_decision('ds_ix') d;
END $$;
SELECT e AS estimate, k.* FROM unnest(ARRAY[60000, 257000000, 1000000000, 4300000000, 3.4e38]::float4[]) e, LATERAL kdecide(e) k ORDER BY e;
DROP FUNCTION kdecide(float4);

DROP FUNCTION kscale(regclass, text, float4);
DROP TABLE ds, dj;
DROP EXTENSION pg_sift;