-- Multi-key operator classes: arrays (DESIGN.md section 17).
--
-- `array_ops` indexes one row under every distinct element of its array, so
-- `@>` is the intersection of the elements' posting sets, `&&` their union,
-- and `<@` cannot be answered from them at all.  Every query below is run
-- through the index and through a forced sequential scan and the two results
-- are compared as multisets, so the access method's own answers are checked
-- and not just the pushdown's.
\set VERBOSITY terse
SET client_min_messages = warning;
LOAD 'pg_lion';

/* VACUUM can only set all-visible for commits that reached disk (citext.sql). */
SET synchronous_commit = on;
SET default_statistics_target = 1000;

CREATE EXTENSION IF NOT EXISTS pg_lion;

CREATE DOMAIN tarr AS text[];

/*
 * Run one query through the index (count pushdown enabled, seqscan off) and
 * through a plain sequential scan, prove the two results are equal as
 * multisets, and report which node answered the first one.
 */
CREATE FUNCTION lion_arrcmp(q text) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
	ln text;
	node text := 'seq scan';
	nrows bigint;
	ndiff bigint;
BEGIN
	PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true);
	PERFORM set_config('enable_seqscan', 'off', true);
	PERFORM set_config('pg_lion.enable_plain_scan', 'off', true);	-- the bitmap path (§29)
	FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF ln LIKE '%Custom Scan (LionCount)%' THEN
			node := 'pushed down';
		ELSIF ln LIKE '%Bitmap Index Scan%' THEN
			node := 'index scan';
		END IF;
	END LOOP;
	EXECUTE format('CREATE TEMP TABLE lion_a_idx AS %s', q);

	PERFORM set_config('pg_lion.enable_count_pushdown', 'off', true);
	PERFORM set_config('enable_seqscan', 'on', true);
	PERFORM set_config('enable_bitmapscan', 'off', true);
	PERFORM set_config('enable_indexscan', 'off', true);
	EXECUTE format('CREATE TEMP TABLE lion_a_seq AS %s', q);
	PERFORM set_config('enable_bitmapscan', 'on', true);
	PERFORM set_config('enable_indexscan', 'on', true);
	PERFORM set_config('pg_lion.enable_plain_scan', 'on', true);

	EXECUTE 'SELECT count(*) FROM lion_a_idx' INTO nrows;
	EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM lion_a_idx EXCEPT ALL SELECT * FROM lion_a_seq) a)'
			' + (SELECT count(*) FROM (SELECT * FROM lion_a_seq EXCEPT ALL SELECT * FROM lion_a_idx) b)'
		INTO ndiff;
	EXECUTE 'DROP TABLE lion_a_idx, lion_a_seq';

	IF ndiff <> 0 THEN
		RETURN format('MISMATCH: %s rows differ', ndiff);
	END IF;
	RETURN format('%s, %s rows', node, nrows);
END $$;

/* The plan of one query with the index preferred. */
CREATE FUNCTION lion_arrplan(q text) RETURNS SETOF text
LANGUAGE plpgsql AS $$
DECLARE
	ln text;
BEGIN
	PERFORM set_config('enable_seqscan', 'off', true);
	PERFORM set_config('pg_lion.enable_plain_scan', 'off', true);	-- the bitmap path (§29)
	FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		RETURN NEXT ln;
	END LOOP;
	PERFORM set_config('enable_seqscan', 'on', true);
	PERFORM set_config('pg_lion.enable_plain_scan', 'on', true);
END $$;

-- ---- the table ---------------------------------------------------------
--
-- tags: 1 to 3 of 40 text tags, with duplicates on purpose (row i holds
-- t(i%40) twice whenever i%7 = 0) and a NULL element every 53rd row.
-- nums: two int elements.  Row 0 has empty arrays, row -1 NULL ones.
CREATE TABLE lion_arr (
	id		int			NOT NULL,
	grp		int			NOT NULL,	-- 8 groups, scalar lion index
	tags	text[],
	nums	int[]
);

