-- A correlated LIMIT 1 lookup of a table by a unique key is read by the scan as a lookup table (its rows hashed by key); the rows are those
-- of PostgreSQL's plan of it. A key the table can repeat is read the same way only where the rows of a key agree on the columns read, and the child runs
-- for the other keys (test agg_first_row_dup). An order in the child, a condition that is not a comparison with a constant or a key that is not an equality keep the child.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
-- a LATERAL child can only run as a nested loop: without one the scan is the plan, with the child or its table as lookup
SET enable_nestloop = off;
CREATE TABLE facts (id int, a int, b int, k text, v text, t text, amount int, w float8);
INSERT INTO facts
SELECT i, i % 200, i % 101, 'k' || (i % 200), 'v' || (i % 101), 't' || ((i % 200) % 7), i % 1000, i % 1000 + 0.25 * (i % 4) FROM generate_series(1, 20000) i;
-- NULL keys, and keys the tables do not have
INSERT INTO facts VALUES (20001, NULL, 1, 'k1', 'v1', 't1', 1), (20002, 1, NULL, 'k1', NULL, 't1', 2), (20003, 500, 1, 'zz', 'v1', 't1', 3),
 (20004, 1, 500, 'k1', 'zz', 'zz', 4), (20005, NULL, NULL, NULL, NULL, NULL, 5);
CREATE INDEX facts_col ON facts USING sift_col (id, a, b, k, v, t, amount, w);
CREATE TABLE dims (a int, b int, label text, score int);
INSERT INTO dims SELECT x, y, 'l' || x || '_' || y, x * 3 + y FROM generate_series(0, 199) x, generate_series(0, 100) y;
CREATE UNIQUE INDEX dims_key ON dims (a, b);
CREATE TABLE dims_text (k varchar, v text, label text, score int);
INSERT INTO dims_text SELECT 'k' || x, 'v' || y, 'l' || x || '_' || y, x * 3 + y FROM generate_series(0, 199) x, generate_series(0, 100) y;
CREATE UNIQUE INDEX dims_text_key ON dims_text (k, v);
CREATE TABLE dims3 (a int, b int, t text, label text, score int);
INSERT INTO dims3 SELECT x, y, 't' || (x % 7), 'l' || x || '_' || y, x * 3 + y FROM generate_series(0, 199) x, generate_series(0, 100) y;
CREATE UNIQUE INDEX dims3_key ON dims3 (a, b, t);
-- the same rows with a key some of them repeat, and the same table with its unique index on a column the child does not compare
CREATE TABLE dims_dup (a int, b int, label text, score int);
INSERT INTO dims_dup SELECT * FROM dims;
INSERT INTO dims_dup SELECT a, b, 'again', score + 1000 FROM dims WHERE a < 20;
CREATE INDEX dims_dup_key ON dims_dup (a, b);
CREATE TABLE dims_id (id int, a int, b int, label text, score int);
INSERT INTO dims_id SELECT row_number() OVER (), a, b, label, score FROM dims;
CREATE UNIQUE INDEX dims_id_key ON dims_id (id);
CREATE INDEX dims_id_ab ON dims_id (a, b);
-- three times the rows of the distinct inputs: reading the table once costs less than a run of the child for each input
CREATE TABLE dims_wide (a int, b int, label text, score int);
INSERT INTO dims_wide SELECT x, y, 'l' || x || '_' || y, x * 3 + y FROM generate_series(0, 599) x, generate_series(0, 100) y;
CREATE UNIQUE INDEX dims_wide_key ON dims_wide (a, b);
-- three versions of each (a, b), in no order, a unique index on the key and the version: the latest is the one row an ORDER BY returns
CREATE TABLE vers (id int PRIMARY KEY, a int NOT NULL, b int NOT NULL, ts int NOT NULL, dt date NOT NULL, label text, score int);
INSERT INTO vers SELECT row_number() OVER (), a, b, z, date '2020-01-01' + z, 'v' || a || '_' || b || '_' || z, a * 3 + b + z * 7
 FROM (SELECT x AS a, y AS b, z FROM generate_series(0, 199) x, generate_series(0, 100) y, generate_series(1, 3) z ORDER BY (x * 131 + y * 17 + z * 7) % 1009) q;
