-- ORDER BY k LIMIT n with a condition on another column is read from the order array of k, the heap row of each entry testing the condition, while the rows pass: the n
-- take n over the pass rate fetches, and the walk gives the rows to the units when they are more than four times n or a quarter of the heap's pages (gord_giveup). The planner
-- priced such a scan as the units it may fall back to (3 times a scan of the unfiltered order) however many rows passed; it prices the fetches of the statistics' pass rate: a
-- condition that most rows pass costs the order alone, one that 10% pass a hundred and fifty fetches, and the cost grows with the fetches the pass rate asks for.
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE feed (id int, k int, q50 int, q10 int, q1 int, gone timestamptz, pad text) WITH (autovacuum_enabled = false);
ALTER TABLE feed ALTER pad SET STORAGE PLAIN;
INSERT INTO feed SELECT i, (i::bigint * 7919) % 60001, (hashint4(i) & 1023 < 512)::int, (hashint4(i + 1) & 1023 < 102)::int, (hashint4(i + 2) & 1023 < 10)::int,
	CASE WHEN i % 50 = 0 THEN now() END, repeat('x', 900) FROM generate_series(1, 60000) i;
CREATE INDEX feed_i ON feed USING sift_col (id, k, q50, q10, q1, gone) WITH (order_columns = 'k', order_array_columns = 'k');
VACUUM (FREEZE, ANALYZE) feed;

CREATE FUNCTION page_cost(cond text) RETURNS float8 LANGUAGE sql AS
$$ SELECT scan_cost FROM sift.explain_why('SELECT id, k FROM feed WHERE ' || cond || ' ORDER BY k LIMIT 20') $$;

SELECT page_cost('q50 = 1') < 2 * page_cost('true') AS half_pass_costs_the_order,
	page_cost('gone IS NULL') < 2 * page_cost('true') AS soft_delete_costs_the_order,
	page_cost('q10 = 1') < page_cost('q1 = 1') AS cost_grows_with_the_fetches,
	page_cost('q50 = 1') < page_cost('q10 = 1') AS cost_grows_from_half_to_tenth;

DROP FUNCTION page_cost(text);
DROP TABLE feed;
DROP EXTENSION pg_sift;