-- ORDER BY <columns> LIMIT n from the column units (docs/COL-SEARCH.md, "Top-N"): the filters, the match set of a text search and the sort
-- columns are read from the units, the heap only for the n rows of the result. Every query runs twice, the plain plan as the reference
-- (sift.enable_agg off) and the pushed one, and the rows are compared in order: after the build and after each kind of write.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET default_statistics_target = 10000;
SET sift.async_maintenance = off;
-- pad makes a page hold about 5 rows: 24000 rows are about 19 superblocks, more than a window of the visit order
CREATE TABLE ct (id serial PRIMARY KEY, store int, status text, amount numeric(10,2), placed date, ts timestamp, seq int, f float8, memo text,
				 pad text DEFAULT repeat('x', 1500));
ALTER TABLE ct ALTER COLUMN pad SET STORAGE PLAIN;
CREATE FUNCTION ct_words(seed int) RETURNS text LANGUAGE sql IMMUTABLE AS $$ SELECT sift_helpers.words_from_seed_list(seed) $$;
INSERT INTO ct (store, status, amount, placed, ts, seq, f, memo)
SELECT 1 + (hashint4(i) & 2147483647) % 40,
	   CASE WHEN i % 23 = 0 THEN NULL ELSE (ARRAY['paid','shipped','delivered','cancelled'])[1 + (hashint4(i + 7) & 2147483647) % 4] END,
	   CASE WHEN i % 997 = 0 THEN 'NaN'::numeric WHEN i % 29 = 0 THEN NULL ELSE ((hashint4(i + 3) & 2147483647) % 30000)::numeric / 100 END,
	   CASE WHEN i % 31 = 0 THEN NULL ELSE date '2024-01-01' + (hashint4(i + 5) & 2147483647) % 700 END,
	   timestamp '2024-01-01' + (hashint4(i + 11) & 2147483647) % 60000000 * interval '1 second',
	   i * 3,
	   CASE WHEN i % 991 = 0 THEN 'NaN'::float8 WHEN i % 37 = 0 THEN NULL WHEN i % 41 = 0 THEN -0.0 ELSE ((hashint4(i + 13) & 2147483647) % 100000) / 7.0 END,
	   CASE WHEN i % 41 = 0 THEN NULL ELSE ct_words(i) END
FROM generate_series(1, 24000) i;
CREATE INDEX ct_bm ON ct USING sift (memo);
CREATE INDEX ct_col ON ct USING sift_col (id, store, status, amount, placed, ts, seq, f) WITH (write_buffer_limit = 256);
NOTICE:  sift_col index "ct_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ct_col');
VACUUM (ANALYZE) ct;
CREATE FUNCTION ct_mode(pushed bool) RETURNS void LANGUAGE sql AS $$ SELECT sift_helpers.agg_mode(pushed) $$;
-- 'same' / 'DIFFERENT' (the rows and their order), '+topn' when the pushed plan is the top-N scan, and the rows
CREATE FUNCTION ct_check(q text, arg text DEFAULT NULL) RETURNS text LANGUAGE plpgsql AS $$
DECLARE n bigint; plan text := ''; line text; res text;
BEGIN
	DROP TABLE IF EXISTS ct_a, ct_b;
	PERFORM ct_mode(true);
	FOR line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q USING arg LOOP plan := plan || line || E'\n'; END LOOP;
	EXECUTE 'CREATE TEMP TABLE ct_a AS SELECT row_number() OVER () AS pos, row_to_json(x)::text AS r FROM (' || q || ') x' USING arg;
	PERFORM ct_mode(false);
	EXECUTE 'CREATE TEMP TABLE ct_b AS SELECT row_number() OVER () AS pos, row_to_json(x)::text AS r FROM (' || q || ') x' USING arg;
	SELECT count(*) INTO n FROM ((SELECT pos, r FROM ct_a EXCEPT ALL SELECT pos, r FROM ct_b) UNION ALL (SELECT pos, r FROM ct_b EXCEPT ALL SELECT pos, r FROM ct_a)) d;
	res := CASE WHEN n = 0 THEN 'same' ELSE 'DIFFERENT' END;
	RETURN res || CASE WHEN plan ~ 'Top-N: ' THEN '+topn' ELSE '' END || ' ' || (SELECT count(*) FROM ct_b);
END $$;
-- one number of the EXPLAIN ANALYZE of the pushed plan
CREATE FUNCTION ct_stat(q text, field text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE j jsonb;
BEGIN
	PERFORM ct_mode(true);
	EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF, FORMAT JSON) ' || q INTO j;
	RETURN jsonb_path_query_first(j, format('$.**.%I', field)::jsonpath)::bigint;