CREATE UNIQUE INDEX vers_key ON vers (a, b, ts);
CREATE UNIQUE INDEX vers_dt ON vers (a, b, dt);
-- the order column holds NULLs, the primary key breaks the ties; the same rows with a repeated version
CREATE TABLE vers_null (id int PRIMARY KEY, a int, b int, ts int, label text, score int);
INSERT INTO vers_null SELECT id, a, b, CASE WHEN ts >= 2 THEN NULL ELSE ts END, label, score FROM vers;
CREATE INDEX vers_null_ab ON vers_null (a, b);
CREATE TABLE vers_null_key (id int PRIMARY KEY, a int NOT NULL, b int NOT NULL, ts int, label text, score int);
INSERT INTO vers_null_key SELECT * FROM vers_null;
CREATE UNIQUE INDEX vers_null_key_ts ON vers_null_key (a, b, ts);
CREATE TABLE vers_dup (id int, a int NOT NULL, b int NOT NULL, ts int NOT NULL, label text, score int);
INSERT INTO vers_dup SELECT id, a, b, ts, label, score FROM vers;
INSERT INTO vers_dup SELECT id + 100000, a, b, ts, 'again', score + 1000 FROM vers WHERE a < 20 AND ts = 3;
CREATE INDEX vers_dup_key ON vers_dup (a, b, ts);
CREATE TABLE dims_label (label text, score int);
INSERT INTO dims_label SELECT label, score FROM dims;
CREATE UNIQUE INDEX dims_label_key ON dims_label (label);
-- a key two rows hold, one updated in place: the index finds the new version first, a heap scan the other row
CREATE TABLE pick (a int, label text) WITH (fillfactor = 50);
INSERT INTO pick VALUES (7, 'first'), (7, 'second');
CREATE INDEX pick_a ON pick (a);
UPDATE pick SET label = 'first, updated' WHERE label = 'first';
VACUUM (FREEZE, ANALYZE) facts;
ANALYZE pick, dims_label, dims, dims_text, dims3, dims_dup, dims_id, dims_wide, vers, vers_null, vers_null_key, vers_dup;
-- whether the query is pushed to the scan, the rows it differs by from the plan without it, and the tables the scan read as first-row lookups
CREATE FUNCTION check_first(q text) RETURNS TABLE (pushed bool, differences bigint, lookups int) LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
  SET LOCAL sift.enable_agg = off;
  EXECUTE 'CREATE TEMP TABLE r_off ON COMMIT DROP AS ' || q;
  SET LOCAL sift.enable_agg = on;
  EXECUTE 'CREATE TEMP TABLE r_on ON COMMIT DROP AS ' || q;
  SELECT count(*) INTO differences FROM
    ((TABLE r_off EXCEPT ALL TABLE r_on) UNION ALL (TABLE r_on EXCEPT ALL TABLE r_off)) d;
  pushed := false;
  lookups := 0;
  FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
    pushed := pushed OR l LIKE '%Custom Scan (SiftAgg)%';
    IF l ~ '(\d+) first row tables' THEN lookups := substring(l from '(\d+) first row tables')::int; END IF;
  END LOOP;
  DROP TABLE r_off, r_on;
  RETURN NEXT;
