-- The margin a lion custom path has to win by (DESIGN.md §39).
--
-- A lion custom path's own price is divided by pg_lion.pushdown_margin, 1
-- (none) by default.  This test lists the settings; shows that the margin is a threshold on the ratio
-- of the two prices, that it divides a lion path's cost exactly, that core's
-- enable_* settings still force a lion plan they leave enabled however dear
-- the margin makes it, and a margin above 1 one they do not (LionOrdered,
-- which enable_indexscan disables with core's ordered index scans), and that
-- it is not applied where it hedges nothing - against a plan
-- forced with nothing of core's left enabled, or the AM's own scan of a lion
-- index.  The rows of every plan are checked against the same query run
-- with lion's paths off and sequential scans only.
\set VERBOSITY terse
SET client_min_messages = warning;
LOAD 'pg_lion';
CREATE EXTENSION IF NOT EXISTS pg_lion;
RESET client_min_messages;
-- VACUUM can only set all-visible once the commit record is on disk
SET synchronous_commit = on;
-- a sample of every row: the statistics, and the plans, do not depend on it
SET default_statistics_target = 1000;
SET max_parallel_workers_per_gather = 0;
-- ---------- 1. the settings ----------
-- a user setting, from a hundredth to a thousand
SELECT name, setting, boot_val, min_val, max_val, context
  FROM pg_settings
 WHERE name = 'pg_lion.pushdown_margin';
          name           | setting | boot_val | min_val | max_val | context 
-------------------------+---------+----------+---------+---------+---------
 pg_lion.pushdown_margin | 1       | 1        | 0.01    | 1000    | user
(1 row)

-- the cold reads' (DESIGN.md §39, "A nested loop's cold reads", "A bitmap
-- heap scan's cold reads"): the price of a page read synchronously and of
-- one read ahead, multiples of seq_page_cost from 0 up as every cost
-- setting is, and the most they take off a lion path's price, a divisor
-- from 1 (nothing) to 10
SELECT name, setting, boot_val, min_val, max_val::float8 > 1e300 AS unbounded,
	   context, unit IS NULL AS no_unit
  FROM pg_settings
 WHERE name ~ '^pg_lion\.cold_'
 ORDER BY name;
             name              | setting | boot_val | min_val | unbounded | context | no_unit 
-------------------------------+---------+----------+---------+-----------+---------+---------
 pg_lion.cold_max_discount     | 2       | 2        | 1       | f         | user    | t
 pg_lion.cold_page_cost        | 50      | 50       | 0       | t         | user    | t
 pg_lion.cold_stream_page_cost | 6.25    | 6.25     | 0       | t         | user    | t
(3 rows)

SET pg_lion.cold_max_discount = 0.99;
ERROR:  0.99 is outside the valid range for parameter "pg_lion.cold_max_discount" (1 .. 10)
SET pg_lion.cold_max_discount = 10.5;
ERROR:  10.5 is outside the valid range for parameter "pg_lion.cold_max_discount" (1 .. 10)
SET pg_lion.cold_stream_page_cost = -1;
ERROR:  -1 is outside the valid range for parameter "pg_lion.cold_stream_page_cost" (0 .. 1.79769e+308)
SET pg_lion.cold_page_cost = -1;
ERROR:  -1 is outside the valid range for parameter "pg_lion.cold_page_cost" (0 .. 1.79769e+308)
SET pg_lion.cold_max_discount = 1;
SET pg_lion.cold_max_discount = 10;
SET pg_lion.cold_stream_page_cost = 0;
SHOW pg_lion.cold_max_discount;
 pg_lion.cold_max_discount 
---------------------------
 10
(1 row)

SHOW pg_lion.cold_stream_page_cost;
 pg_lion.cold_stream_page_cost 
-------------------------------
 0
(1 row)

RESET pg_lion.cold_max_discount;
RESET pg_lion.cold_stream_page_cost;
-- 60000 rows: g20, g200 and c2 at random, k a permutation of the ids under a
-- btree for the ordered scan
CREATE TABLE lcr (id int, k int, g20 int, g200 int, c2 int, pad text)
	WITH (autovacuum_enabled = off);
INSERT INTO lcr
SELECT i, (i::bigint * 7919 % 60013)::int,
	   (hashint4(i) & 2147483647) % 20,
	   (hashint4(i + 1000000) & 2147483647) % 200,
	   (hashint4(i + 2000000) & 2147483647) % 2,
	   repeat('x', 60)
  FROM generate_series(1, 60000) i;
CREATE INDEX lcr_g20 ON lcr USING lion (g20);
CREATE INDEX lcr_g200 ON lcr USING lion (g200);
CREATE INDEX lcr_c2 ON lcr USING lion (c2);
CREATE INDEX lcr_k ON lcr (k, id);
VACUUM (FREEZE, ANALYZE) lcr;
-- the same rows in three range partitions, g200 and c2 indexed in each, and
-- a default partition that stays empty and is vacuumed with the rest: core
-- scans it sequentially, at no price
CREATE TABLE lcrp (id int, g200 int, c2 int) PARTITION BY RANGE (id);
CREATE TABLE lcrp0 PARTITION OF lcrp FOR VALUES FROM (1) TO (20001);
CREATE TABLE lcrp1 PARTITION OF lcrp FOR VALUES FROM (20001) TO (40001);
CREATE TABLE lcrp2 PARTITION OF lcrp FOR VALUES FROM (40001) TO (60001);
CREATE TABLE lcrpd PARTITION OF lcrp DEFAULT;
INSERT INTO lcrp SELECT id, g200, c2 FROM lcr;
CREATE INDEX lcrp_g200 ON lcrp USING lion (g200);
CREATE INDEX lcrp_c2 ON lcrp USING lion (c2);
VACUUM (FREEZE, ANALYZE) lcrp;
-- the FK-side join's shape: a fact of 40000 rows over 2000 keys, 20 rows a
-- key, x of 10 values; its dimension, attr of 7
CREATE TABLE lcrd (pk int8 PRIMARY KEY, attr int);
INSERT INTO lcrd SELECT i, i % 7 FROM generate_series(1, 2000) i;
CREATE TABLE lcrf (id int, fk int8, x int);
INSERT INTO lcrf SELECT i, (i * 7919) % 2000 + 1, i % 10 FROM generate_series(1, 40000) i;
CREATE INDEX lcrf_fk ON lcrf USING lion (fk);
CREATE INDEX lcrf_x ON lcrf USING lion (x);
VACUUM (FREEZE, ANALYZE) lcrd;
VACUUM (FREEZE, ANALYZE) lcrf;
/*
 * lcr_set() sets, for the rest of the statement, every setting this test
 * moves to its default, and then the settings in sw, name and value in turn:
 * a statement that calls the functions below more than once sees each call's
 * settings alone.
 */
CREATE FUNCTION lcr_set(sw text[]) RETURNS void
LANGUAGE plpgsql AS $$
DECLARE
	n text;
	i int;
BEGIN
	FOREACH n IN ARRAY ARRAY['pg_lion.pushdown_margin',
							 'pg_lion.enable_count_pushdown',
							 'pg_lion.enable_semijoin', 'pg_lion.enable_ordered_scan',
							 'pg_lion.enable_plain_scan',
							 'enable_seqscan', 'enable_indexscan',
							 'enable_indexonlyscan', 'enable_bitmapscan',
							 'enable_tidscan', 'enable_sort', 'enable_hashagg',
							 'enable_hashjoin', 'enable_mergejoin',
							 'enable_nestloop'] LOOP
		PERFORM set_config(n, (SELECT boot_val FROM pg_settings WHERE name = n),
						   true);
	END LOOP;
	FOR i IN 1 .. coalesce(array_length(sw, 1), 0) BY 2 LOOP
		PERFORM set_config(sw[i], sw[i + 1], true);
	END LOOP;
END $$;
/* The estimated total cost of q's plan under the settings in sw. */
CREATE FUNCTION lcr_cost(q text, sw text[] DEFAULT '{}') RETURNS float8
LANGUAGE plpgsql AS $$
DECLARE
	p json;
BEGIN
	PERFORM lcr_set(sw);
	EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO p;
	RETURN (p->0->'Plan'->>'Total Cost')::float8;
END $$;
/*
 * The topmost lion node of q's plan under the settings in sw - LionCount,
 * LionSemiJoin, LionAntiJoin, LionOrdered - or 'core' where it has none.
 */
CREATE FUNCTION lcr_lion(q text, sw text[] DEFAULT '{}') RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
	ln text;
BEGIN
	PERFORM lcr_set(sw);
	FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF ln ~ 'Custom Scan \(Lion' THEN
			RETURN substring(ln FROM 'Custom Scan \((Lion[A-Za-z]*)\)');
		END IF;
	END LOOP;
	RETURN 'core';
END $$;
/*
 * q's rows under the settings in sw against its rows with lion's paths off
 * and sequential scans only, as multisets.
 */
CREATE FUNCTION lcr_same(q text, sw text[] DEFAULT '{}') RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
	n bigint;
	d bigint;
BEGIN
	PERFORM lcr_set(sw);
	EXECUTE format('CREATE TEMP TABLE lcr_a AS SELECT s::text AS r FROM (%s) s', q);
	PERFORM lcr_set('{pg_lion.enable_count_pushdown, off, pg_lion.enable_semijoin, off,
					  pg_lion.enable_ordered_scan, off, enable_indexscan, off,
					  enable_indexonlyscan, off, enable_bitmapscan, off}');
	EXECUTE format('CREATE TEMP TABLE lcr_b AS SELECT s::text AS r FROM (%s) s', q);
	EXECUTE 'SELECT count(*) FROM lcr_a' INTO n;
	EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM lcr_a EXCEPT ALL SELECT * FROM lcr_b) a)'
			' + (SELECT count(*) FROM (SELECT * FROM lcr_b EXCEPT ALL SELECT * FROM lcr_a) b)'
		INTO d;
	EXECUTE 'DROP TABLE lcr_a, lcr_b';
	IF d <> 0 THEN
		RETURN format('MISMATCH: %s rows differ', d);
	END IF;
	RETURN format('same, %s rows', n);
