-- Multi-key operator classes: tsvector (DESIGN.md section 17).
--
-- `tsvector_ops` indexes one row under every lexeme of its tsvector, with
-- text keys.  A tsquery of AND and OR over plain lexemes becomes a boolean
-- tree over those keys and is answered exactly; NOT, phrase search, prefixes
-- and weights need positions or ranges the index does not have, and fall back
-- to scanning every indexed row with a recheck.  The indexes here are built
-- WITH (store_positions = false) to keep it so: positions.sql tests what a
-- tsvector index stores by default.
--
-- Every query is run through the index and through a forced sequential scan
-- and the two results compared as multisets.
\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 FUNCTION lion_tscmp(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_t_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_t_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_t_idx' INTO nrows;
	EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM lion_t_idx EXCEPT ALL SELECT * FROM lion_t_seq) a)'
			' + (SELECT count(*) FROM (SELECT * FROM lion_t_seq EXCEPT ALL SELECT * FROM lion_t_idx) b)'
		INTO ndiff;
	EXECUTE 'DROP TABLE lion_t_idx, lion_t_seq';

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

CREATE FUNCTION lion_tsplan(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 ---------------------------------------------------------
--
-- A few hundred generated sentences over a vocabulary of ~60 words, so that
-- the posting sets are big enough to be interesting and the answers small
-- enough to compare.  Row 0's text is empty, so its tsvector has no lexeme at
-- all and the row lands in the reserved EMPTY entry; row -1's is NULL.
CREATE TABLE lion_ts (
	id		int		NOT NULL,
	grp		int		NOT NULL,	-- 6 groups, scalar lion index
	body	text,
	tsv		tsvector
);

INSERT INTO lion_ts (id, grp, body)
SELECT i, i % 6,
	   'alpha w' || (i % 17) || ' w' || (i % 23) || ' w' || (i % 29) ||
	   ' bravo charlie delta w' || (i % 31)
  FROM generate_series(1, 600) i;
INSERT INTO lion_ts (id, grp, body) VALUES (0, 0, '');
INSERT INTO lion_ts (id, grp, body) VALUES (-1, 1, NULL);
UPDATE lion_ts SET tsv = to_tsvector('english', coalesce(body, ''))
 WHERE body IS NOT NULL;

CREATE INDEX lion_ts_tsv ON lion_ts USING lion (tsv) WITH (store_positions = false);
CREATE INDEX lion_ts_grp ON lion_ts USING lion (grp);
VACUUM (FREEZE, ANALYZE) lion_ts;

-- The key column of a tsvector_ops index is text: the lexeme.
SELECT a.attname, a.atttypid::regtype
  FROM pg_attribute a
 WHERE a.attrelid = 'lion_ts_tsv'::regclass AND a.attnum > 0;

SELECT lion_index_verify('lion_ts_tsv', true);
SELECT entries, null_tids, empty_tids FROM lion_index_stats('lion_ts_tsv');

-- ---- plans -------------------------------------------------------------
SELECT lion_tsplan($$SELECT id FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'w1 & w2')$$);
SELECT lion_tsplan($$SELECT count(*) FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'w1 & w2')$$);
SELECT lion_tsplan($$SELECT count(*) FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'w1 | w2')$$);
-- NOT beside a lexeme, phrase and weights are counted from the superset of
-- their lexemes' rows and rechecked; a prefix is never pushed down
SELECT lion_tsplan($$SELECT count(*) FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'alpha & !w1')$$);
SELECT lion_tsplan($$SELECT count(*) FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'w1:*')$$);
SELECT lion_tsplan($$SELECT count(*) FROM lion_ts
					WHERE tsv @@ phraseto_tsquery('english', 'bravo charlie')$$);
SELECT lion_tsplan($$SELECT count(*) FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'alpha:A')$$);
SELECT lion_tsplan($$SELECT grp, count(*) FROM lion_ts
					WHERE tsv @@ to_tsquery('english', 'w1 & w2') GROUP BY grp$$);