END $$;
-- two integer keys: a LEFT join (a NULL key and a key the table lacks give NULL columns), columns of the row, an INNER join with a condition on its columns
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s, count(d.label) AS labelled
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.label, count(*) AS n, sum(f.amount) AS s
 FROM facts f JOIN LATERAL (SELECT label, score FROM dims WHERE dims.b = f.b AND dims.a = f.a LIMIT 1) d ON d.score % 50 = 0 GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 7 AS g, count(*) AS n FROM facts f
 LEFT JOIN LATERAL (SELECT score FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON d.score > 300 GROUP BY 1
$q$);
-- text and varchar keys, three keys
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims_text x WHERE x.k = f.k AND x.v = f.v LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims3 x WHERE x.a = f.a AND x.b = f.b AND x.t = f.t LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims_wide x WHERE x.a = f.a AND x.b = f.b LIMIT 1) d ON true GROUP BY 1
$q$);
-- ORDER BY .. LIMIT 1: the key's first row by the order, when a unique index holds the key and the order columns (NOT NULL), whichever way it is sorted
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s, count(d.label) AS labelled
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM vers x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts DESC LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.label, count(*) AS n
 FROM facts f JOIN LATERAL (SELECT label FROM vers x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers x WHERE x.b = f.b AND x.a = f.a AND x.score > 100 ORDER BY x.dt DESC LIMIT 1) d ON true GROUP BY 1
$q$);
-- the order column is not in the output, the output is not the order column, a condition drops the latest row
SELECT * FROM check_first($q$
 SELECT d.ts, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT ts FROM vers x WHERE x.a = f.a AND x.b = f.b AND x.ts < 3 ORDER BY x.ts DESC LIMIT 1) d ON true GROUP BY 1
$q$);
-- the order column holds NULLs: the primary key is in the order, so there is no tie, and NULLs come where the sort puts them
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers_null x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts DESC, x.id DESC LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers_null x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts NULLS LAST, x.id LIMIT 1) d ON true GROUP BY 1
$q$);
-- the order is not total: a NULL order column under a unique index that takes NULLs for different, a version some keys repeat, no unique index
-- on the key and the order, a text or expression order, four order columns
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers_null_key x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts DESC LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers_dup x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts DESC LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers x WHERE x.a = f.a AND x.b = f.b ORDER BY x.label LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts + 1 DESC LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM vers x WHERE x.a = f.a AND x.b = f.b ORDER BY x.ts, x.dt, x.id, x.score LIMIT 1) d ON true GROUP BY 1
$q$);
-- laterals one after the other, the second keyed by a column of the first's row: two lookup tables
SELECT * FROM check_first($q$
 SELECT e.score % 10 AS g, count(*) AS n, sum(e.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true
 LEFT JOIN LATERAL (SELECT score FROM dims_label WHERE dims_label.label = d.label LIMIT 1) e ON true GROUP BY 1
$q$);
-- the rows a plan finds first for a key that is repeated depend on the plan (here the index and the heap disagree): the lookup table cannot
-- tell which the child returns, so the child runs for that key (the table is read only after the child has run for many keys: agg_first_row_dup)
SELECT * FROM check_first($q$
 SELECT d.label, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT label FROM pick WHERE pick.a = f.a LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.label, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT label FROM pick WHERE pick.a = f.a ORDER BY label LIMIT 1) d ON true GROUP BY 1
$q$);
-- every kind of aggregate over the facts, grouped by a column of the lookup row: the float sums are merged in the order of the scan's lanes,
-- which a scan decides from the spec with the lookup table, not the child run per input; the same for each form of the child
SELECT shape, agg, r.* FROM unnest(ARRAY['count(*)', 'count(f.w)', 'count(DISTINCT f.w)', 'sum(f.amount)', 'avg(f.amount)', 'sum(f.w)', 'avg(f.w)', 'min(f.w)', 'max(f.w)',
 'sum(f.w) FILTER (WHERE f.amount > 500)', 'sum(f.w), avg(d.score), count(d.label)']) agg,
 unnest(ARRAY['dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1', 'dims WHERE dims.a = f.a AND dims.b = f.b AND dims.score > 100 LIMIT 1',
 'dims WHERE dims.a = f.a AND dims.b = f.b ORDER BY dims.score DESC LIMIT 1', 'vers WHERE vers.a = f.a AND vers.b = f.b ORDER BY vers.ts DESC LIMIT 1']) shape,
 LATERAL check_first(format($q$
 SELECT d.score / 100 AS g, %s FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM %s) d ON true GROUP BY 1
$q$, agg, shape)) r;
-- laterals one after the other, grouped by a column of the second
SELECT agg, r.* FROM unnest(ARRAY['count(*)', 'sum(f.amount)', 'sum(f.w)', 'avg(f.w)', 'min(f.w)', 'count(DISTINCT f.w)']) agg,
 LATERAL check_first(format($q$
 SELECT e.score / 100 AS g, %s
 FROM facts f LEFT JOIN LATERAL (SELECT label FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true
 LEFT JOIN LATERAL (SELECT score FROM dims_label WHERE dims_label.label = d.label LIMIT 1) e ON true GROUP BY 1
$q$, agg)) r;
-- the rows the lookups return
SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s, count(d.label) AS labelled
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true GROUP BY 1 ORDER BY 1;
-- a condition on the row: the key's row when it passes, none when it does not (a comparison with a constant, a null test, AND/OR/NOT, a column of
-- the key, a column the child does not return, a condition no row passes)
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s, count(d.label) AS labelled
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b AND dims.score > 100 LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.label, count(*) AS n
 FROM facts f JOIN LATERAL (SELECT label FROM dims WHERE dims.a = f.a AND dims.b = f.b AND (dims.score < 50 OR NOT dims.b >= 3) AND dims.label IS NOT NULL LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM dims3 x WHERE x.a = f.a AND x.b = f.b AND x.t = f.t AND x.a > 20 AND x.label <> 'l5_5' LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM dims WHERE dims.a = f.a AND dims.b = f.b AND dims.score < 0 LIMIT 1) d ON true GROUP BY 1