END $$;
-- ---------- 2. the margin ----------
-- a GROUP BY the node wins by far against a hash aggregate (the ratio well
-- above the dearest margin): the margin is a
-- threshold on the ratio of its price to core's best - just above the ratio
-- the node is chosen, just below it core's plan is
SELECT lion < core * 0.9 AS wins_by_far,
	   lcr_lion(q, ARRAY['pg_lion.pushdown_margin',
						 least(lion / core * 1.05, 1)::text]) AS just_above,
	   lcr_lion(q, ARRAY['pg_lion.pushdown_margin',
						 greatest(lion / core * 0.95, 0.01)::text]) AS just_below
  FROM (SELECT q, lcr_cost(q, '{pg_lion.pushdown_margin, 1}') AS lion,
			   lcr_cost(q, '{pg_lion.enable_count_pushdown, off}') AS core
		  FROM (VALUES ('SELECT g20, count(*) FROM lcr GROUP BY g20')) v(q)) c;
 wins_by_far | just_above | just_below 
-------------+------------+------------
 t           | LionCount  | core
(1 row)

-- it divides the node's own cost exactly, and nothing above it is marked up:
-- the GROUP BY's, and a LionOrdered's under its LIMIT, which is chosen over
-- core's ordered index scan at either margin
SELECT n, lcr_lion(q, sw) AS plan,
	   round((lcr_cost(q, sw || '{pg_lion.pushdown_margin, 0.5}') /
			  lcr_cost(q, sw || '{pg_lion.pushdown_margin, 1}'))::numeric, 2) AS half_margin
  FROM (VALUES ('group', 'SELECT g20, count(*) FROM lcr GROUP BY g20',
				'{}'::text[]),
			   ('ordered', 'SELECT id FROM lcr WHERE g200 = 17 ORDER BY k, id LIMIT 10',
				'{}'::text[])) v(n, q, sw);
    n    |    plan     | half_margin 