INSERT INTO lion_arr
SELECT i, i % 8,
	   CASE WHEN i % 53 = 0
			THEN ARRAY['t' || (i % 40), NULL, 't' || (i % 13)]
			WHEN i % 7 = 0
			THEN ARRAY['t' || (i % 40), 't' || (i % 40), 't' || (i % 13)]
			ELSE ARRAY['t' || (i % 40), 't' || (i % 13), 't' || (i % 3)]
	   END,
	   ARRAY[i % 11, i % 5]
  FROM generate_series(1, 20000) i;
INSERT INTO lion_arr VALUES (0, 0, '{}', '{}');
INSERT INTO lion_arr VALUES (-1, 1, NULL, NULL);

CREATE INDEX lion_arr_tags ON lion_arr USING lion (tags);
CREATE INDEX lion_arr_nums ON lion_arr USING lion (nums);
CREATE INDEX lion_arr_grp ON lion_arr USING lion (grp);
VACUUM (FREEZE, ANALYZE) lion_arr;

-- The opclasses validate.
SELECT amvalidate(oid) FROM pg_opclass
 WHERE opcmethod = (SELECT oid FROM pg_am WHERE amname = 'lion')
   AND opcname IN ('array_ops', 'tsvector_ops')
 ORDER BY opcname;

-- The index stores ELEMENTS, so its key column is the element type.
SELECT a.attname, a.atttypid::regtype
  FROM pg_attribute a
 WHERE a.attrelid = 'lion_arr_tags'::regclass AND a.attnum > 0;

SELECT lion_index_verify('lion_arr_tags', true);
SELECT lion_index_verify('lion_arr_nums', true);

-- 40 tag entries + 13 + 3 overlap; the NULL array is one entry, the empty
-- array another.  empty_tids counts the rows with no element at all, and
-- nullelem_tids the rows whose every element is NULL (none yet).
SELECT entries, null_tids, empty_tids, nullelem_tids
  FROM lion_index_stats('lion_arr_tags');
SELECT entries, null_tids, empty_tids, nullelem_tids
  FROM lion_index_stats('lion_arr_nums');

-- lion_index_count() is a single-key function: a multi-key index has no
-- equality strategy to look a value up with, and says so.
SELECT lion_index_count('lion_arr_tags', '{t5}'::text[]);

-- ---- plans -------------------------------------------------------------
SELECT lion_arrplan($$SELECT id FROM lion_arr WHERE tags @> '{t5}'$$);
SELECT lion_arrplan($$SELECT count(*) FROM lion_arr WHERE tags @> '{t5,t7}'$$);
SELECT lion_arrplan($$SELECT count(*) FROM lion_arr WHERE tags && '{t5,t7}'$$);
-- `<@` needs every row rechecked, so it is never pushed down
SELECT lion_arrplan($$SELECT count(*) FROM lion_arr WHERE tags <@ '{t5,t7}'$$);
-- `@> '{}'` is every non-NULL row: an ALL-mode scan, also not pushed down
SELECT lion_arrplan($$SELECT count(*) FROM lion_arr WHERE tags @> '{}'$$);
SELECT lion_arrplan($$SELECT grp, count(*) FROM lion_arr
					 WHERE tags @> '{t5}' GROUP BY grp$$);
SELECT lion_arrplan($$SELECT count(*) FROM lion_arr
					 WHERE tags @> '{t5}' AND nums && '{3}'$$);

-- ---- @> with one, two and three elements -------------------------------
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t5}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t5,t5}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t5,t2}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t5,t2,t0}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t5,nosuchtag}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE nums @> '{3,4}'$$);

-- ---- && ----------------------------------------------------------------
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags && '{t5}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags && '{t5,t2}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags && '{nosuchtag,t39}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags && '{nosuchtag}'$$);

-- ---- two index quals on one column --------------------------------------
-- The access method answers both and ANDs their key sets, as it ANDs two
-- columns' (DESIGN.md §5 SCAN step 5).
SELECT lion_arrcmp($$SELECT id FROM lion_arr
				   WHERE tags @> '{t5}' AND tags && '{t2,t7}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr
				   WHERE tags @> '{t5}' AND tags @> '{t2}'$$);

-- ---- <@ (recheck) ------------------------------------------------------
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags <@ '{t5,t2,t0}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE nums <@ '{0,1,2,3,4}'$$);

-- ---- empty query arrays ------------------------------------------------
-- `@> '{}'` is true of every non-NULL array, empty ones included.
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{}'$$);
-- `&& '{}'` is true of nothing.
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags && '{}'$$);

