-- The skip of an OFFSET over an order read group after group (ORDER BY g, key) checks the entries of a group at once, 12 bytes each; a group longer than
-- skip_pending (1,048,576 entries) is walked with the groups after it. The limit is lowered so that groups of a hundred rows reach it: the rows are the same.
CREATE EXTENSION pg_sift_test CASCADE;
NOTICE:  installing required extension "pg_sift"
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
SET max_parallel_maintenance_workers = 0;
SET sift.async_maintenance = off;
CREATE TABLE gw (id int, g int, created timestamp, score int);
INSERT INTO gw SELECT i, 1 + abs(hashint4(i)) % 100, timestamp '2024-01-01' + abs(hashint4(i + 7)) % 5000000 * interval '1 second', abs(hashint4(i + 3)) % 1000
FROM generate_series(1, 20000) i;
CREATE INDEX gw_i ON gw USING sift_col (id, g, created, score) WITH (key_cut = 'off', order_columns = 'created', order_array_columns = 'created', order_array_group = 'g');
VACUUM (FREEZE, ANALYZE, DISABLE_PAGE_SKIPPING) gw;
CREATE TABLE gp AS SELECT * FROM gw;
ANALYZE gp;
CREATE FUNCTION gw_digest(tbl text, o text, l int, f int) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text;
BEGIN
	SET LOCAL sift.enable_agg = on; SET LOCAL sift.agg_force = on; SET LOCAL enable_indexscan = off; SET LOCAL enable_bitmapscan = off; SET LOCAL enable_indexonlyscan = off;
	EXECUTE format('SELECT md5(string_agg(t::text, ''|'')) FROM (SELECT id, g, created FROM %I ORDER BY %s LIMIT %s OFFSET %s) t', tbl, o, l, f) INTO r;
	RETURN r;
END $$;
-- the same page from the index with the default limit, with the limit under the size of a group, and from the copy without the index
CREATE TEMP TABLE pages (o text, f int);
INSERT INTO pages VALUES ('g, created, id', 3000), ('g DESC, created DESC, id', 3000), ('g, created, id', 15000), ('g DESC, id', 100);
CREATE TEMP TABLE got AS SELECT o, f, gw_digest('gw', o, 30, f) AS a FROM pages;
SET sift.test_limits = 'skip_pending=50';
SELECT p.o, p.f, g.a = gw_digest('gw', p.o, 30, p.f) AS same_under_limit, g.a = gw_digest('gp', p.o, 30, p.f) AS same_as_copy FROM pages p JOIN got g USING (o, f) ORDER BY 1, 2;
            o             |   f   | same_under_limit | same_as_copy 
--------------------------+-------+------------------+--------------
 g DESC, created DESC, id |  3000 | t                | t
 g DESC, id               |   100 | t                | t
 g, created, id           |  3000 | t                | t
 g, created, id           | 15000 | t                | t
(4 rows)

RESET sift.test_limits;
DROP FUNCTION gw_digest(text, text, int, int);
DROP TABLE gw, gp;
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;