-- ---- AND, OR, nested parentheses ---------------------------------------
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@ to_tsquery('english', 'w1')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@ to_tsquery('english', 'w1 & w2')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@ to_tsquery('english', 'w1 | w2')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & (w1 | w2)')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', '(w1 | w2) & (w3 | w4)')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & bravo & charlie & delta')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', '((w1 | w2) & w3) | (w4 & w5)')$$);
-- a lexeme that matches nothing, alone and inside a tree
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@ to_tsquery('english', 'nosuchword')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & nosuchword')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha | nosuchword')$$);

-- ---- two index quals, and `@@ ANY (...)` --------------------------------
-- The access method answers both and ANDs their lexeme trees, as it ANDs
-- two columns' (DESIGN.md §5 SCAN step 5).
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1')
					AND tsv @@ to_tsquery('english', 'w2')$$);
-- `@@ ANY (array of tsqueries)` is the union of the queries' answers, which
-- an array_ops index cannot be given (an array of arrays does not exist).
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ ANY (ARRAY['w1 & w2', 'w3 & w4']::tsquery[])$$);
-- one element of the list needs the ALL fallback: the union still holds
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ ANY (ARRAY['w1 & w2', '!w3']::tsquery[])$$);

-- ---- the fallbacks -----------------------------------------------------
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & !w1')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', '!w1')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@ to_tsquery('english', 'w1:*')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & w1:*')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ phraseto_tsquery('english', 'bravo charlie')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'bravo <-> charlie')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'bravo <2> delta')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@ to_tsquery('english', 'alpha:A')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha:* & bravo:B')$$);

-- plainto_tsquery is a plain AND, so it is answered exactly
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ plainto_tsquery('english', 'bravo charlie')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ plainto_tsquery('english', 'alpha w1')$$);

-- `@@@` is `@@`
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@@ to_tsquery('english', 'w1 & w2')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@@ to_tsquery('english', 'alpha & !w1')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@@ to_tsquery('english', 'bravo <-> charlie')$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv @@@ to_tsquery('english', 'w1:*')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@@ to_tsquery('english', 'w1 | w2')$$);

-- NULL and empty tsvectors
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv IS NULL$$);
SELECT lion_tscmp($$SELECT id FROM lion_ts WHERE tsv IS NOT NULL$$);

-- ---- the count pushdown ------------------------------------------------
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1 & w2')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1 | w2')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & (w1 | w2)')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & !w1')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1 & w2') AND grp = 3$$);

-- the counts themselves
SELECT count(*) FROM lion_ts WHERE tsv @@ to_tsquery('english', 'w1 & w2');
SELECT count(*) FROM lion_ts WHERE tsv @@ to_tsquery('english', 'w1 | w2');
SELECT count(*) FROM lion_ts WHERE tsv @@ to_tsquery('english', 'alpha & (w1 | w2)');
SELECT count(*) FROM lion_ts WHERE tsv @@ to_tsquery('english', 'nosuchword');

-- ---- GROUP BY a scalar column with a tsquery WHERE clause --------------
SELECT lion_tscmp($$SELECT grp, count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1 & w2') GROUP BY grp$$);
SELECT lion_tscmp($$SELECT grp, count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1 | w2') GROUP BY grp$$);
SELECT grp, count(*) FROM lion_ts
 WHERE tsv @@ to_tsquery('english', 'w1 & w2') GROUP BY grp ORDER BY grp;

-- ---- writes ------------------------------------------------------------
INSERT INTO lion_ts (id, grp, body, tsv)
SELECT i, i % 6, 'echo foxtrot w' || (i % 17),
	   to_tsvector('english', 'echo foxtrot w' || (i % 17))
  FROM generate_series(601, 900) i;
INSERT INTO lion_ts (id, grp, body, tsv) VALUES (1000, 2, '', to_tsvector('english', ''));
INSERT INTO lion_ts (id, grp, body, tsv) VALUES (1001, 2, NULL, NULL);

SELECT lion_index_verify('lion_ts_tsv', true);
SELECT entries, null_tids, empty_tids FROM lion_index_stats('lion_ts_tsv');

-- a dirty heap
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'echo & foxtrot')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'echo & w3')$$);