---------+-------------+-------------
 group   | LionCount   |        2.00
 ordered | LionOrdered |        2.00
(2 rows)

-- core's enable_* settings still force the GROUP BY's node at the dearest
-- margin, and a margin above 1 forces LionOrdered, which enable_indexscan
-- would disable with core's ordered index scan; neither is chosen at the
-- dearest margin unforced, and lion's switches still leave them out with no
-- margin
SELECT n, lcr_lion(q, sw) AS forced,
	   lcr_lion(q, unforced || '{pg_lion.pushdown_margin, 0.01}') AS unforced,
	   lcr_lion(q, ARRAY['pg_lion.pushdown_margin', '1', switch, 'off']) AS switched_off
  FROM (VALUES ('group', 'SELECT g20, count(*) FROM lcr GROUP BY g20',
				'{enable_hashagg, off, enable_sort, off, pg_lion.pushdown_margin, 0.01}'::text[],
				'{}'::text[], 'pg_lion.enable_count_pushdown'),
			   ('ordered', 'SELECT id FROM lcr WHERE g200 = 17 ORDER BY k, id LIMIT 10',
				'{enable_seqscan, off, enable_bitmapscan, off, pg_lion.pushdown_margin, 1000}'::text[],
				'{}'::text[], 'pg_lion.enable_ordered_scan')) v(n, q, sw, unforced, switch);
    n    |   forced    | unforced | switched_off 
