-- ORDER BY d DESC, id DESC LIMIT n [OFFSET m] for one group (key_order = 'g, d'): the unique column after the key breaks the ties of the key, as the primary key ends every order a
-- paged list makes, and the walk of the group's order array serves it (Global Order: true) with a condition on another column too (a soft-delete test on every query), where the
-- walk refused a sort of more than one key under such a condition and read the units. The answers are the table's (a copy without an index is the oracle), whatever the direction, the
-- keys of the order, the groups, the page, the ties (a day holds many rows), the NULL keys and a write buffer.
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;
\set VERBOSITY terse
CREATE TABLE tb (id int PRIMARY KEY, g int, d date, gone timestamp, kind int, pad text);
INSERT INTO tb SELECT i, 1 + (i / 7800) % 38, CASE WHEN i % 97 = 0 THEN NULL ELSE date '2010-01-01' + abs(hashint4(i)) % 5000 END, CASE WHEN i % 500 = 0 THEN timestamp '2021-01-01' END, i % 5, repeat('x', 200)
FROM generate_series(1, 600000) i;
CREATE TABLE tp AS SELECT * FROM tb;
CREATE INDEX tb_i ON tb USING sift_col (id, g, d, gone, kind) WITH (key_order = 'g, d');
VACUUM (FREEZE, ANALYZE) tb;
ANALYZE tp;
-- rows after the build: in the write buffer
INSERT INTO tb SELECT i, 1 + (i / 7800) % 38, date '2010-01-01' + abs(hashint4(i)) % 5000, NULL, i % 5, 'y' FROM generate_series(600001, 600600) i;
INSERT INTO tp SELECT * FROM tb WHERE id > 600000;
-- the answer is the oracle's; 'walk' says the order array served it
CREATE FUNCTION tb_chk(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE pl text; walk bool := false; ra text; rb 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;
	FOR pl IN EXECUTE 'EXPLAIN (ANALYZE, COSTS off, TIMING off, SUMMARY off) ' || replace(q, '@', 'tb') LOOP
		walk := walk OR pl ~ 'Global Order: true';
	END LOOP;
	EXECUTE 'SELECT md5(string_agg(t::text, ''|'')) FROM (' || replace(q, '@', 'tb') || ') t' INTO ra;
	EXECUTE 'SELECT md5(string_agg(t::text, ''|'')) FROM (' || replace(q, '@', 'tp') || ') t' INTO rb;
	RETURN CASE WHEN ra IS DISTINCT FROM rb THEN 'DIFFERS ' || q WHEN walk THEN 'walk' ELSE 'units' END;
END $$;
CREATE FUNCTION tb_all() RETURNS TABLE (cond text, order_by text, result text) LANGUAGE sql AS
$$ SELECT w, o, tb_chk(format('SELECT id, g, d FROM @ WHERE %s ORDER BY %s LIMIT %s OFFSET %s', w, o, l, f))
   FROM unnest(ARRAY['g = 7', 'g = 7 AND gone IS NULL', 'g IN (3, 7, 11) AND gone IS NULL', 'g = 7 AND gone IS NULL AND kind = 3', 'g = 7 AND d >= date ''2018-06-01'' AND gone IS NULL']) w,
		unnest(ARRAY['d DESC, id DESC', 'd, id', 'd DESC NULLS LAST, id DESC']) o, unnest(ARRAY[1, 20]) l, unnest(ARRAY[0, 150]) f $$;
-- the price of the walk against the units moves with the statistics a sample gives, so the plan is said by whether it is used for the form of the list, and the answers by none differing
SELECT cond, count(*) FILTER (WHERE result LIKE 'DIFFERS%') AS differ, count(*) FILTER (WHERE result = 'walk') > 0 AS walks FROM tb_all() GROUP BY 1 ORDER BY 1;
                       cond                        | differ | walks 
---------------------------------------------------+--------+-------
 g = 7                                             |      0 | t
 g = 7 AND d >= date '2018-06-01' AND gone IS NULL |      0 | t
 g = 7 AND gone IS NULL                            |      0 | t
 g = 7 AND gone IS NULL AND kind = 3               |      0 | t
 g IN (3, 7, 11) AND gone IS NULL                  |      0 | t
(5 rows)

DROP TABLE tb, tp;
DROP FUNCTION tb_chk(text), tb_all();
DROP EXTENSION pg_sift_test, pg_sift CASCADE;