UPDATE lion_ts SET tsv = to_tsvector('english', 'golf hotel')
 WHERE id BETWEEN 100 AND 150;
DELETE FROM lion_ts WHERE id BETWEEN 200 AND 250;

SELECT lion_index_verify('lion_ts_tsv', true);
SELECT lion_tscmp($$SELECT id FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'golf & hotel')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & bravo')$$);

VACUUM lion_ts;
SELECT lion_index_verify('lion_ts_tsv', true);

VACUUM (FREEZE, ANALYZE) lion_ts;
-- an all-visible heap: the counts come out of the visibility map
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'alpha & bravo')$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'golf | echo')$$);
SELECT lion_tscmp($$SELECT grp, count(*) FROM lion_ts
				  WHERE tsv @@ to_tsquery('english', 'w1 & w2') GROUP BY grp$$);
SELECT lion_index_verify('lion_ts_tsv', true);

-- ---- the EMPTY entry spilling (2026-09-20 review, finding 1) ----------
/*
 * A tsvector with no lexemes belongs to the reserved EMPTY entry, which must
 * stay the EMPTY entry when its posting set moves onto container pages - the
 * array case is argued in array.sql; here the rows are empty documents.
 */
CREATE TABLE lion_ts_empty (id int NOT NULL, tsv tsvector);
CREATE INDEX lion_ts_empty_tsv ON lion_ts_empty USING lion (tsv)
	WITH (inline_limit = 64, store_positions = false);
INSERT INTO lion_ts_empty
SELECT i, CASE WHEN i % 2 = 0 THEN to_tsvector('simple', '')
			   ELSE to_tsvector('simple', 'onlyword') END
  FROM generate_series(1, 6000) i;
-- one EMPTY entry with 3000 rows and one lexeme entry, both spilled
SELECT entries, inline_entries, null_tids, empty_tids,
	   container_pages > 0 AS spilled
  FROM lion_index_stats('lion_ts_empty_tsv');
SELECT lion_index_verify('lion_ts_empty_tsv', true);
-- these rows must join the EMPTY entry, not start a second one
INSERT INTO lion_ts_empty VALUES (10001, to_tsvector('simple', '')), (10002, NULL);
SELECT entries, null_tids, empty_tids
  FROM lion_index_stats('lion_ts_empty_tsv');
SELECT lion_index_verify('lion_ts_empty_tsv', true);
-- and once more after a VACUUM has rewritten the entries
DELETE FROM lion_ts_empty WHERE id % 4 = 0;
VACUUM (FREEZE) lion_ts_empty;
SELECT entries, null_tids, empty_tids
  FROM lion_index_stats('lion_ts_empty_tsv');
SELECT lion_index_verify('lion_ts_empty_tsv', true);
DROP TABLE lion_ts_empty;

-- ---- costing the ALL-mode fallback (2026-09-21 follow-up review) -------
/*
 * A phrase, a prefix, a weight mask and a NOT cannot be answered from the
 * stored lexeme memberships, so 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).  That is correct but it is a
 * whole-index traversal plus a whole-table recheck, and the generic index
 * estimate used to price it with the PREDICATE's selectivity instead - a
 * prefix query estimated at 192 cost units against the sequential scan's
 * 9156, for a plan that emitted 1.18M posting TIDs and ran 2.6x slower than
 * that sequential scan (68.6/62.3 ms against 31.8/23.8 ms on 200k
 * documents).
 *
 * The queries here aggregate sum(id) rather than count(*) so that the count
 * pushdown is out of the picture and the comparison is between the bitmap
 * plan and the sequential scan, which is where the misestimate lived.  An
 * exact query must keep the index - a plain Index Scan for these few rows
 * since DESIGN.md §29 - and an ALL-mode one must lose it.
 */