---------+-------------+----------+--------------
 group   | LionCount   | core     | core
 ordered | LionOrdered | core     | core
(2 rows)

-- and none where it hedges nothing: a plan core's enable_* settings force,
-- with nothing of core's left to compete (the aggregate over lion's walk of
-- the B-tree, a LionBtreeScan, is lion's own plan and no competitor), costs
-- the same at every margin (lion's own price, which its choices among its
-- own forms are made in).  LionOrdered is not forced so:
-- the enable_indexscan that leaves core no ordered index scan disables it
-- too, and which of two disabled plans wins is a matter of the major
-- (disabled nodes from 18, disable_cost before).
SELECT n, lcr_lion(q, sw) AS plan,
	   lcr_cost(q, sw || '{pg_lion.pushdown_margin, 0.01}') =
	   lcr_cost(q, sw || '{pg_lion.pushdown_margin, 1}') AS same_at_every_margin
  FROM (VALUES ('count, forced', 'SELECT count(*) FROM lcr WHERE g200 = 17 AND c2 = 1',
				'{enable_seqscan, off, enable_bitmapscan, off, enable_tidscan, off, pg_lion.enable_plain_scan, off}'::text[]),
			   ('group, forced', 'SELECT g20, count(*) FROM lcr GROUP BY g20',
				'{enable_hashagg, off, enable_sort, off}'::text[]),
			   ('semi, forced', 'SELECT d.pk FROM lcrd d WHERE EXISTS (SELECT 1 FROM lcrf f WHERE f.fk = d.pk AND f.x = 3)',
				'{enable_hashjoin, off, enable_mergejoin, off, enable_nestloop, off}'::text[])) v(n, q, sw);
       n       |     plan     | same_at_every_margin 
---------------+--------------+----------------------
 count, forced | LionCount    | t
 group, forced | LionCount    | t
 semi, forced  | LionSemiJoin | t
(3 rows)

-- a count whose cheapest competitor is the AM's own scan of a lion index (an
-- aggregate over its plain index scan here; bitmap scans are off so that the
-- AM's BitmapAnd, whose price moves with the indexes' sizes, is never the
-- cheapest), priced by lion's model as the node is, costs the same at every
-- margin - at a hundredth it would be core's plan; over a partitioned table, where the competitor is an
-- aggregate over an Append of the partitions' plain index scans and the
-- sequential scan of the empty one, the same
SELECT n, lcr_lion(q, '{enable_bitmapscan, off}') AS plan,
	   lcr_cost(q, '{enable_bitmapscan, off, pg_lion.pushdown_margin, 0.01}') =
	   lcr_cost(q, '{enable_bitmapscan, off, pg_lion.pushdown_margin, 1}') AS same_at_every_margin
  FROM (VALUES ('plain', 'SELECT count(*) FROM lcr WHERE g200 = 17 AND c2 = 1'),
			   ('partitioned', 'SELECT count(*) FROM lcrp WHERE g200 = 17 AND c2 = 1')) v(n, q);
      n      |   plan    | same_at_every_margin 