-- ---- NULLs on both sides -----------------------------------------------
-- A NULL element in the QUERY: `@>` is then false everywhere, `&&` ignores it.
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> ARRAY['t5', NULL]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags && ARRAY['t5', NULL]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> ARRAY[NULL]::text[]$$);
-- A NULL element in the indexed VALUE: the row is indexed under its others.
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t1}' AND id % 53 = 0$$);
-- A NULL array value itself.
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags IS NULL$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags IS NOT NULL$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags IS NULL$$);

-- ---- array = (the rows holding every element, rechecked) -------------
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags = '{t5,t5,t5}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags = '{t5,t18,t2}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags = '{t18,t5,t2}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags = ARRAY['t13', NULL, 't0']$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags = '{}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE nums = '{3,4}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE nums = '{3,4}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags = NULL$$);
SELECT count(*) FROM lion_arr WHERE nums = '{3,4}';
SELECT count(*) FROM lion_arr WHERE tags = '{t5,t18,t2}';

-- equal elements, but not equal arrays: repeats, NULLs, order, dimensions
-- and lower bounds
CREATE TABLE lion_arr_eq (id int, a int[]);
INSERT INTO lion_arr_eq SELECT i, ARRAY[i % 5, i % 7] FROM generate_series(1, 3000) i;
INSERT INTO lion_arr_eq VALUES (10001, '{}'), (10002, '{NULL}'), (10003, '{1,NULL}'),
	(10004, '{1,1}'), (10005, '[0:1]={1,1}'), (10006, '{{1,1}}'), (10007, '{1}'),
	(10008, NULL), (10009, '{NULL,NULL}'), (10010, '{NULL,1}');
CREATE INDEX lion_arr_eq_a ON lion_arr_eq USING lion (a);
VACUUM ANALYZE lion_arr_eq;
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{1,1}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{1}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{NULL}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{1,NULL}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{{1,1}}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '[0:1]={1,1}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{3,4}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_eq WHERE a = '{3,4}'$$);
SELECT id FROM lion_arr_eq WHERE a = '{1,1}' AND id > 10000 ORDER BY id;
SELECT id FROM lion_arr_eq WHERE a = '{NULL}' ORDER BY id;
SELECT id FROM lion_arr_eq WHERE a = '{}' ORDER BY id;
-- after writes
INSERT INTO lion_arr_eq VALUES (10011, '{1,1}'), (10012, '{4,3}');
DELETE FROM lion_arr_eq WHERE id = 10004;
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{1,1}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_eq WHERE a = '{4,3}'$$);
SELECT lion_index_verify('lion_arr_eq_a', true);
DROP TABLE lion_arr_eq;

-- ---- the count pushdown ------------------------------------------------
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{t5,t2}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags && '{t5,t2}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr
				   WHERE tags @> '{t5}' AND tags && '{t2,t7}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr
				   WHERE tags @> '{t5}' AND nums @> '{3}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr
				   WHERE tags @> '{t5}' AND grp = 3$$);
-- a key that does not exist: no rows, and no merge to run
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{t5,nosuchtag}'$$);

-- the counts themselves (lion_arrcmp only proves the two plans agree)
SELECT count(*) FROM lion_arr WHERE tags @> '{t5,t2}';
SELECT count(*) FROM lion_arr WHERE tags && '{t5,t2}';
SELECT count(*) FROM lion_arr WHERE tags @> '{t5}' AND tags && '{t2,t7}';
SELECT count(*) FROM lion_arr WHERE tags @> '{t5,nosuchtag}';
SELECT count(*) FROM lion_arr WHERE tags @> '{}';

-- ---- GROUP BY a scalar column with a multi-key WHERE clause ------------
SELECT lion_arrcmp($$SELECT grp, count(*) FROM lion_arr
				   WHERE tags @> '{t5}' GROUP BY grp$$);
SELECT lion_arrcmp($$SELECT grp, count(*) FROM lion_arr
				   WHERE tags && '{t5,t2}' GROUP BY grp$$);
SELECT lion_arrcmp($$SELECT grp, count(*) FROM lion_arr
				   WHERE tags @> '{t5}' AND nums @> '{3}' GROUP BY grp$$);