CREATE TABLE lion_tsc (id int NOT NULL, tsv tsvector NOT NULL);
INSERT INTO lion_tsc
SELECT i, to_tsvector('simple',
					  'common w' || (i % 20) || ' w' || (i % 400) ||
					  ' rare' || i || ' endword')
  FROM generate_series(1, 20000) i;
CREATE INDEX lion_tsc_tsv ON lion_tsc USING lion (tsv) WITH (store_positions = false);
VACUUM (FREEZE, ANALYZE) lion_tsc;
-- exact: one lexeme, an AND and an OR over lexemes
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc WHERE tsv @@ 'rare500'::tsquery;
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc
	WHERE tsv @@ 'rare500 & endword'::tsquery;
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc
	WHERE tsv @@ 'rare500 | rare501'::tsquery;
-- a superset, rechecked: a phrase, a weight mask, a NOT beside a lexeme;
-- ALL mode: a prefix
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc
	WHERE tsv @@ 'common <-> w1'::tsquery;
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc WHERE tsv @@ 'rare50:*'::tsquery;
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc WHERE tsv @@ 'rare500:A'::tsquery;
EXPLAIN (COSTS OFF) SELECT sum(id) FROM lion_tsc
	WHERE tsv @@ 'rare500 & !endword'::tsquery;
-- an unknown value has to be priced as the expensive shape as well
PREPARE lion_tsc_p(tsquery) AS SELECT sum(id) FROM lion_tsc WHERE tsv @@ $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE lion_tsc_p('rare500');
RESET plan_cache_mode;
DEALLOCATE lion_tsc_p;
-- and the answers are the same whichever plan runs
SELECT lion_tscmp($$SELECT count(*) FROM lion_tsc WHERE tsv @@ 'rare50:*'::tsquery$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_tsc
				   WHERE tsv @@ 'common <-> w1'::tsquery$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_tsc
				   WHERE tsv @@ 'rare500 & endword'::tsquery$$);
-- the superset's rows, fetched by the bitmap scan and rechecked in the heap
SELECT lion_tscmp($$SELECT id FROM lion_tsc
				   WHERE tsv @@ 'common <-> w1'::tsquery$$);
SELECT lion_tscmp($$SELECT id FROM lion_tsc
				   WHERE tsv @@ 'w1 <-> common'::tsquery$$);
SELECT lion_tscmp($$SELECT id FROM lion_tsc
				   WHERE tsv @@ 'rare500 <2> w0'::tsquery$$);
SELECT lion_tscmp($$SELECT id FROM lion_tsc
				   WHERE tsv @@ 'rare500:A'::tsquery$$);
SELECT lion_tscmp($$SELECT id FROM lion_tsc
				   WHERE tsv @@ 'rare500 & !endword'::tsquery$$);
SELECT lion_tscmp($$SELECT id FROM lion_tsc
				   WHERE tsv @@ 'w3 & !(w3 <-> w3)'::tsquery$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_tsc
				   WHERE tsv @@ 'w3 <-> w3'::tsquery$$);
SELECT lion_tscmp($$SELECT count(*) FROM lion_tsc
				   WHERE tsv @@ 'w1 <-> w1 | rare7'::tsquery$$);
DROP TABLE lion_tsc;

-- ---- max_entries on a lexeme index -------------------------------------
CREATE TABLE lion_ts_many (tsv tsvector);
INSERT INTO lion_ts_many
SELECT to_tsvector('simple', 'lex' || i || ' lex' || (i + 1))
  FROM generate_series(1, 3000) i;
CREATE INDEX lion_ts_many_tsv ON lion_ts_many USING lion (tsv)
	WITH (max_entries = 500, store_positions = false);
SELECT entries > 500 FROM lion_index_stats('lion_ts_many_tsv');
SELECT lion_index_verify('lion_ts_many_tsv', true);

DROP TABLE lion_ts_many;
DROP TABLE lion_ts;
DROP FUNCTION lion_tscmp(text);
DROP FUNCTION lion_tsplan(text);