-- A consumer that drops most rows pulls past the scan's k hint: the search repeats with more hits (16 times as
-- many below 4096, 4 times beyond), and the rows and their order stay those of the full ranking
CREATE EXTENSION pg_sift;
\ir test/sift_plan.sql
\set ECHO none
CREATE TABLE ov (id int, body text);
INSERT INTO ov SELECT i, repeat('alpha ', 1 + i % 5) || repeat('beta ', i % 4) || 'w' || i % 311 FROM generate_series(1, 6000) i;
CREATE INDEX ov_body ON ov USING sift (body);
CREATE TABLE ov_keep (id int PRIMARY KEY);
INSERT INTO ov_keep SELECT i FROM generate_series(1, 6000) i WHERE i % 67 = 0;
VACUUM ANALYZE ov;
ANALYZE ov_keep;
-- the join keeps 1.5% of the rows: 30 of them need about 2000 pulled, the hint is 30, so the search grows twice
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SELECT sift_plan('SELECT o.id FROM ov o JOIN ov_keep k ON k.id = o.id WHERE o.body @@> ''alpha OR beta''
	ORDER BY sift.score(o.ctid) DESC LIMIT 30') ~ 'Nested Loop(.|\n)*SiftScan' AS nested_loop_over_scan;
 nested_loop_over_scan 
-----------------------
 t
(1 row)

-- the scores of the 30 best joined rows are those of the full ranking filtered by the join
SELECT (SELECT array_agg(s) FROM (SELECT sift.score(o.ctid) AS s FROM ov o JOIN ov_keep k ON k.id = o.id
		WHERE o.body @@> 'alpha OR beta' ORDER BY sift.score(o.ctid) DESC LIMIT 30) j)
	= (SELECT array_agg(s) FROM (SELECT t.s FROM (SELECT id, sift.score(ctid) AS s FROM ov WHERE body @@> 'alpha OR beta'
		ORDER BY sift.score(ctid) DESC OFFSET 0) t JOIN ov_keep k ON k.id = t.id ORDER BY t.s DESC LIMIT 30) r) AS top30;
 top30 
-------
 t
(1 row)

-- drained: every joined row comes out, best first, as many as the full ranking holds
SELECT (SELECT array_agg(s) FROM (SELECT sift.score(o.ctid) AS s FROM ov o JOIN ov_keep k ON k.id = o.id
		WHERE o.body @@> 'alpha OR beta' ORDER BY sift.score(o.ctid) DESC) j)
	= (SELECT array_agg(s) FROM (SELECT t.s FROM (SELECT id, sift.score(ctid) AS s FROM ov WHERE body @@> 'alpha OR beta'
		ORDER BY sift.score(ctid) DESC OFFSET 0) t JOIN ov_keep k ON k.id = t.id ORDER BY t.s DESC) r) AS drained,
	(SELECT sum(o.id) FROM ov o JOIN ov_keep k ON k.id = o.id WHERE o.body @@> 'alpha OR beta') AS rows_sum;
 drained | rows_sum 
---------+----------
 t       |   268335
(1 row)

-- more matches than the first growth holds (1024 hits, then 16384): the search grows again, by 4, and the drained order is still the ranking's
CREATE TABLE ov_big (id int, body text);
INSERT INTO ov_big SELECT i, repeat('alpha ', 1 + i % 7) || 'w' || i % 997 FROM generate_series(1, 20000) i;
CREATE INDEX ov_big_body ON ov_big USING sift (body);
CREATE TABLE ov_big_keep (id int PRIMARY KEY);
INSERT INTO ov_big_keep SELECT i FROM generate_series(1, 20000) i WHERE i % 1000 = 0;
VACUUM ANALYZE ov_big;
ANALYZE ov_big_keep;
SELECT (SELECT array_agg(s) FROM (SELECT sift.score(o.ctid) AS s FROM ov_big o JOIN ov_big_keep k ON k.id = o.id
		WHERE o.body @@> 'alpha' ORDER BY sift.score(o.ctid) DESC) j)
	= (SELECT array_agg(s) FROM (SELECT t.s FROM (SELECT id, sift.score(ctid) AS s FROM ov_big WHERE body @@> 'alpha'
		ORDER BY sift.score(ctid) DESC OFFSET 0) t JOIN ov_big_keep k ON k.id = t.id ORDER BY t.s DESC) r) AS drained_big,
	(SELECT count(*) FROM ov_big o JOIN ov_big_keep k ON k.id = o.id WHERE o.body @@> 'alpha') AS joined;
 drained_big | joined 
-------------+--------
 t           |     20
(1 row)

-- a cursor fetched row by row: its hint is a fraction of the estimated rows (cursor_tuple_fraction), and it is pulled past it
CREATE FUNCTION ov_fetch_all() RETURNS float4[] LANGUAGE plpgsql AS $$
DECLARE
	c CURSOR FOR SELECT sift.score(o.ctid) FROM ov o JOIN ov_keep k ON k.id = o.id WHERE o.body @@> 'alpha OR beta' ORDER BY sift.score(o.ctid) DESC;
	r float4;
	res float4[] := '{}';
BEGIN
	OPEN c;
	LOOP
		FETCH c INTO r;
		EXIT WHEN NOT FOUND;
		res := res || r;
	END LOOP;
	CLOSE c;
	RETURN res;
END $$;
SELECT ov_fetch_all() = (SELECT array_agg(s) FROM (SELECT t.s FROM (SELECT id, sift.score(ctid) AS s FROM ov WHERE body @@> 'alpha OR beta'
		ORDER BY sift.score(ctid) DESC OFFSET 0) t JOIN ov_keep k ON k.id = t.id ORDER BY t.s DESC) r) AS cursor_drained;
 cursor_drained 
----------------
 t
(1 row)

RESET enable_hashjoin;
RESET enable_mergejoin;
DROP FUNCTION sift_plan(text), ov_fetch_all();
DROP TABLE ov, ov_keep, ov_big, ov_big_keep;
DROP EXTENSION pg_sift;