-- ORDER BY <columns> LIMIT n on a sift_table (docs/COL-SEARCH.md, "Top-N"): the filters, the match set of a text search and the sort columns
-- are read from the column units and the write buffers, the table 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 sift.async_maintenance = off;
-- pad makes a superblock hold about 1000 rows: 24000 rows are about 24 superblocks, more than a window of the visit order
CREATE TABLE ct (id serial, store int, status text, amount numeric(10,2), placed date, ts timestamp, seq int, f float8, memo text,
				 pad text DEFAULT repeat('x', 1500)) USING sift_table;
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);
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$);
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+topn 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     +
       | 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+topn 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     +
                          | 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)

-- 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 10 rows are in the newest superblocks, and the units before them are
-- not opened (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 
------------+-----------
          4 |         2
(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 buffer of the table, 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+topn 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     +
        | 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+topn 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     +
        | 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+topn 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     +
        | 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+topn 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     +
       | 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+topn 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     +
          | 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+topn 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     +
        | 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+topn 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     +
         | 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;
-- columns added later: the older segments have none of the sort column (null, or the constant of the default), the newer ones have it
ALTER TABLE ct ADD COLUMN z int;
ALTER TABLE ct ADD COLUMN w int DEFAULT 5;
INSERT INTO ct (store, status, ts, seq, z, w, memo)
SELECT 1 + i % 40, (ARRAY['paid','shipped'])[1 + i % 2], timestamp '2026-01-01' + i * interval '1 minute', 300000 + i, (hashint4(i) & 2147483647) % 500, i % 4, ct_words(i + 200000)
FROM generate_series(1, 3000) i;
INSERT INTO ct_q VALUES
	('added_z',    $q$SELECT id, z FROM ct ORDER BY z DESC NULLS LAST, id LIMIT 10$q$),
	('added_z_nf', $q$SELECT id, z FROM ct ORDER BY z NULLS FIRST, id LIMIT 10$q$),
	('added_w',    $q$SELECT id, w FROM ct WHERE status = 'paid' ORDER BY w DESC, id LIMIT 10$q$),
	('added_w_s',  $q$SELECT id, w, ts FROM ct WHERE memo @@> 'gift' ORDER BY w, ts DESC, id LIMIT 10$q$);
SELECT * FROM ct_stage('added columns');
     stage     |         results         
---------------+-------------------------
 added columns | added_w=same+topn 10   +
               | added_w_s=same+topn 10 +
               | added_z=same+topn 10   +
               | added_z_nf=same+topn 10+
               | 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 52       +
               | 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+topn 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     +
               | 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)

VACUUM ct;
SELECT * FROM ct_stage('added columns, vacuumed');
          stage          |         results         
-------------------------+-------------------------
 added columns, vacuumed | added_w=same+topn 10   +
                         | added_w_s=same+topn 10 +
                         | added_z=same+topn 10   +
                         | added_z_nf=same+topn 10+
                         | 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 52       +
                         | 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+topn 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     +
                         | 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)

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