-- the real answer, for comparison
SELECT grp, count(*) FROM lion_arr WHERE tags @> '{t5}' GROUP BY grp ORDER BY grp;

-- ---- writes ------------------------------------------------------------
INSERT INTO lion_arr
SELECT i, i % 8, ARRAY['t' || (i % 40), 'new' || (i % 3)], ARRAY[i % 11, 99]
  FROM generate_series(20001, 21000) i;
INSERT INTO lion_arr VALUES (30000, 2, '{}', NULL);
INSERT INTO lion_arr VALUES (30001, 2, ARRAY[NULL]::text[], '{7}');

SELECT lion_index_verify('lion_arr_tags', true);
SELECT lion_index_verify('lion_arr_nums', true);
-- '{}' joined the EMPTY entry, '{NULL}' started the NULLELEMS one
SELECT entries, null_tids, empty_tids, nullelem_tids
  FROM lion_index_stats('lion_arr_tags');

-- a dirty heap: the count has to visit it
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{new1}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{t5,new1}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{}' AND id > 29999$$);

UPDATE lion_arr SET tags = ARRAY['upd', 't5'] WHERE id BETWEEN 100 AND 200;
DELETE FROM lion_arr WHERE id BETWEEN 300 AND 400;

SELECT lion_index_verify('lion_arr_tags', true);
SELECT lion_arrcmp($$SELECT id FROM lion_arr WHERE tags @> '{upd}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{upd,t5}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{t5}'$$);

VACUUM lion_arr;
SELECT lion_index_verify('lion_arr_tags', true);
SELECT lion_index_verify('lion_arr_nums', true);

-- an all-visible heap: the count comes out of the visibility map
VACUUM (FREEZE, ANALYZE) lion_arr;
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{upd,t5}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags @> '{t5}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr WHERE tags && '{upd,new1}'$$);
SELECT lion_arrcmp($$SELECT grp, count(*) FROM lion_arr
				   WHERE tags @> '{t5}' GROUP BY grp$$);

-- ---- reserved entries that spill (2026-09-20 review, finding 1) --------
/*
 * Both key-less entries - the EMPTY entry of §17 and the NULL entry of §14 -
 * must keep their identity when their posting set outgrows the entry tuple
 * and moves onto container pages.  lion_entry_spill() used to preserve only
 * the NULL flag, so a spilled EMPTY entry came back as an ordinary entry with
 * a zero-length key: the next empty array could not find it and started a
 * second one, lion_index_stats() stopped counting those rows in
 * empty_tids, and lion_index_verify() reported a key of length 0.
 *
 * inline_limit = 64 makes a posting set spill after a handful of TIDs, and
 * alternating rows keep the containers from being cheap runs, so the INSERT
 * path performs the transition for both kinds.
 */
CREATE TABLE lion_arr_res (id int NOT NULL, a int[]);
CREATE INDEX lion_arr_res_a ON lion_arr_res USING lion (a)
	WITH (inline_limit = 64);
INSERT INTO lion_arr_res
SELECT i, CASE WHEN i % 2 = 0 THEN '{}'::int[] ELSE '{1}'::int[] END
  FROM generate_series(1, 10000) i;
-- exactly two entries: the EMPTY one with 5000 rows, and key 1
SELECT entries, inline_entries, null_tids, empty_tids,
	   container_pages > 0 AS spilled
  FROM lion_index_stats('lion_arr_res_a');
SELECT lion_index_verify('lion_arr_res_a', true);

-- the same for the NULL entry, in the same index
INSERT INTO lion_arr_res
SELECT i, CASE WHEN i % 2 = 0 THEN NULL ELSE '{2}'::int[] END
  FROM generate_series(10001, 14000) i;
-- four entries, all of them chains now: EMPTY, NULL, key 1, key 2
SELECT entries, inline_entries, null_tids, empty_tids
  FROM lion_index_stats('lion_arr_res_a');
SELECT lion_index_verify('lion_arr_res_a', true);

-- The proof that the spilled entries are still the reserved ones: these two
-- rows must JOIN them instead of creating a third and fourth key-less entry.
INSERT INTO lion_arr_res VALUES (20001, '{}'), (20002, NULL);
SELECT entries, null_tids, empty_tids FROM lion_index_stats('lion_arr_res_a');
SELECT lion_index_verify('lion_arr_res_a', true);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_res WHERE a @> '{}'$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_res WHERE a IS NULL AND id > 13990$$);