$q$);
-- a condition that can raise an error (the row with a = 250 divides by zero, the facts never ask for it) or that is not a comparison with a constant
-- (a list, the outer row) stays with the child
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM dims_wide x WHERE x.a = f.a AND x.b = f.b AND x.score / (x.a - 250) < 1 LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM dims WHERE dims.a = f.a AND dims.b = f.b AND dims.score IN (3, 10, 400) LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM dims WHERE dims.a = f.a AND dims.b = f.b AND dims.score > f.amount LIMIT 1) d ON true GROUP BY 1
$q$);
-- a key some rows repeat, a unique index on other columns, an order, an offset, a column that is not the table's, a key that is an
-- expression of the outer row (the child compares a parameter): the child runs
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b + 1 LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims_dup WHERE dims_dup.a = f.a AND dims_dup.b = f.b LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims_id WHERE dims_id.a = f.a AND dims_id.b = f.b LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b ORDER BY label LIMIT 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1 OFFSET 1) d ON true GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT d.s % 10 AS g, count(*) AS n, sum(d.s) AS t
 FROM facts f LEFT JOIN LATERAL (SELECT score + 1 AS s FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true GROUP BY 1
$q$);
-- a table that is replaced between two runs of one statement: the lookup is made from the rows of the run
PREPARE first_q AS SELECT d.score % 10 AS g, count(*) AS n, sum(d.score) AS s
 FROM facts f LEFT JOIN LATERAL (SELECT label, score FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true GROUP BY 1 ORDER BY 1;
CREATE TEMP TABLE before_q AS EXECUTE first_q;
UPDATE dims SET score = score + 1 WHERE a = 3;
CREATE TEMP TABLE after_q AS EXECUTE first_q;
SELECT (SELECT sum(s) FROM after_q) - (SELECT sum(s) FROM before_q) AS moved;
SET sift.enable_agg = off;
CREATE TEMP TABLE plain_q AS EXECUTE first_q;
RESET sift.enable_agg;
SELECT count(*) AS differences FROM ((TABLE after_q EXCEPT ALL TABLE plain_q) UNION ALL (TABLE plain_q EXCEPT ALL TABLE after_q)) d;
-- EXPLAIN names the lookup, and the plan of the child that was not run (the share of all-visible pages is masked: PostgreSQL 19 sets pages all-visible as a read prunes them, so it is not a property of the fixture)
CREATE FUNCTION explain_first(q text) RETURNS TABLE (shown text) LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
  FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
    IF l LIKE '%first row tables%' OR l LIKE '%First Row Lookup%' OR l LIKE '%never executed%' OR l LIKE '%Limit%' THEN shown := regexp_replace(trim(l), '[0-9]+ % of', 'N % of'); RETURN NEXT; END IF;
  END LOOP;
END $$;
SELECT * FROM explain_first($q$
 SELECT d.score % 10 AS g, count(*) AS n
 FROM facts f LEFT JOIN LATERAL (SELECT score FROM dims WHERE dims.a = f.a AND dims.b = f.b LIMIT 1) d ON true GROUP BY 1
$q$);
DROP FUNCTION explain_first(text);
-- the inputs of the child are counted from the elements the index holds: the filter on the elements is one PostgreSQL has no statistics for (it expects
-- a fraction of an element a document), the elements that pass are a thousand, each with a key of its own, and the child run for each costs more than
-- the table read once; the same statement over three documents keeps the child
CREATE TABLE fr_docs (id int PRIMARY KEY, kind text NOT NULL, parts jsonb NOT NULL);
INSERT INTO fr_docs SELECT i, 'k' || (i % 3), (SELECT jsonb_agg(jsonb_build_object('cat', 'c' || ((i + j) % 8), 'sub', 's' || ((i * 7 + j * 13) % 3000), 'score', (i + j) % 5)) FROM generate_series(1, 4) j) FROM generate_series(1, 1500) i;
CREATE INDEX fr_docs_col ON fr_docs USING sift_col (id, kind, parts) WITH (paths = 'parts[]{cat astext, sub astext, score float8}');
CREATE TABLE fr_map (id int PRIMARY KEY, kind text NOT NULL, cat text NOT NULL, sub text NOT NULL, label text);
INSERT INTO fr_map SELECT g, 'k' || (g % 3), 'c' || (g % 8), 's' || (g % 3000), 'm' || (g % 11) FROM generate_series(1, 24000) g;
CREATE INDEX fr_map_key ON fr_map (kind, cat, sub);
VACUUM (FREEZE, ANALYZE) fr_docs, fr_map;
SELECT * FROM check_first($q$
 SELECT m.label, count(*) AS n FROM fr_docs d CROSS JOIN LATERAL jsonb_to_recordset(d.parts) o(cat text, sub text, score float8)
 LEFT JOIN LATERAL (SELECT label FROM fr_map WHERE fr_map.kind = d.kind AND fr_map.cat = o.cat AND fr_map.sub = o.sub ORDER BY fr_map.id LIMIT 1) m ON true
 WHERE o.score >= 0 AND o.score = 1 GROUP BY 1
$q$);
SELECT * FROM check_first($q$
 SELECT m.label, count(*) AS n FROM fr_docs d CROSS JOIN LATERAL jsonb_to_recordset(d.parts) o(cat text, sub text, score float8)
 LEFT JOIN LATERAL (SELECT label FROM fr_map WHERE fr_map.kind = d.kind AND fr_map.cat = o.cat AND fr_map.sub = o.sub ORDER BY fr_map.id LIMIT 1) m ON true
 WHERE o.score >= 0 AND o.score = 1 AND d.id <= 3 GROUP BY 1
$q$);
DROP FUNCTION check_first(text);
DROP TABLE facts, dims, dims_text, dims3, dims_dup, dims_id, dims_wide, vers, vers_null, vers_null_key, vers_dup, pick, dims_label, fr_docs, fr_map;
DROP EXTENSION pg_sift;