END $$;
CREATE TABLE ct_q (tag text PRIMARY KEY, q text NOT NULL);
INSERT INTO ct_q VALUES
	('newest',     $q$SELECT id, ts, memo FROM ct WHERE memo @@> 'gift' ORDER BY ts DESC, id DESC LIMIT 10$q$),
	('oldest',     $q$SELECT id, ts, memo FROM ct WHERE memo @@> 'gift' ORDER BY ts, id LIMIT 7$q$),
	('filters',    $q$SELECT id, ts, amount FROM ct WHERE memo @@> 'cable wire' AND status = 'paid' AND store > 10 AND placed > date '2024-06-01' ORDER BY ts DESC, id LIMIT 12$q$),
	('offset',     $q$SELECT id, ts FROM ct WHERE memo @@> 'express OR pickup' ORDER BY ts DESC, id DESC LIMIT 5 OFFSET 7$q$),
	('date_desc',  $q$SELECT id, placed FROM ct WHERE memo @@> 'late' ORDER BY placed DESC, id LIMIT 15$q$),
	('date_nl',    $q$SELECT id, placed FROM ct WHERE memo @@> 'late' ORDER BY placed DESC NULLS LAST, id LIMIT 15$q$),
	('date_nf',    $q$SELECT id, placed FROM ct WHERE memo @@> 'late' ORDER BY placed NULLS FIRST, id LIMIT 15$q$),
	('date_asc',   $q$SELECT id, placed FROM ct WHERE memo @@> 'late' ORDER BY placed, id LIMIT 15$q$),
	('num_desc',   $q$SELECT id, amount FROM ct WHERE memo @@> 'refund' ORDER BY amount DESC, id LIMIT 20$q$),
	('num_asc',    $q$SELECT id, amount FROM ct WHERE memo @@> 'refund' ORDER BY amount, id LIMIT 20$q$),
	('num_nan',    $q$SELECT id, amount FROM ct ORDER BY amount DESC NULLS LAST, id LIMIT 30$q$),
	('float_desc', $q$SELECT id, f FROM ct WHERE memo @@> 'size' ORDER BY f DESC, id LIMIT 9$q$),
	('float_asc',  $q$SELECT id, f FROM ct WHERE memo @@> 'size' ORDER BY f, id LIMIT 9$q$),
	('float_all',  $q$SELECT id, f FROM ct ORDER BY f DESC NULLS LAST, id LIMIT 40$q$),
	('two_keys',   $q$SELECT id, store, amount FROM ct WHERE memo @@> 'box' ORDER BY store DESC, amount, id LIMIT 25$q$),
	('three_keys', $q$SELECT id, store, status FROM ct WHERE memo @@> 'box OR wire' ORDER BY store, placed DESC, id DESC LIMIT 25$q$),
	('no_search',  $q$SELECT id, ts FROM ct WHERE status = 'shipped' AND store < 10 ORDER BY ts DESC, id DESC LIMIT 10$q$),
	('no_filter',  $q$SELECT id, ts FROM ct ORDER BY ts DESC, id DESC LIMIT 10$q$),
	('id_desc',    $q$SELECT id FROM ct WHERE memo @@> 'wire' ORDER BY id DESC LIMIT 6$q$),
	('phrase',     $q$SELECT id, ts FROM ct WHERE memo @@> '"cable wire"' ORDER BY ts DESC, id LIMIT 10$q$),
	('prefix',     $q$SELECT id, ts FROM ct WHERE memo @@> 'exch*' ORDER BY ts, id LIMIT 10$q$),
	('fuzzy',      $q$SELECT id, ts FROM ct WHERE memo @@> 'wiree~1' ORDER BY ts, id LIMIT 10$q$),
	('boolean',    $q$SELECT id, ts FROM ct WHERE memo @@> '(damaged OR broken) AND NOT refund' ORDER BY ts DESC, id LIMIT 10$q$),
	('none',       $q$SELECT id, ts FROM ct WHERE memo @@> 'zzzzzz' ORDER BY ts DESC, id LIMIT 10$q$),
	('few',        $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' AND store = 3 AND status = 'paid' ORDER BY ts DESC, id LIMIT 5000$q$),
	('expr_out',   $q$SELECT id, upper(memo) AS m, amount * 2 AS a2 FROM ct WHERE memo @@> 'perfect' ORDER BY seq DESC LIMIT 8$q$),
	('junk_key',   $q$SELECT memo FROM ct WHERE memo @@> 'perfect' ORDER BY seq DESC LIMIT 8$q$),
	('seq_desc',   $q$SELECT id, seq FROM ct WHERE memo @@> 'thanks' ORDER BY seq DESC LIMIT 10$q$),
	('seq_asc',    $q$SELECT id, seq FROM ct WHERE memo @@> 'thanks' ORDER BY seq LIMIT 10$q$),
	('seq_plain',  $q$SELECT id, seq FROM ct WHERE status = 'paid' ORDER BY seq DESC LIMIT 10$q$),
	('text_key',   $q$SELECT id, status FROM ct WHERE memo @@> 'gift' ORDER BY status, id LIMIT 10$q$),
	('col_or',     $q$SELECT id, ts FROM ct WHERE (status = 'paid' OR store < 10) AND memo @@> 'wire' ORDER BY ts, id LIMIT 10$q$),
	('in_or',      $q$SELECT id, ts FROM ct WHERE memo @@> 'wire' OR status = 'paid' ORDER BY ts, id LIMIT 10$q$),
	('or_two',     $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' OR memo @@> 'wire' ORDER BY ts DESC, id DESC LIMIT 10$q$),
	('or_three',   $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' OR memo @@> 'wire' OR memo @@> 'label' ORDER BY ts, id LIMIT 12 OFFSET 4$q$),
	('or_same',    $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' OR memo @@> 'gift' ORDER BY ts DESC, id LIMIT 10$q$),
	('or_phrase',  $q$SELECT id, ts FROM ct WHERE memo @@> '"cable wire"' OR memo @@> 'gift NOT wire' ORDER BY ts DESC, id LIMIT 10$q$),
	('or_filters', $q$SELECT id, ts FROM ct WHERE (memo @@> 'gift' OR memo @@> 'label') AND status = 'paid' AND store > 5 ORDER BY amount DESC NULLS LAST, id LIMIT 10$q$),
	('or_none',    $q$SELECT id, ts FROM ct WHERE memo @@> 'zzzzzz' OR memo @@> 'yyyyyy' ORDER BY ts DESC, id LIMIT 10$q$),
	('or_column',  $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' OR store = 3 ORDER BY ts DESC, id LIMIT 10$q$),
	('or_and',     $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' AND (memo @@> 'wire' OR memo @@> 'label') ORDER BY ts DESC, id LIMIT 10$q$);
-- A generic plan of q (a PREPARE of the types) executed with args, against the plain plan of the same statement: the plan of each mode is
-- made again (a cached generic plan does not follow the settings). 'same' / 'DIFFERENT', '+topn' when the pushed plan is the top-N scan,
-- the rows that went to the sort ('sort N') or 'heap', and the number of rows
CREATE FUNCTION ct_generic(types text, q text, args text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE n bigint; plan text := ''; line text; res text;
BEGIN
	DROP TABLE IF EXISTS ct_a, ct_b;
	PERFORM set_config('plan_cache_mode', 'force_generic_plan', true);
	PERFORM ct_mode(true);
	DEALLOCATE ALL;
	EXECUTE format('PREPARE ct_g (%s) AS %s', types, q);
	FOR line IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) EXECUTE ct_g(' || args || ')' LOOP plan := plan || line || E'\n'; END LOOP;
	DEALLOCATE ct_g;
	EXECUTE format('PREPARE ct_g (%s) AS %s', types, q);
	EXECUTE 'CREATE TEMP TABLE ct_a0 AS EXECUTE ct_g(' || args || ')';
	CREATE TEMP TABLE ct_a AS SELECT row_number() OVER () AS pos, row_to_json(x)::text AS r FROM ct_a0 x;
	PERFORM ct_mode(false);
	DEALLOCATE ct_g;
	EXECUTE format('PREPARE ct_g (%s) AS %s', types, q);
	EXECUTE 'CREATE TEMP TABLE ct_b0 AS EXECUTE ct_g(' || args || ')';
	CREATE TEMP TABLE ct_b AS SELECT row_number() OVER () AS pos, row_to_json(x)::text AS r FROM ct_b0 x;
	DEALLOCATE ct_g;
	DROP TABLE ct_a0, ct_b0;
	SELECT count(*) INTO n FROM ((SELECT pos, r FROM ct_a EXCEPT ALL SELECT pos, r FROM ct_b) UNION ALL (SELECT pos, r FROM ct_b EXCEPT ALL SELECT pos, r FROM ct_a)) d;
	res := CASE WHEN n = 0 THEN 'same' ELSE 'DIFFERENT' END;
	RETURN res || CASE WHEN plan ~ 'Top-N: ' THEN '+topn' ELSE '' END || ' ' || (SELECT count(*) FROM ct_b) ||
		CASE WHEN plan ~ 'Top-N: ' THEN CASE WHEN plan ~ ' sorted rows' THEN ' sort ' || (regexp_match(plan, '(\d+) sorted rows'))[1] ELSE ' heap' END ELSE '' END;
END $$;
CREATE FUNCTION ct_stage(name text) RETURNS TABLE (stage text, results text) LANGUAGE sql AS $$
	SELECT name, string_agg(tag || '=' || ct_check(q), E'\n' ORDER BY tag COLLATE "C") FROM ct_q $$;
SELECT * FROM ct_stage('build');
NOTICE:  table "ct_a" does not exist, skipping
NOTICE:  table "ct_b" does not exist, skipping
 stage |         results         
-------+-------------------------
 build | boolean=same+topn 10   +
       | col_or=same+topn 10    +
       | date_asc=same+topn 15  +
       | date_desc=same+topn 15 +
       | date_nf=same+topn 15   +
       | date_nl=same+topn 15   +
       | expr_out=same+topn 8   +
       | few=same+topn 31       +
       | filters=same+topn 12   +
       | float_all=same+topn 40 +
       | float_asc=same+topn 9  +
       | float_desc=same+topn 9 +
       | fuzzy=same+topn 10     +
       | id_desc=same+topn 6    +
       | in_or=same 10          +
       | junk_key=same+topn 8   +
       | newest=same+topn 10    +
       | no_filter=same+topn 10 +
       | no_search=same+topn 10 +
       | none=same 0            +
       | num_asc=same+topn 20   +
       | num_desc=same+topn 20  +
       | num_nan=same+topn 30   +
       | offset=same+topn 5     +
       | oldest=same+topn 7     +
       | or_and=same 10         +
       | or_column=same 10      +
       | or_filters=same+topn 10+
       | or_none=same+topn 0    +
       | or_phrase=same+topn 10 +
       | or_same=same+topn 10   +
       | or_three=same+topn 12  +
       | or_two=same+topn 10    +
       | phrase=same+topn 10    +
       | prefix=same+topn 10    +
       | seq_asc=same+topn 10   +
       | seq_desc=same+topn 10  +
       | seq_plain=same+topn 10 +
       | text_key=same+topn 10  +
       | three_keys=same+topn 25+
       | two_keys=same+topn 25
(1 row)

SET sift.test_search_chunk_sbs = 1;
SELECT * FROM ct_stage('chunks of one superblock');
          stage           |         results         
--------------------------+-------------------------
 chunks of one superblock | boolean=same+topn 10   +
                          | col_or=same+topn 10    +
                          | date_asc=same+topn 15  +
                          | date_desc=same+topn 15 +
                          | date_nf=same+topn 15   +
                          | date_nl=same+topn 15   +
                          | expr_out=same+topn 8   +
                          | few=same+topn 31       +
                          | filters=same+topn 12   +
                          | float_all=same+topn 40 +
                          | float_asc=same+topn 9  +
                          | float_desc=same+topn 9 +
                          | fuzzy=same+topn 10     +
                          | id_desc=same+topn 6    +
                          | in_or=same 10          +
                          | junk_key=same+topn 8   +
                          | newest=same+topn 10    +
                          | no_filter=same+topn 10 +
                          | no_search=same+topn 10 +
                          | none=same 0            +
                          | num_asc=same+topn 20   +
                          | num_desc=same+topn 20  +
                          | num_nan=same+topn 30   +
                          | offset=same+topn 5     +
                          | oldest=same+topn 7     +
                          | or_and=same 10         +
                          | or_column=same 10      +
                          | or_filters=same+topn 10+
                          | or_none=same+topn 0    +
                          | or_phrase=same+topn 10 +
                          | or_same=same+topn 10   +
                          | or_three=same+topn 12  +
                          | or_two=same+topn 10    +
                          | phrase=same+topn 10    +
                          | prefix=same+topn 10    +
                          | seq_asc=same+topn 10   +
                          | seq_desc=same+topn 10  +
                          | seq_plain=same+topn 10 +
                          | text_key=same+topn 10  +
                          | three_keys=same+topn 25+
                          | two_keys=same+topn 25
(1 row)

RESET sift.test_search_chunk_sbs;
SELECT ct_check('SELECT id, ts FROM ct WHERE memo @@> $1 ORDER BY ts DESC, id LIMIT 10', 'cable wire') AS param;
    param     
--------------
 same+topn 10
(1 row)

SELECT ct_check('SELECT id, ts FROM ct WHERE memo @@> $1 ORDER BY ts DESC, id LIMIT 10') AS param_null;
 param_null 
------------
 same 0
(1 row)

SELECT ct_check('SELECT id, ts FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT 10 OFFSET 3') AS offset_lit;
  offset_lit  
--------------
 same+topn 10
(1 row)

-- LIMIT and OFFSET as parameters of one generic plan: the plan does not know the value, the scan reads it when it starts and keeps a heap
-- of that many rows up to 100000, and every row that passes in a sort above it (or when there is no limit)
SELECT ct_generic('bigint', 'SELECT id, ts FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT $1', v) AS generic_limit, v
FROM (VALUES ('0'), ('1'), ('10'), ('100000'), ('100001'), ('NULL'), ('9223372036854775807')) x(v);
      generic_limit       |          v          
--------------------------+---------------------
 same+topn 0 heap         | 0
 same+topn 1 heap         | 1
 same+topn 10 heap        | 10
 same+topn 5218 heap      | 100000
 same+topn 5218 sort 5218 | 100001
 same+topn 5218 sort 5218 | NULL
 same+topn 5218 sort 5218 | 9223372036854775807
(7 rows)

SELECT ct_generic('bigint, bigint', 'SELECT id, ts FROM ct WHERE memo @@> ''gift'' ORDER BY ts, id LIMIT $1 OFFSET $2', v) AS generic_offset, v
FROM (VALUES ('10, 5'), ('10, NULL'), ('NULL, 5'), ('NULL, NULL'), ('5, 99999'), ('5, 100000'), ('9223372036854775807, 9223372036854775807')) x(v);
      generic_offset      |                    v                     
--------------------------+------------------------------------------
 same+topn 10 heap        | 10, 5
 same+topn 10 heap        | 10, NULL
 same+topn 5213 sort 5218 | NULL, 5
 same+topn 5218 sort 5218 | NULL, NULL
 same+topn 0 sort 5218    | 5, 99999
 same+topn 0 sort 5218    | 5, 100000
 same+topn 0 sort 5218    | 9223372036854775807, 9223372036854775807
(7 rows)

SELECT ct_generic('bigint', 'SELECT id, ts FROM ct WHERE memo @@> ''gift'' OR memo @@> ''label'' ORDER BY amount, id LIMIT $1', v) AS generic_or, v
FROM (VALUES ('7'), ('NULL')) x(v);
        generic_or        |  v   
--------------------------+------
 same+topn 7 heap         | 7
 same+topn 9432 sort 9432 | NULL
(2 rows)

SELECT ct_generic('bigint', 'SELECT id, ts FROM ct ORDER BY ts DESC, id LIMIT $1', v) AS generic_no_filter, v FROM (VALUES ('3'), ('100001')) x(v);
     generic_no_filter      |   v    
----------------------------+--------
 same+topn 3 heap           | 3
 same+topn 24000 sort 24000 | 100001
(2 rows)

-- the Limit node above keeps its own rules: the error of a negative value
DO $$ BEGIN PERFORM ct_generic('bigint', 'SELECT id FROM ct WHERE memo @@> ''gift'' ORDER BY ts, id LIMIT $1', '-1');
EXCEPTION WHEN OTHERS THEN RAISE NOTICE '% %', SQLSTATE, SQLERRM; END $$;
NOTICE:  2201W LIMIT must not be negative
-- a scroll cursor over a generic plan reads the rows forward and back
DO $$
DECLARE
	c SCROLL CURSOR (n bigint) FOR SELECT id FROM ct WHERE memo @@> 'gift' ORDER BY ts DESC, id LIMIT n;
	want int[]; got int[] := '{}'; r int;
BEGIN
	PERFORM set_config('sift.enable_agg', 'off', true);
	want := ARRAY(SELECT id FROM ct WHERE memo @@> 'gift' ORDER BY ts DESC, id LIMIT 1000000);
	PERFORM set_config('sift.enable_agg', 'on', true), set_config('plan_cache_mode', 'force_generic_plan', true);
	OPEN c(1000000);
	LOOP FETCH c INTO r; EXIT WHEN NOT FOUND; got := got || r; END LOOP;
	LOOP FETCH PRIOR FROM c INTO r; EXIT WHEN NOT FOUND; got := got || r; END LOOP;
	CLOSE c;
	RAISE NOTICE 'cursor % %', cardinality(want), got = want || ARRAY(SELECT x FROM unnest(want) WITH ORDINALITY u(x, o) ORDER BY o DESC);
END $$;
NOTICE:  cursor 5218 t
-- the heap is read for the rows of the result only, and not at all when the units hold every column of the output; a column that grows with the table lets the scan skip superblocks
SELECT ct_stat(q, 'Heap Fetches') AS heap_fetches, ct_stat(q, 'Search Matches') > 100 AS many_matches
FROM (SELECT $q$SELECT id, ts, memo FROM ct WHERE memo @@> 'gift' ORDER BY ts DESC, id DESC LIMIT 10$q$ AS q) x;
 heap_fetches | many_matches 
--------------+--------------
           10 | t
(1 row)

SELECT ct_stat(q, 'Heap Fetches') AS heap_fetches
FROM (SELECT $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' ORDER BY ts DESC, id DESC LIMIT 10$q$ AS q) x;
 heap_fetches 
--------------
            0
(1 row)

SELECT ct_stat(q, 'Superblocks Skipped By Top-N') > 0 AS skipped_desc
FROM (SELECT $q$SELECT id, seq FROM ct WHERE memo @@> 'thanks' ORDER BY seq DESC LIMIT 10$q$ AS q) x;
 skipped_desc 
--------------
 t
(1 row)

SELECT ct_stat(q, 'Superblocks Skipped By Top-N') > 0 AS skipped_asc
FROM (SELECT $q$SELECT id, seq FROM ct WHERE memo @@> 'thanks' ORDER BY seq LIMIT 10$q$ AS q) x;
 skipped_asc 
-------------
 t
(1 row)

SELECT ct_stat(q, 'Superblocks Skipped By Top-N') = 0 AS none_skipped_when_random
FROM (SELECT $q$SELECT id, ts FROM ct WHERE memo @@> 'gift' ORDER BY ts DESC, id DESC LIMIT 10$q$ AS q) x;
 none_skipped_when_random 
--------------------------
 t
(1 row)

-- a sort column that grows with the table is read from its best end: the newest superblock holds the 10 rows, and the units before it are
-- not opened (one unit; a visit in table order opened every unit of its window)
SELECT ct_stat('SELECT id FROM ct WHERE memo @@> ''fast'' ORDER BY id DESC LIMIT 10', 'Units') AS units_desc,
	   ct_stat('SELECT id FROM ct WHERE memo @@> ''fast'' ORDER BY id LIMIT 10', 'Units') AS units_asc;
 units_desc | units_asc 
------------+-----------
          1 |         1
(1 row)

CREATE FUNCTION ct_plan(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE plan text := ''; line text;
BEGIN
	FOR line IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP plan := plan || line || E'\n'; END LOOP;
	RETURN CASE WHEN plan ~ 'Top-N: ' THEN 'top-n' WHEN plan ~ 'Index Scan' THEN 'btree' ELSE 'other' END;
END $$;
-- no plan is a top-N when the limit is a sub-select or a volatile function, or a constant past the cap (a limit with ties is one: limit_ties)
SELECT ct_plan('SELECT id FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT 10') AS limit_const,
	   ct_plan('SELECT id FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT (SELECT 10)') AS limit_subquery,
	   ct_plan('SELECT id FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT (random() * 0 + 10)::int') AS limit_volatile,
	   ct_plan('SELECT id FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id FETCH FIRST 10 ROWS WITH TIES') AS limit_ties,
	   ct_plan('SELECT id FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT 100001') AS limit_past_cap;
 limit_const | limit_subquery | limit_volatile | limit_ties | limit_past_cap 
-------------+----------------+----------------+------------+----------------
 top-n       | other          | other          | top-n      | other
(1 row)

-- the GUC
SET sift.enable_topn = off;
SELECT ct_check('SELECT id, ts FROM ct WHERE memo @@> ''gift'' ORDER BY ts DESC, id LIMIT 10') NOT LIKE '%+topn%' AS no_topn_when_off;
 no_topn_when_off 
------------------
 t
(1 row)

RESET sift.enable_topn;
-- inserts land in the write buffers of both indexes, unvacuumed
INSERT INTO ct (store, status, amount, placed, ts, seq, f, memo)
SELECT 1 + (hashint4(i) & 2147483647) % 40, (ARRAY['paid','shipped'])[1 + i % 2], 20 + i % 90, date '2025-06-01' + i % 20,
	   timestamp '2025-01-01' + i * interval '1 minute', 100000 + i, i / 3.0, ct_words(i + 100000)
FROM generate_series(1, 2000) i;
SELECT * FROM ct_stage('insert');
 stage  |         results         
--------+-------------------------
 insert | boolean=same+topn 10   +
        | col_or=same+topn 10    +
        | date_asc=same+topn 15  +
        | date_desc=same+topn 15 +
        | date_nf=same+topn 15   +
        | date_nl=same+topn 15   +
        | expr_out=same+topn 8   +
        | few=same+topn 36       +
        | filters=same+topn 12   +
        | float_all=same+topn 40 +
        | float_asc=same+topn 9  +
        | float_desc=same+topn 9 +
        | fuzzy=same+topn 10     +
        | id_desc=same+topn 6    +
        | in_or=same 10          +
        | junk_key=same+topn 8   +
        | newest=same+topn 10    +
        | no_filter=same+topn 10 +
        | no_search=same+topn 10 +
        | none=same 0            +
        | num_asc=same+topn 20   +
        | num_desc=same+topn 20  +
        | num_nan=same+topn 30   +
        | offset=same+topn 5     +
        | oldest=same+topn 7     +
        | or_and=same 10         +
        | or_column=same 10      +
        | or_filters=same+topn 10+
        | or_none=same+topn 0    +
        | or_phrase=same+topn 10 +
        | or_same=same+topn 10   +
        | or_three=same+topn 12  +
        | or_two=same+topn 10    +
        | phrase=same+topn 10    +
        | prefix=same+topn 10    +
        | seq_asc=same+topn 10   +
        | seq_desc=same+topn 10  +
        | seq_plain=same+topn 10 +
        | text_key=same+topn 10  +
        | three_keys=same+topn 25+
        | two_keys=same+topn 25
(1 row)

-- updates make new versions (new TIDs, new text); the old ones are dead but not vacuumed
UPDATE ct SET memo = memo || ' wire' WHERE id % 9 = 0;
UPDATE ct SET status = 'paid', ts = ts + interval '3 days' WHERE id % 10 = 0;
SELECT * FROM ct_stage('update');
 stage  |         results         
--------+-------------------------
 update | boolean=same+topn 10   +
        | col_or=same+topn 10    +
        | date_asc=same+topn 15  +
        | date_desc=same+topn 15 +
        | date_nf=same+topn 15   +
        | date_nl=same+topn 15   +
        | expr_out=same+topn 8   +
        | few=same+topn 43       +
        | filters=same+topn 12   +
        | float_all=same+topn 40 +
        | float_asc=same+topn 9  +
        | float_desc=same+topn 9 +
        | fuzzy=same+topn 10     +
        | id_desc=same+topn 6    +
        | in_or=same 10          +
        | junk_key=same+topn 8   +
        | newest=same+topn 10    +
        | no_filter=same+topn 10 +
        | no_search=same+topn 10 +
        | none=same 0            +
        | num_asc=same+topn 20   +
        | num_desc=same+topn 20  +
        | num_nan=same+topn 30   +
        | offset=same+topn 5     +
        | oldest=same+topn 7     +
        | or_and=same 10         +
        | or_column=same 10      +
        | or_filters=same+topn 10+
        | or_none=same+topn 0    +
        | or_phrase=same+topn 10 +
        | or_same=same+topn 10   +
        | or_three=same+topn 12  +
        | or_two=same+topn 10    +
        | phrase=same+topn 10    +
        | prefix=same+topn 10    +
        | seq_asc=same+topn 10   +
        | seq_desc=same+topn 10  +
        | seq_plain=same+topn 10 +
        | text_key=same+topn 10  +
        | three_keys=same+topn 25+
        | two_keys=same+topn 25
(1 row)

DELETE FROM ct WHERE id % 13 = 0;
SELECT * FROM ct_stage('delete');
 stage  |         results         
--------+-------------------------
 delete | boolean=same+topn 10   +
        | col_or=same+topn 10    +
        | date_asc=same+topn 15  +
        | date_desc=same+topn 15 +
        | date_nf=same+topn 15   +
        | date_nl=same+topn 15   +
        | expr_out=same+topn 8   +
        | few=same+topn 37       +
        | filters=same+topn 12   +
        | float_all=same+topn 40 +
        | float_asc=same+topn 9  +
        | float_desc=same+topn 9 +
        | fuzzy=same+topn 10     +
        | id_desc=same+topn 6    +
        | in_or=same 10          +
        | junk_key=same+topn 8   +
        | newest=same+topn 10    +
        | no_filter=same+topn 10 +
        | no_search=same+topn 10 +
        | none=same 0            +
        | num_asc=same+topn 20   +
        | num_desc=same+topn 20  +
        | num_nan=same+topn 30   +
        | offset=same+topn 5     +
        | oldest=same+topn 7     +
        | or_and=same 10         +
        | or_column=same 10      +
        | or_filters=same+topn 10+
        | or_none=same+topn 0    +
        | or_phrase=same+topn 10 +
        | or_same=same+topn 10   +
        | or_three=same+topn 12  +
        | or_two=same+topn 10    +
        | phrase=same+topn 10    +
        | prefix=same+topn 10    +
        | seq_asc=same+topn 10   +
        | seq_desc=same+topn 10  +
        | seq_plain=same+topn 10 +
        | text_key=same+topn 10  +
        | three_keys=same+topn 25+
        | two_keys=same+topn 25
(1 row)

-- rows of this transaction are seen by this transaction only; a rolled back insert is seen by nobody
BEGIN;
INSERT INTO ct (store, status, amount, placed, ts, seq, f, memo)
SELECT 1, 'paid', 500 + i, date '2026-01-01', timestamp '2027-01-01' + i * interval '1 second', 900000 + i, i, 'cable wire broken box gift thanks perfect' FROM generate_series(1, 30) i;
DELETE FROM ct WHERE id % 17 = 0;
SELECT * FROM ct_stage('own');
 stage |         results         
-------+-------------------------
 own   | boolean=same+topn 10   +
       | col_or=same+topn 10    +
       | date_asc=same+topn 15  +
       | date_desc=same+topn 15 +
       | date_nf=same+topn 15   +
       | date_nl=same+topn 15   +
       | expr_out=same+topn 8   +
       | few=same+topn 36       +
       | filters=same+topn 12   +
       | float_all=same+topn 40 +
       | float_asc=same+topn 9  +
       | float_desc=same+topn 9 +
       | fuzzy=same+topn 10     +
       | id_desc=same+topn 6    +
       | in_or=same 10          +
       | junk_key=same+topn 8   +
       | newest=same+topn 10    +
       | no_filter=same+topn 10 +
       | no_search=same+topn 10 +
       | none=same 0            +
       | num_asc=same+topn 20   +
       | num_desc=same+topn 20  +
       | num_nan=same+topn 30   +
       | offset=same+topn 5     +
       | oldest=same+topn 7     +
       | or_and=same 10         +
       | or_column=same 10      +
       | or_filters=same+topn 10+
       | or_none=same+topn 0    +
       | or_phrase=same+topn 10 +
       | or_same=same+topn 10   +
       | or_three=same+topn 12  +
       | or_two=same+topn 10    +
       | phrase=same+topn 10    +
       | prefix=same+topn 10    +
       | seq_asc=same+topn 10   +
       | seq_desc=same+topn 10  +
       | seq_plain=same+topn 10 +
       | text_key=same+topn 10  +
       | three_keys=same+topn 25+
       | two_keys=same+topn 25
(1 row)

ROLLBACK;
SELECT * FROM ct_stage('rollback');
  stage   |         results         
----------+-------------------------
 rollback | boolean=same+topn 10   +
          | col_or=same+topn 10    +
          | date_asc=same+topn 15  +
          | date_desc=same+topn 15 +
          | date_nf=same+topn 15   +
          | date_nl=same+topn 15   +
          | expr_out=same+topn 8   +
          | few=same+topn 37       +
          | filters=same+topn 12   +
          | float_all=same+topn 40 +
          | float_asc=same+topn 9  +
          | float_desc=same+topn 9 +
          | fuzzy=same+topn 10     +
          | id_desc=same+topn 6    +
          | in_or=same 10          +
          | junk_key=same+topn 8   +
          | newest=same+topn 10    +
          | no_filter=same+topn 10 +
          | no_search=same+topn 10 +
          | none=same 0            +
          | num_asc=same+topn 20   +
          | num_desc=same+topn 20  +
          | num_nan=same+topn 30   +
          | offset=same+topn 5     +
          | oldest=same+topn 7     +
          | or_and=same 10         +
          | or_column=same 10      +
          | or_filters=same+topn 10+
          | or_none=same+topn 0    +
          | or_phrase=same+topn 10 +
          | or_same=same+topn 10   +
          | or_three=same+topn 12  +
          | or_two=same+topn 10    +
          | phrase=same+topn 10    +
          | prefix=same+topn 10    +
          | seq_asc=same+topn 10   +
          | seq_desc=same+topn 10  +
          | seq_plain=same+topn 10 +
          | text_key=same+topn 10  +
          | three_keys=same+topn 25+
          | two_keys=same+topn 25
(1 row)

-- the same after VACUUM
VACUUM ct;
SELECT * FROM ct_stage('vacuum');
 stage  |         results         
--------+-------------------------
 vacuum | boolean=same+topn 10   +
        | col_or=same+topn 10    +
        | date_asc=same+topn 15  +
        | date_desc=same+topn 15 +
        | date_nf=same+topn 15   +
        | date_nl=same+topn 15   +
        | expr_out=same+topn 8   +
        | few=same+topn 37       +
        | filters=same+topn 12   +
        | float_all=same+topn 40 +
        | float_asc=same+topn 9  +
        | float_desc=same+topn 9 +
        | fuzzy=same+topn 10     +
        | id_desc=same+topn 6    +
        | in_or=same 10          +
        | junk_key=same+topn 8   +
        | newest=same+topn 10    +
        | no_filter=same+topn 10 +
        | no_search=same+topn 10 +
        | none=same 0            +
        | num_asc=same+topn 20   +
        | num_desc=same+topn 20  +
        | num_nan=same+topn 30   +
        | offset=same+topn 5     +
        | oldest=same+topn 7     +
        | or_and=same 10         +
        | or_column=same 10      +
        | or_filters=same+topn 10+
        | or_none=same+topn 0    +
        | or_phrase=same+topn 10 +
        | or_same=same+topn 10   +
        | or_three=same+topn 12  +
        | or_two=same+topn 10    +
        | phrase=same+topn 10    +
        | prefix=same+topn 10    +
        | seq_asc=same+topn 10   +
        | seq_desc=same+topn 10  +
        | seq_plain=same+topn 10 +
        | text_key=same+topn 10  +
        | three_keys=same+topn 25+
        | two_keys=same+topn 25
(1 row)

-- and after the write buffers become immutable ones
SELECT sift.col_promote('ct_col');
 col_promote 
-------------
           1
(1 row)

SELECT * FROM ct_stage('promote');
  stage  |         results         
---------+-------------------------
 promote | boolean=same+topn 10   +
         | col_or=same+topn 10    +
         | date_asc=same+topn 15  +
         | date_desc=same+topn 15 +
         | date_nf=same+topn 15   +
         | date_nl=same+topn 15   +
         | expr_out=same+topn 8   +
         | few=same+topn 37       +
         | filters=same+topn 12   +
         | float_all=same+topn 40 +
         | float_asc=same+topn 9  +
         | float_desc=same+topn 9 +
         | fuzzy=same+topn 10     +
         | id_desc=same+topn 6    +
         | in_or=same 10          +
         | junk_key=same+topn 8   +
         | newest=same+topn 10    +
         | no_filter=same+topn 10 +
         | no_search=same+topn 10 +
         | none=same 0            +
         | num_asc=same+topn 20   +
         | num_desc=same+topn 20  +
         | num_nan=same+topn 30   +
         | offset=same+topn 5     +
         | oldest=same+topn 7     +
         | or_and=same 10         +
         | or_column=same 10      +
         | or_filters=same+topn 10+
         | or_none=same+topn 0    +
         | or_phrase=same+topn 10 +
         | or_same=same+topn 10   +
         | or_three=same+topn 12  +
         | or_two=same+topn 10    +
         | phrase=same+topn 10    +
         | prefix=same+topn 10    +
         | seq_asc=same+topn 10   +
         | seq_desc=same+topn 10  +
         | seq_plain=same+topn 10 +
         | text_key=same+topn 10  +
         | three_keys=same+topn 25+
         | two_keys=same+topn 25
(1 row)

-- workers do not change the plan of a top-N
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SELECT * FROM ct_stage('workers');
  stage  |         results         
---------+-------------------------
 workers | boolean=same+topn 10   +
         | col_or=same+topn 10    +
         | date_asc=same+topn 15  +
         | date_desc=same+topn 15 +
         | date_nf=same+topn 15   +
         | date_nl=same+topn 15   +
         | expr_out=same+topn 8   +
         | few=same+topn 37       +
         | filters=same+topn 12   +
         | float_all=same+topn 40 +
         | float_asc=same+topn 9  +
         | float_desc=same+topn 9 +
         | fuzzy=same+topn 10     +
         | id_desc=same+topn 6    +
         | in_or=same 10          +
         | junk_key=same+topn 8   +
         | newest=same+topn 10    +
         | no_filter=same+topn 10 +
         | no_search=same+topn 10 +
         | none=same 0            +
         | num_asc=same+topn 20   +
         | num_desc=same+topn 20  +
         | num_nan=same+topn 30   +
         | offset=same+topn 5     +
         | oldest=same+topn 7     +
         | or_and=same 10         +
         | or_column=same 10      +
         | or_filters=same+topn 10+
         | or_none=same+topn 0    +
         | or_phrase=same+topn 10 +
         | or_same=same+topn 10   +
         | or_three=same+topn 12  +
         | or_two=same+topn 10    +
         | phrase=same+topn 10    +
         | prefix=same+topn 10    +
         | seq_asc=same+topn 10   +
         | seq_desc=same+topn 10  +
         | seq_plain=same+topn 10 +
         | text_key=same+topn 10  +
         | three_keys=same+topn 25+
         | two_keys=same+topn 25
(1 row)

RESET max_parallel_workers_per_gather;
RESET parallel_setup_cost;
RESET parallel_tuple_cost;
RESET min_parallel_table_scan_size;
-- against an ordered btree on the sort column the planner keeps the cheaper plan: a common word is found in a few rows of the index, a rare
-- combination of words is not (the cost of the top-N is its units and its match set, whatever the table order)
CREATE INDEX ct_ts_idx ON ct (ts);
SELECT ct_plan('SELECT id, ts FROM ct WHERE memo @@> ''fast'' ORDER BY ts DESC LIMIT 10') AS common_ts,
	   ct_plan('SELECT id, ts FROM ct WHERE memo @@> ''fast'' ORDER BY id DESC LIMIT 10') AS common_id,
	   ct_plan('SELECT id, ts FROM ct WHERE memo @@> ''gift AND wire AND label'' ORDER BY ts DESC LIMIT 10') AS rare_ts,
	   ct_plan('SELECT id, ts FROM ct WHERE memo @@> ''gift AND wire AND label'' ORDER BY id DESC LIMIT 10') AS rare_id;
 common_ts | common_id | rare_ts | rare_id 
-----------+-----------+---------+---------
 btree     | btree     | top-n   | top-n
(1 row)

-- a sift_table gives the TIDs of its rows itself: the same top-N (test tbl_topn)
CREATE TABLE ct_t (id serial, store int, ts timestamp, memo text) USING sift_table;
INSERT INTO ct_t (store, ts, memo) SELECT store, ts, memo FROM ct ORDER BY id;
CREATE INDEX ct_t_bm ON ct_t USING sift (memo);
SELECT ct_check('SELECT id, ts FROM ct_t WHERE memo @@> ''gift'' ORDER BY ts DESC, id DESC LIMIT 10') AS tbl;
     tbl      
--------------
 same+topn 10
(1 row)

-- The top-N opens the match set whole (the posting lists of the words), whatever part of the table it reads: on a table of 41 superblocks
-- (rows of one page in six) a search for any of the 20 words costs the top-N about 635 and the btree plan, which the declared cost of the
-- match function sets (COST: what a row costs to test), 25 + COST / 40. The btree plan is the cheaper one up to a COST of about 25000; if
-- the opening were scaled by the fraction read (0.2) the top-N would cost 355 and win from 13000. COST 3000 and 300000 are far on either side,
-- 18000 is between the two.
CREATE TABLE cb (id serial PRIMARY KEY, ts timestamp, memo text) WITH (fillfactor = 10);
INSERT INTO cb (ts, memo) SELECT timestamp '2024-01-01' + (hashint4(i + 11) & 2147483647) % 60000000 * interval '1 second', ct_words(i)
FROM generate_series(1, 100000) i;
CREATE INDEX cb_bm ON cb USING sift (memo);
CREATE INDEX cb_col ON cb USING sift_col (id, ts) WITH (order_array_columns = off);
NOTICE:  sift_col index "cb_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('cb_col');
VACUUM (ANALYZE) cb;
SELECT $q$SELECT id, ts FROM cb WHERE memo @@> 'fast OR late OR damaged OR refund OR gift OR invoice OR exchange OR missing OR perfect OR broken OR express OR pickup OR thanks OR wrong OR size OR color OR cable OR wire OR box OR label' ORDER BY id DESC LIMIT 10$q$ AS q \gset
BEGIN;
ALTER FUNCTION sift.match(text, text) COST 3000;
SELECT ct_plan(:'q') AS cost_3000;
 cost_3000 
-----------
 btree
(1 row)

ALTER FUNCTION sift.match(text, text) COST 18000;
SELECT ct_plan(:'q') AS cost_18000;
 cost_18000 
------------
 btree
(1 row)

ALTER FUNCTION sift.match(text, text) COST 300000;
SELECT ct_plan(:'q') AS cost_300000;
 cost_300000 
-------------
 top-n
(1 row)

ROLLBACK;
DROP TABLE cb;
DROP TABLE ct_q, ct_t, ct;
DROP FUNCTION ct_plan(text), ct_words(int), ct_mode(bool), ct_check(text, text), ct_generic(text, text, text), ct_stat(text, text), ct_stage(text);
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;