/*
 * VACUUM performs the same transition: each key here is clustered, so the
 * build gives it a couple of run containers that fit in the entry tuple;
 * deleting every other row turns each of them into a NARROW larger than the
 * index's inline_limit, so ambulkdelete spills the entry while rewriting it
 * (test/sql/vacuum.sql does the same thing to ordinary keys).
 */
CREATE TABLE lion_arr_res2 (id int NOT NULL, a int[]);
INSERT INTO lion_arr_res2
SELECT i, CASE WHEN i <= 10000 THEN '{}'::int[]
			   WHEN i <= 20000 THEN NULL
			   ELSE '{7}'::int[] END
  FROM generate_series(1, 30000) i;
CREATE INDEX lion_arr_res2_a ON lion_arr_res2 USING lion (a) WITH (inline_limit = 1024);
-- three INLINE entries: EMPTY, NULL and key 7, runs of clustered TIDs
SELECT entries, inline_entries, run_containers, null_tids, empty_tids,
	   container_pages > 0 AS spilled
  FROM lion_index_stats('lion_arr_res2_a');
DELETE FROM lion_arr_res2 WHERE id % 2 = 0;
VACUUM (FREEZE) lion_arr_res2;
-- every entry has spilled during the VACUUM, reserved ones included
SELECT entries, inline_entries, narrow_containers, null_tids, empty_tids,
	   container_pages > 0 AS spilled
  FROM lion_index_stats('lion_arr_res2_a');
SELECT lion_index_verify('lion_arr_res2_a', true);
INSERT INTO lion_arr_res2 VALUES (20001, '{}'), (20002, NULL);
SELECT entries, null_tids, empty_tids FROM lion_index_stats('lion_arr_res2_a');
SELECT lion_index_verify('lion_arr_res2_a', true);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_res2 WHERE a @> '{}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_res2 WHERE a IS NULL$$);

-- The third key-less entry, NULLELEMS (arrays whose elements are all NULL),
-- spills and keeps its identity the same way: on INSERT ...
INSERT INTO lion_arr_res
SELECT i, CASE WHEN i % 4 = 0 THEN ARRAY[NULL]::int[]
			   WHEN i % 4 = 2 THEN ARRAY[NULL, NULL]::int[]
			   ELSE '{3}'::int[] END
  FROM generate_series(30001, 34000) i;
SELECT entries, null_tids, empty_tids, nullelem_tids,
	   container_pages > 0 AS spilled
  FROM lion_index_stats('lion_arr_res_a');
INSERT INTO lion_arr_res VALUES (40001, '{NULL}'), (40002, '{{NULL},{NULL}}');
SELECT entries, null_tids, empty_tids, nullelem_tids
  FROM lion_index_stats('lion_arr_res_a');
SELECT lion_index_verify('lion_arr_res_a', true);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_res WHERE a @> '{}'$$);
-- ... and in VACUUM
INSERT INTO lion_arr_res2
SELECT i, ARRAY[NULL]::int[] FROM generate_series(30001, 40000) i;
DELETE FROM lion_arr_res2 WHERE id > 30000 AND id % 2 = 0;
VACUUM (FREEZE) lion_arr_res2;
SELECT entries, null_tids, empty_tids, nullelem_tids
  FROM lion_index_stats('lion_arr_res2_a');
SELECT lion_index_verify('lion_arr_res2_a', true);
INSERT INTO lion_arr_res2 VALUES (40001, '{NULL}');
SELECT entries, nullelem_tids FROM lion_index_stats('lion_arr_res2_a');
SELECT lion_index_verify('lion_arr_res2_a', true);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_res2 WHERE a @> '{}'$$);
DROP TABLE lion_arr_res, lion_arr_res2;