-------------+-----------+----------------------
 plain       | LionCount | t
 partitioned | LionCount | t
(2 rows)

-- ---------- 3. the AM's own scans ----------
-- the AM's own scans (the count's pushdown off, sequential scans off) cost
-- the same at every margin; LionOrdered (chosen over core's ordered index
-- scan) is marked up by it
SELECT n, lcr_lion(q, sw) AS plan,
	   lcr_cost(q, sw || '{pg_lion.pushdown_margin, 0.5}') =
	   lcr_cost(q, sw || '{pg_lion.pushdown_margin, 1}') AS same_at_every_margin
  FROM (VALUES ('am scans', 'SELECT count(*) FROM lcr WHERE g200 = 17 AND c2 = 1',
				'{pg_lion.enable_count_pushdown, off, enable_seqscan, off}'::text[]),
			   ('ordered', 'SELECT id FROM lcr WHERE g200 = 17 ORDER BY k, id LIMIT 10',
				'{}'::text[])) v(n, q, sw);
    n     |    plan     | same_at_every_margin 
----------+-------------+----------------------
 am scans | core        | t
 ordered  | LionOrdered | f
(2 rows)

-- ---------- 4. the answers ----------
-- every plan above against lion's paths off and sequential scans only
SELECT n, lcr_same(q, sw) AS answer
  FROM (VALUES ('count', 'SELECT count(*) FROM lcr WHERE g200 = 17 AND c2 = 1', '{}'::text[]),
			   ('count, forced', 'SELECT count(*) FROM lcr WHERE g200 = 17 AND c2 = 1',
				'{enable_seqscan, off, enable_bitmapscan, off, enable_tidscan, off, pg_lion.enable_plain_scan, off}'::text[] || '{pg_lion.pushdown_margin, 0.01}'::text[]),
			   ('count over a scan', 'SELECT count(*) FROM lcr WHERE g200 = 17',
				'{enable_bitmapscan, off, pg_lion.enable_plain_scan, off}'),
			   ('ordered', 'SELECT id FROM lcr WHERE g200 = 17 ORDER BY k, id LIMIT 10', '{enable_seqscan, off, enable_bitmapscan, off, enable_tidscan, off, pg_lion.pushdown_margin, 1000}'::text[]),
			   ('group', 'SELECT g20, count(*) FROM lcr GROUP BY g20',
				'{enable_sort, off}'),
			   ('fkjoin', 'SELECT count(*) FROM lcrf f JOIN lcrd d ON f.fk = d.pk WHERE d.attr = 2', '{enable_hashjoin, off, enable_mergejoin, off, enable_nestloop, off}'::text[]),
			   ('semi', 'SELECT d.pk FROM lcrd d WHERE EXISTS (SELECT 1 FROM lcrf f WHERE f.fk = d.pk AND f.x = 3)', '{enable_hashjoin, off, enable_mergejoin, off, enable_nestloop, off}'::text[]),
			   ('anti', 'SELECT d.pk FROM lcrd d WHERE NOT EXISTS (SELECT 1 FROM lcrf f WHERE f.fk = d.pk AND f.x = 3)', '{enable_hashjoin, off, enable_mergejoin, off, enable_nestloop, off}'::text[])) v(n, q, sw);
         n         |     answer      
-------------------+-----------------
 count             | same, 1 rows
 count, forced     | same, 1 rows
 count over a scan | same, 1 rows
 ordered           | same, 10 rows
 group             | same, 20 rows
 fkjoin            | same, 1 rows
 semi              | same, 200 rows
 anti              | same, 1800 rows
(8 rows)

DROP TABLE lcr, lcrp, lcrf, lcrd;
DROP FUNCTION lcr_same(text, text[]);
DROP FUNCTION lcr_lion(text, text[]);
DROP FUNCTION lcr_cost(text, text[]);
DROP FUNCTION lcr_set(text[]);