-- A top-N over a key that grows with the table and sorts descending starts from the last superblock and visits windows of up to 8 superblocks towards the first.
-- The superblocks of a window are read from its best end, so a window holding the rows of the page reads one unit, not every unit of the window in table order.
-- A page holds one row, so a superblock is 256 rows and 60 of them make the table.
CREATE EXTENSION pg_sift;
SET sift.test_col_legacy_options = on;
SET client_min_messages = warning;
SET max_parallel_maintenance_workers = 0;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 10000; SET random_page_cost = 10000;
CREATE TABLE rw (id int, k int, j int, pad text) WITH (fillfactor = 10, autovacuum_enabled = false);
INSERT INTO rw SELECT i, i, i % 7, repeat('x', 600) FROM generate_series(1, 15360) i;
CREATE INDEX rw_idx ON rw USING sift_col (id, k, j) WITH (order_columns = 'k, j');
VACUUM (FREEZE, ANALYZE) rw;
-- 'same' when the rows and their order are those of the normal plan; the units read by the sorted runs and the units the zones pruned
CREATE FUNCTION chk(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text; r text := ''; a text; b text;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) ' || q LOOP
		IF l LIKE '%units read by sorted run%' OR l LIKE '%Units: %' THEN r := r || ' ' || trim(l); END IF;
	END LOOP;
	EXECUTE format('SELECT string_agg(x::text, E'','') FROM (%s) x', q) INTO a;
	SET LOCAL sift.enable_topn = off; SET LOCAL sift.enable_agg = off;
	EXECUTE format('SELECT string_agg(x::text, E'','') FROM (%s) x', q) INTO b;
	RESET sift.enable_topn; RESET sift.enable_agg;
	RETURN format('%s |%s', CASE WHEN a IS NOT DISTINCT FROM b THEN 'same' ELSE 'DIFFERENT' END, r);
END $$;
SELECT n, chk(q) FROM (VALUES
	(1, 'SELECT id, k FROM rw WHERE k <= 7000 ORDER BY k DESC LIMIT 10'),
	(2, 'SELECT id, k FROM rw WHERE k <= 7000 AND j = 3 ORDER BY k DESC LIMIT 10'),
	(3, 'SELECT id, k FROM rw WHERE k BETWEEN 3000 AND 7000 ORDER BY k DESC LIMIT 400'),
	(4, 'SELECT id, k FROM rw WHERE k <= 100 ORDER BY k DESC LIMIT 10'),
	(5, 'SELECT id, k FROM rw ORDER BY k DESC LIMIT 10'),
	(6, 'SELECT id, k FROM rw WHERE k < 0 ORDER BY k DESC LIMIT 10'),
	(7, 'SELECT id, k FROM rw WHERE k <= 7000 ORDER BY k DESC OFFSET 600 LIMIT 10')) v (n, q) ORDER BY n;
 n |                                                                     chk                                                                      
---+----------------------------------------------------------------------------------------------------------------------------------------------
 1 | same | Top-N Run: 27 superblocks skipped, 1 units read by sorted run, 1 units read without filters, 10 heap fetches Units: 1 read, 32 pruned
 2 | same | Units: 1 read, 32 pruned
 3 | same | Top-N Run: 25 superblocks skipped, 3 units read by sorted run, 3 units read without filters, 88 heap fetches Units: 3 read, 43 pruned
 4 | same | Top-N Run: 0 superblocks skipped, 1 units read by sorted run, 1 units read without filters, 10 heap fetches Units: 1 read, 59 pruned
 5 | same | Top-N Run: 59 superblocks skipped, 1 units read by sorted run, 10 heap fetches Units: 1 read
 6 | same | Units: 0 read, 60 pruned
 7 | same | Top-N Run: 24 superblocks skipped, 4 units read by sorted run, 4 units read without filters, 10 heap fetches Units: 4 read, 32 pruned
(7 rows)

DROP FUNCTION chk(text);
DROP TABLE rw;
DROP EXTENSION pg_sift;