-- ---- a partitioned table (DESIGN.md §16 meets §17) ---------------------
CREATE TABLE lion_arr_part (id int, grp int, tags text[]) PARTITION BY RANGE (id);
CREATE TABLE lion_arr_p1 PARTITION OF lion_arr_part FOR VALUES FROM (0) TO (2000);
CREATE TABLE lion_arr_p2 PARTITION OF lion_arr_part FOR VALUES FROM (2000) TO (4000);
INSERT INTO lion_arr_part
SELECT i, i % 8, ARRAY['t' || (i % 40), 't' || (i % 13)]
  FROM generate_series(0, 3999) i;
CREATE INDEX ON lion_arr_p1 USING lion (tags);
CREATE INDEX ON lion_arr_p2 USING lion (tags);
CREATE INDEX ON lion_arr_p1 USING lion (grp);
CREATE INDEX ON lion_arr_p2 USING lion (grp);
VACUUM (FREEZE, ANALYZE) lion_arr_part;

SELECT lion_arrplan($$SELECT count(*) FROM lion_arr_part WHERE tags @> '{t5,t2}'$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_part WHERE tags @> '{t5,t2}'$$);
SELECT lion_arrcmp($$SELECT grp, count(*) FROM lion_arr_part
				   WHERE tags && '{t5,t2}' GROUP BY grp$$);
SELECT count(*) FROM lion_arr_part WHERE tags @> '{t5,t2}';
DROP TABLE lion_arr_part;

-- ---- element types the keys cannot be made from ------------------------
-- The element type needs a default hash or btree opclass, as GIN needs a
-- btree one, and says so when it has neither.
CREATE TABLE lion_arr_odd (p point[], m int[][], d tarr);
CREATE INDEX ON lion_arr_odd USING lion (p);

-- A multi-dimensional array is just its elements, and a domain its base type.
CREATE INDEX lion_arr_odd_m ON lion_arr_odd USING lion (m);
CREATE INDEX lion_arr_odd_d ON lion_arr_odd USING lion (d);
INSERT INTO lion_arr_odd (m, d)
	VALUES ('{{1,2},{3,4}}', '{a,b}'), ('{{5,6},{1,9}}', '{b,c}');
SELECT lion_index_verify('lion_arr_odd_m', true);
SELECT lion_index_verify('lion_arr_odd_d', true);
SELECT lion_arrcmp($$SELECT m FROM lion_arr_odd WHERE m @> '{1}'$$);
SELECT lion_arrcmp($$SELECT m FROM lion_arr_odd WHERE m @> '{{1,2}}'$$);
SELECT lion_arrcmp($$SELECT d FROM lion_arr_odd WHERE d @> '{b}'$$);
DROP TABLE lion_arr_odd;

-- ---- element types with a btree opclass and no hash one ---------------
-- money, bit, varbit, tsvector and tsquery: the btree comparison orders the
-- column, and the hash is the stored image's where the btree class says
-- equal values are stored alike, 0 otherwise (DESIGN.md §17).
CREATE TABLE lion_arr_bt (id int, m money[], b bit(3)[], vb varbit[],
						  tv tsvector[], tq tsquery[]);
INSERT INTO lion_arr_bt SELECT g,
	ARRAY[(g % 7)::numeric::money, (g % 11)::numeric::money],
	ARRAY[(g % 8)::bit(3), ((g + 1) % 8)::bit(3)],
	ARRAY[((g % 5)::bit(4))::varbit, repeat('1', g % 3 + 1)::varbit],
	ARRAY[to_tsvector('simple', 'w' || (g % 6) || ' x' || (g % 4)),
		  to_tsvector('simple', 'y' || (g % 3))],
	ARRAY[('w' || (g % 6))::tsquery, ('x' || (g % 4) || ' & y1')::tsquery]
  FROM generate_series(1, 2000) g;
INSERT INTO lion_arr_bt VALUES (0, '{}', '{}', NULL, '{}', ARRAY[NULL]::tsquery[]);
CREATE INDEX lion_arr_bt_m ON lion_arr_bt USING lion (m);
CREATE INDEX lion_arr_bt_b ON lion_arr_bt USING lion (b);
CREATE INDEX lion_arr_bt_vb ON lion_arr_bt USING lion (vb);
CREATE INDEX lion_arr_bt_tv ON lion_arr_bt USING lion (tv);
CREATE INDEX lion_arr_bt_tq ON lion_arr_bt USING lion (tq);
-- inserted after the build, and deleted, as well as built
INSERT INTO lion_arr_bt SELECT 3000 + g,
	ARRAY[(g % 3)::numeric::money], ARRAY[(g % 2)::bit(3)],
	ARRAY[B'101'::varbit], ARRAY[to_tsvector('simple', 'w1')], ARRAY['w1'::tsquery]
  FROM generate_series(1, 200) g;
DELETE FROM lion_arr_bt WHERE id % 10 = 0;
VACUUM ANALYZE lion_arr_bt;
SELECT attno, ordered, entries FROM lion_index_stats('lion_arr_bt_m');
SELECT lion_index_verify(i, true)
  FROM unnest(ARRAY['lion_arr_bt_m', 'lion_arr_bt_b', 'lion_arr_bt_vb', 'lion_arr_bt_tv',
					'lion_arr_bt_tq']::regclass[]) i;
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE m @> ARRAY[3::numeric::money]$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_bt WHERE m @> ARRAY[3::numeric::money, 5::numeric::money]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE m && ARRAY[0::numeric::money, 10::numeric::money]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE m <@ ARRAY[0::numeric::money, 1::numeric::money, 2::numeric::money]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE b @> ARRAY[B'101']$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_bt WHERE b && ARRAY[B'101', B'000']$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE b <@ ARRAY[B'000', B'001']$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE vb @> ARRAY[B'11'::varbit]$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_bt WHERE vb && ARRAY[B'0011'::varbit, B'101'::varbit]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE vb <@ ARRAY[B'0011'::varbit, B'1'::varbit, B'11'::varbit]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE tv @> ARRAY[to_tsvector('simple', 'y1')]$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_bt WHERE tv && ARRAY[to_tsvector('simple', 'w1 x1'), to_tsvector('simple', 'y2')]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE tv <@ ARRAY[to_tsvector('simple', 'w1'), to_tsvector('simple', 'y0')]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE tq @> ARRAY['w2'::tsquery]$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arr_bt WHERE tq && ARRAY['w2'::tsquery, 'x1 & y1'::tsquery]$$);
SELECT lion_arrcmp($$SELECT id FROM lion_arr_bt WHERE tq <@ ARRAY['w1'::tsquery]$$);
SELECT * FROM lion_index_facets('lion_arr_bt_m', 3);
SELECT * FROM lion_index_facets('lion_arr_bt_tq', 2);
DROP TABLE lion_arr_bt;

-- ---- costing the ALL-mode fallback (2026-09-21 follow-up review) -------
/*
 * `<@` and `@> '{}'` cannot be answered from the stored element
 * memberships: the extractor falls back to LION_QMODE_ALL,
 * the scan emits the union of every entry, and every candidate row is
 * fetched and rechecked in the heap (DESIGN.md §17).  The generic index
 * estimate used to price that with the PREDICATE's selectivity, which made a
 * whole-index traversal plus a whole-table recheck look like a selective
 * lookup.
 *
 * sum(id) keeps the count pushdown out of the comparison, so what is being
 * chosen between is the index and the sequential scan.  An exact `@>` or `&&`
 * must keep the index - a plain Index Scan for these one-row answers since
 * DESIGN.md §29 - and an ALL-mode query must lose it.  A
 * multi-key clause whose value is not a plan-time Const has to be priced as
 * the expensive shape too, because the query's SHAPE is what decides its
 * mode.
 */
CREATE TABLE lion_arrc (id int NOT NULL, tags text[] NOT NULL);
INSERT INTO lion_arrc
SELECT i, ARRAY['t' || (i % 20), 't' || (i % 400), 'u' || i]
  FROM generate_series(1, 20000) i;
CREATE INDEX lion_arrc_tags ON lion_arrc USING lion (tags);
VACUUM (FREEZE, ANALYZE) lion_arrc;
-- exact: containment and overlap over real elements
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_arrc WHERE tags @> ARRAY['u500'];
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_arrc
	WHERE tags @> ARRAY['u500', 't0'];
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_arrc
	WHERE tags && ARRAY['u500', 'u501'];
-- ALL mode: `<@`, the empty array; a NULL element is the AND of the others,
-- rechecked (it is then false everywhere, which the recheck finds)
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_arrc
	WHERE tags <@ ARRAY['u500', 't0', 't1'];
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_arrc WHERE tags @> ARRAY[]::text[];
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_arrc
	WHERE tags @> ARRAY['u500', NULL];
-- an unknown value has to be priced as the expensive shape as well
PREPARE lion_arrc_p(text[]) AS SELECT sum(id) FROM lion_arrc WHERE tags @> $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE lion_arrc_p(ARRAY['u500']);
RESET plan_cache_mode;
DEALLOCATE lion_arrc_p;
/*
 * ... and the count pushdown prices a multi-key clause it cannot see the
 * query of the same way: `tags @> $1` with `$1 = '{}'` extracts to ALL mode,
 * which the node answers from a sequential scan that tests every row
 * (DESIGN.md §17, "A query known only at run time").  Alone, the clause is
 * then no source of the node's AND, and the node is priced as that
 * sequential scan - the ordinary plan's, without its Aggregate - and chosen:
 * no slower than the ordinary plan at worst, and for a value that turns out
 * exact, like this one, answered from the posting sets.  A CUSTOM plan folds
 * the parameter to a literal and is pushed down as usual.
 */
PREPARE lion_arrc_c(text[]) AS SELECT count(*) FROM lion_arrc WHERE tags @> $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE lion_arrc_c(ARRAY['u500']);
EXECUTE lion_arrc_c(ARRAY['u500']);
EXECUTE lion_arrc_c(ARRAY[]::text[]);
SET plan_cache_mode = force_custom_plan;
EXPLAIN (COSTS OFF) EXECUTE lion_arrc_c(ARRAY['u500']);
EXECUTE lion_arrc_c(ARRAY['u500']);
RESET plan_cache_mode;
DEALLOCATE lion_arrc_c;
-- and the answers are the same whichever plan runs
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arrc
				    WHERE tags <@ ARRAY['u500', 't0', 't1']$$);
SELECT lion_arrcmp($$SELECT count(*) FROM lion_arrc
				    WHERE tags @> ARRAY['u500', 't0']$$);
DROP TABLE lion_arrc;

-- ---- max_entries, the cardinality guard --------------------------------
CREATE TABLE lion_arr_many (a text[]);
INSERT INTO lion_arr_many
SELECT ARRAY['k' || i, 'k' || (i + 1)] FROM generate_series(1, 2000) i;

-- the build counts the distinct keys exactly, so the warning is exact
SET client_min_messages = warning;
CREATE INDEX lion_arr_many_a ON lion_arr_many USING lion (a)
	WITH (max_entries = 100);
-- once per backend per index: the insert below stays quiet
INSERT INTO lion_arr_many VALUES (ARRAY['brand', 'new']);
SELECT entries > 100 FROM lion_index_stats('lion_arr_many_a');

-- a fresh index in a fresh backend warns again, this time from an insert
DROP INDEX lion_arr_many_a;
\connect -
SET client_min_messages = warning;
SET synchronous_commit = on;
CREATE INDEX lion_arr_many_b ON lion_arr_many USING lion (a)
	WITH (max_entries = 1000000);
ALTER INDEX lion_arr_many_b SET (max_entries = 50);
INSERT INTO lion_arr_many SELECT ARRAY['z' || i] FROM generate_series(1, 200) i;
SELECT lion_index_verify('lion_arr_many_b', true);

-- a column of a few keys with many rows each: the index's blocks are its
-- posting trees', its directory one leaf, and a new key is the sixth entry
-- of the index, not a cause to warn
CREATE TABLE lion_arr_few (a int[]);
INSERT INTO lion_arr_few SELECT ARRAY[i % 5] FROM generate_series(1, 500000) i;
CREATE INDEX lion_arr_few_a ON lion_arr_few USING lion (a)
	WITH (max_entries = 100);
SELECT pg_relation_size('lion_arr_few_a') / 8192 > 20 AS many_blocks, entries
  FROM lion_index_stats('lion_arr_few_a');
INSERT INTO lion_arr_few VALUES (ARRAY[5]);
SELECT entries FROM lion_index_stats('lion_arr_few_a');
DROP TABLE lion_arr_few;

DROP TABLE lion_arr_many;
DROP TABLE lion_arr;
DROP FUNCTION lion_arrcmp(text);
DROP FUNCTION lion_arrplan(text);
DROP DOMAIN tarr;