-- The dimension by probe (DESIGN.md §46, LionProbeJoin):
--
--   SELECT <predicates of d's columns, CASE over them>, <f's columns>, count(*)
--     FROM f JOIN d ON f.fk = d.pk
--    WHERE <f's clauses> AND <d's clauses lion indexes answer>
--      [AND NOT EXISTS (SELECT 1 FROM s WHERE s.a = f.fk ...)]
--    GROUP BY ...
--
-- with no lion index on f.  The node reads f's rows from core's own path,
-- looks each key up in d's primary key, and tests the TID found against sets
-- of d's lion indexes built once: the WHERE's, and a label set an outcome of
-- each column the GROUP BY's predicates read.  The heap is read on pages the
-- visibility map does not call all-visible, and for every row found when the
-- WHERE's set is inexact, to recheck the WHERE on the heap tuple.  Every
-- answer is checked against core's plan with the node switched off.
\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;
SET max_parallel_workers_per_gather = 0;
/*
 * pj() runs q with every join method disabled, so that core has no join the
 * node must beat on price, and says whether the node is the plan (and
 * whether in its parallel form); then again with the node and the rest of
 * lion's pushdowns off, sequential scans only and the joins on, and compares
 * the two answers as multisets.  The settings are the transaction's: they
 * go back to the caller's when it ends.
 */
CREATE FUNCTION pj(q text) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
	ln text;
	node boolean := false;
	parallel boolean := false;
	how text;
	nrows bigint;
	ndiff bigint;
BEGIN
	PERFORM set_config('enable_hashjoin', 'off', true);
	PERFORM set_config('enable_mergejoin', 'off', true);
	PERFORM set_config('enable_nestloop', 'off', true);
	FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		node := node OR ln LIKE '%Custom Scan (LionProbeJoin)%';
		parallel := parallel OR ln LIKE '%Partial %Aggregate%';
	END LOOP;
	how := CASE WHEN NOT node THEN 'no probe join'
				WHEN parallel THEN 'probe join, parallel'
				ELSE 'probe join' END;
	EXECUTE format('CREATE TEMP TABLE pj_on AS SELECT pj_q::text AS r FROM (%s) pj_q', q);

	PERFORM set_config('pg_lion.enable_probe_join', 'off', true);
	PERFORM set_config('pg_lion.enable_count_pushdown', 'off', true);
	PERFORM set_config('enable_hashjoin', 'on', true);
	PERFORM set_config('enable_mergejoin', 'on', true);
	PERFORM set_config('enable_nestloop', 'on', true);
	PERFORM set_config('enable_bitmapscan', 'off', true);
	PERFORM set_config('enable_indexscan', 'off', true);
	PERFORM set_config('enable_indexonlyscan', 'off', true);
	EXECUTE format('CREATE TEMP TABLE pj_off AS SELECT pj_q::text AS r FROM (%s) pj_q', q);

	EXECUTE 'SELECT count(*) FROM pj_off' INTO nrows;
	EXECUTE 'SELECT (SELECT count(*) FROM (TABLE pj_on EXCEPT ALL TABLE pj_off) a)'
			' + (SELECT count(*) FROM (TABLE pj_off EXCEPT ALL TABLE pj_on) b)'
		INTO ndiff;
	EXECUTE 'DROP TABLE pj_on, pj_off';
	IF ndiff <> 0 THEN
		RETURN format('MISMATCH: %s rows differ', ndiff);
	END IF;
	RETURN format('%s, %s rows', how, nrows);
END $$;
/* The answer itself, through the node, the joins disabled. */
CREATE FUNCTION pj_val(q text) RETURNS SETOF text
LANGUAGE plpgsql AS $$
BEGIN
	PERFORM set_config('enable_hashjoin', 'off', true);
	PERFORM set_config('enable_mergejoin', 'off', true);
	PERFORM set_config('enable_nestloop', 'off', true);
	RETURN QUERY EXECUTE format('SELECT pj_q::text FROM (%s) pj_q ORDER BY 1', q);
END $$;
/*
 * The plan of q, the joins disabled, in a form every supported release
 * prints the same way: 18's "Disabled: true" lines dropped, subplans named
 * "expr_N", the memory of the Agg and of a sort and their keys - which
 * repeat the grouped expressions whole - left out.
 * With `run`, under EXPLAIN ANALYZE: the actual row counts left out, and
 * the node's counters printed whole when `exact`, or as "> 0" and "0" when
 * they depend on what an earlier run left behind (index entries a scan
 * marked dead, which the next one skips).
 */
CREATE FUNCTION pj_explain(q text, run boolean DEFAULT false,
						   exact boolean DEFAULT true)
RETURNS SETOF text
LANGUAGE plpgsql AS $$
DECLARE
	ln text;
	opts text := 'COSTS OFF';
BEGIN
	PERFORM set_config('enable_hashjoin', 'off', true);
	PERFORM set_config('enable_mergejoin', 'off', true);
	PERFORM set_config('enable_nestloop', 'off', true);
	IF run THEN
		opts := 'ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF';
	END IF;
	FOR ln IN EXECUTE 'EXPLAIN (' || opts || ') ' || q LOOP
		CONTINUE WHEN ln ~ '^\s*(Disabled|Batches|Memory Usage|Worker \d|Storage|Index Searches|Estimates|Buffers|Sort Method)';
		CONTINUE WHEN ln ~ '^\s*(Group|Sort) Key: ';
		ln := regexp_replace(ln, '\s*\(actual.*\)', '');
		ln := regexp_replace(ln, '(InitPlan|SubPlan) (\d+)', '\1 expr_\2', 'g');
		IF NOT exact AND
		   ln ~ '^\s*(Probes|Null Keys|Rows Removed by [A-Za-z ]+|Heap TIDs [A-Za-z ]+|Lion Sets|Anti Join Keys): ' THEN
			ln := regexp_replace(ln, '\m[1-9][0-9]*\M', '> 0', 'g');
		END IF;
		RETURN NEXT ln;
	END LOOP;
END $$;
-- ---------- the tables ----------
CREATE TYPE pj_mood AS ENUM ('calm', 'busy', 'idle', 'gone');
CREATE TABLE pj_customers (
	id int PRIMARY KEY,
	region text,			-- lion: four values and NULL
	tier int NOT NULL,		-- lion, with region: 1 to 5
	active bool,			-- lion: true, false and NULL
	mood pj_mood,			-- lion: an enum
	tags int[],				-- lion: a multi-key column
	note text				-- no index: what a HOT update changes
) WITH (autovacuum_enabled = off);
INSERT INTO pj_customers
SELECT i,
	   CASE WHEN i % 23 = 0 THEN NULL
			ELSE (ARRAY['north', 'south', 'east', 'west'])[1 + i % 4] END,
	   1 + (i / 4) % 5,
	   CASE WHEN i % 31 = 0 THEN NULL ELSE i % 3 <> 0 END,
	   (enum_range(NULL::pj_mood))[1 + (i / 7) % 4],
	   CASE WHEN i % 29 = 0 THEN NULL
			ELSE ARRAY[i % 6, 6 + i % 5] END,
	   'n' || i
  FROM generate_series(1, 6000) i;
CREATE INDEX pj_customers_rt ON pj_customers USING lion (region, tier);
CREATE INDEX pj_customers_active ON pj_customers USING lion (active);
CREATE INDEX pj_customers_mood ON pj_customers USING lion (mood);
CREATE INDEX pj_customers_tags ON pj_customers USING lion (tags);
-- the fact: no lion index; a NULL key every 50th row, and a key past the
-- dimension's every 47th
CREATE TABLE pj_orders (
	id int NOT NULL,
	customer_id int,
	channel int NOT NULL,
	amount int NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO pj_orders
SELECT i,
	   CASE WHEN i % 50 = 0 THEN NULL
			WHEN i % 47 = 0 THEN 100000 + i
			ELSE 1 + abs(hashint4(i)) % 6000 END,
	   i % 3,
	   abs(hashint4(i + 7)) % 100
  FROM generate_series(1, 30000) i;
-- the anti join's small table: keys of customers, keys of none, and NULLs
CREATE TABLE pj_blocked (customer_id int, reason text);
INSERT INTO pj_blocked
SELECT CASE WHEN i % 10 = 0 THEN NULL
			WHEN i % 10 = 1 THEN 200000 + i
			ELSE i * 37 END,
	   CASE WHEN i % 2 = 0 THEN 'audit' ELSE 'fraud' END
  FROM generate_series(1, 120) i;
VACUUM (FREEZE, ANALYZE) pj_customers, pj_orders, pj_blocked;
-- ---------- 1. the node, against core's plan ----------
-- a CASE over two lion columns, a fact column beside it
SELECT pj($$SELECT CASE WHEN c.region IN ('north', 'east') THEN 'ne'
						WHEN c.tier = 1 THEN 't1' ELSE 'other' END AS k,
				   o.channel, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.active AND c.tier < 4
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 9 rows
(1 row)

SELECT * FROM pj_val($$SELECT CASE WHEN c.region IN ('north', 'east') THEN 'ne'
								   WHEN c.tier = 1 THEN 't1' ELSE 'other' END AS k,
							  o.channel, count(*)
						 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
						WHERE c.active AND c.tier < 4
						GROUP BY 1, 2$$);
     pj_val     
----------------
 (ne,0,1804)
 (ne,1,1740)
 (ne,2,1851)
 (other,0,1309)
 (other,1,1294)
 (other,2,1256)
 (t1,0,690)
 (t1,1,641)
 (t1,2,665)
(9 rows)

-- booleans as the group keys, a NULL truth among them
SELECT pj($$SELECT c.active, c.region = 'south' AS s, c.region IS NULL AS n, count(*)
			  FROM pj_orders o JOIN pj_customers c ON c.id = o.customer_id
			 GROUP BY 1, 2, 3$$);
         pj         
--------------------
 probe join, 9 rows
(1 row)

SELECT * FROM pj_val($$SELECT c.active, c.region = 'south' AS s, count(*)
						 FROM pj_orders o JOIN pj_customers c ON c.id = o.customer_id
						GROUP BY 1, 2$$);
   pj_val    
-------------
 (,,38)
 (,f,638)
 (,t,222)
 (f,,402)
 (f,f,6739)
 (f,t,2206)
 (t,,801)
 (t,f,13352)
 (t,t,4376)
(9 rows)

-- a WHERE on the dimension and fact columns alone in the GROUP BY
SELECT pj($$SELECT o.channel, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.region = 'west' AND c.tier IN (2, 3)
			 GROUP BY 1$$);
         pj         
--------------------
 probe join, 3 rows
(1 row)

-- ... and no GROUP BY at all
SELECT pj($$SELECT count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.mood = 'busy'$$);
         pj         
--------------------
 probe join, 1 rows
(1 row)

-- predicates and the fact's own WHERE, with arithmetic over the predicates
SELECT pj($$SELECT (CASE WHEN c.tier IN (4, 5) THEN 10 ELSE 0 END)
				   + (CASE WHEN c.active THEN 1 ELSE 0 END) AS score,
				   o.amount < 50 AS small, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE o.amount BETWEEN 10 AND 80
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 8 rows
(1 row)

-- an enum and a multi-key column, a HAVING
SELECT pj($$SELECT CASE WHEN c.mood IN ('calm', 'idle') THEN 'quiet'
						WHEN c.mood = 'busy' THEN 'busy' END AS m,
				   c.tags && '{1,2}' AS t12, c.tags @> '{7}' AS t7, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.region <> 'east'
			 GROUP BY 1, 2, 3
			HAVING count(*) > 100$$);
         pj          
---------------------
 probe join, 15 rows
(1 row)

-- an aggregate of a fact column
SELECT pj($$SELECT c.tier = 5 AS top, sum(o.amount), max(o.channel), count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE NOT c.active
			 GROUP BY 1$$);
         pj         
--------------------
 probe join, 2 rows
(1 row)

-- the dimension rows the WHERE leaves out, the keys with no dimension row
-- and the NULL keys count nowhere: the counts below add up to the join's
SELECT * FROM pj_val($$SELECT c.region IS NULL AS nul, count(*)
						 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
						WHERE c.tier = 3
						GROUP BY 1$$);
  pj_val  
----------
 (f,5501)
 (t,261)
(2 rows)

SELECT count(*) FILTER (WHERE customer_id IS NULL) AS null_keys,
	   count(*) FILTER (WHERE customer_id > 6000) AS no_row,
	   count(*) FILTER (WHERE customer_id IN (SELECT id FROM pj_customers WHERE tier = 3)) AS joined
  FROM pj_orders;
 null_keys | no_row | joined 
-----------+--------+--------
       600 |    626 |   5762
(1 row)

-- ---------- 2. the plan, and what it did ----------
SELECT * FROM pj_explain($$SELECT CASE WHEN c.region IN ('north', 'east') THEN 'ne'
									   WHEN c.tier = 1 THEN 't1' ELSE 'other' END AS k,
								  o.channel, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.active AND c.tier < 4
							GROUP BY 1, 2$$);
                                    pj_explain                                    
----------------------------------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: (c.active AND (c.tier < 4))
         Lion Indexes: pj_customers_active, pj_customers_rt
         Cell Predicates: (c.region = ANY ('{north,east}'::text[])), (c.tier = 1)
         Cell Indexes: pj_customers_rt
         ->  Seq Scan on pj_orders o
(9 rows)

SELECT * FROM pj_explain($$SELECT CASE WHEN c.region IN ('north', 'east') THEN 'ne'
									   WHEN c.tier = 1 THEN 't1' ELSE 'other' END AS k,
								  o.channel, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.active AND c.tier < 4
							GROUP BY 1, 2$$, true);
                                    pj_explain                                    
----------------------------------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: (c.active AND (c.tier < 4))
         Lion Indexes: pj_customers_active, pj_customers_rt
         Cell Predicates: (c.region = ANY ('{north,east}'::text[])), (c.tier = 1)
         Cell Indexes: pj_customers_rt
         Probes: 29400
         Null Keys: 600
         Rows Removed by Lion Cond: 17524
         Heap TIDs Skipped via VM: 11250
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 4, 6651 members in 8 containers
         ->  Seq Scan on pj_orders o
(16 rows)

-- no WHERE on the dimension: the labels alone
SELECT * FROM pj_explain($$SELECT c.mood = 'gone' AS g, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							GROUP BY 1$$, true);
                     pj_explain                      
-----------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Cell Predicates: (c.mood = 'gone'::pj_mood)
         Cell Indexes: pj_customers_mood
         Probes: 29400
         Null Keys: 600
         Heap TIDs Skipped via VM: 28774
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 2, 1498 members in 2 containers
         ->  Seq Scan on pj_orders o
(13 rows)

-- no predicate: the WHERE alone
SELECT * FROM pj_explain($$SELECT o.channel, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.tags && '{3}'
							GROUP BY 1$$, true);
                     pj_explain                      
-----------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: (c.tags && '{3}'::integer[])
         Lion Indexes: pj_customers_tags
         Probes: 29400
         Null Keys: 600
         Rows Removed by Lion Cond: 24149
         Heap TIDs Skipped via VM: 4625
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 1, 966 members in 2 containers
         ->  Seq Scan on pj_orders o
(14 rows)

-- a WHERE lion can only bound: its set inexact, every member's heap tuple
-- fetched and the WHERE rechecked on it
SELECT pj($$SELECT c.tier = 1 AS t1, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.tags <@ '{1,2,7,8}'
			 GROUP BY 1$$);
         pj         
--------------------
 probe join, 2 rows
(1 row)

SELECT * FROM pj_explain($$SELECT c.tier = 1 AS t1, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.tags <@ '{1,2,7,8}'
							GROUP BY 1$$, true);
                          pj_explain                           
---------------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: (c.tags <@ '{1,2,7,8}'::integer[])
         Lion Indexes: pj_customers_tags
         Cell Predicates: (c.tier = 1)
         Cell Indexes: pj_customers_rt
         Probes: 29400
         Null Keys: 600
         Rows Removed by Lion Cond: 990
         Heap TIDs Skipped via VM: 0
         Heap TIDs Rechecked: 27784
         Rows Removed by Visibility: 0
         Rows Removed by Lion Recheck: 24105
         Lion Sets: 2, 6994 members in 4 containers, rechecked
         ->  Seq Scan on pj_orders o
(17 rows)

-- ---------- 3. the anti join ----------
SELECT pj($$SELECT CASE WHEN c.region = 'north' THEN 'n' ELSE 'x' END AS k, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.tier <> 2
			   AND NOT EXISTS (SELECT 1 FROM pj_blocked b WHERE b.customer_id = o.customer_id)
			 GROUP BY 1$$);
         pj         
--------------------
 probe join, 2 rows
(1 row)

-- the inner side's key against the dimension's own, and a filter on it
SELECT pj($$SELECT c.active, o.channel, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE NOT EXISTS (SELECT 1 FROM pj_blocked b
								WHERE b.customer_id = c.id AND b.reason = 'fraud')
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 9 rows
(1 row)

SELECT * FROM pj_explain($$SELECT CASE WHEN c.region = 'north' THEN 'n' ELSE 'x' END AS k, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.tier <> 2
							  AND NOT EXISTS (SELECT 1 FROM pj_blocked b WHERE b.customer_id = o.customer_id)
							GROUP BY 1$$, true);
                       pj_explain                        
---------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: (c.tier <> 2)
         Lion Indexes: pj_customers_rt
         Cell Predicates: (c.region = 'north'::text)
         Cell Indexes: pj_customers_rt
         Anti Join Cond: (b.customer_id = o.customer_id)
         Probes: 29400
         Null Keys: 600
         Rows Removed by Lion Cond: 5819
         Rows Removed by Anti Join: 382
         Heap TIDs Skipped via VM: 22573
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 4, 6591 members in 7 containers
         Anti Join Keys: 120 rows, 96 TIDs
         ->  Seq Scan on pj_orders o
         ->  Seq Scan on pj_blocked b
(20 rows)

-- what the anti join takes away: the blocked customers' orders that pass
SELECT count(*) AS blocked_orders
  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
 WHERE c.tier <> 2 AND o.customer_id IN (SELECT customer_id FROM pj_blocked);
 blocked_orders 
----------------
            382
(1 row)

-- ---------- 4. Params: a generic plan, and a rescan ----------
-- A Param of the WHERE is a run-time key of its lion leaf; a cell predicate
-- takes constants only.
SET plan_cache_mode = force_generic_plan;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;
PREPARE pj_p(int) AS
	SELECT c.region = 'south' AS s, count(*)
	  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
	 WHERE c.tier = $1
	 GROUP BY 1 ORDER BY 1;
SELECT * FROM pj_explain('EXECUTE pj_p(2)');
                        pj_explain                         
-----------------------------------------------------------
 Sort
   ->  HashAggregate
         ->  Custom Scan (LionProbeJoin) on pj_customers c
               Probe Index: pj_customers_pkey
               Probe Cond: (c.id = o.customer_id)
               Lion Cond: (c.tier = $1)
               Lion Indexes: pj_customers_rt
               Cell Predicates: (c.region = 'south'::text)
               Cell Indexes: pj_customers_rt
               ->  Seq Scan on pj_orders o
(10 rows)

EXECUTE pj_p(2);
 s | count 
---+-------
 f |  4205
 t |  1361
   |   253
(3 rows)

EXECUTE pj_p(4);
 s | count 
---+-------
 f |  4028
 t |  1382
   |   244
(3 rows)

DEALLOCATE pj_p;
RESET plan_cache_mode;
RESET enable_hashjoin;
RESET enable_mergejoin;
RESET enable_nestloop;
SET pg_lion.enable_probe_join = off;
SELECT c.region = 'south' AS s, count(*)
  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
 WHERE c.tier = 2 GROUP BY 1 ORDER BY 1;
 s | count 
---+-------
 f |  4205
 t |  1361
   |   253
(3 rows)

SELECT c.region = 'south' AS s, count(*)
  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
 WHERE c.tier = 4 GROUP BY 1 ORDER BY 1;
 s | count 
---+-------
 f |  4028
 t |  1382
   |   244
(3 rows)

RESET pg_lion.enable_probe_join;
-- a correlated subquery: the node rescanned with a new tier each time, its
-- sets built again for each (the builds the counters add up)
SELECT pj($$SELECT t, (SELECT count(*)
						FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
					   WHERE c.tier = t AND c.active
					   GROUP BY c.region = 'east' ORDER BY 1 LIMIT 1) AS n
			  FROM generate_series(1, 5) t$$);
         pj         
--------------------
 probe join, 5 rows
(1 row)

SELECT * FROM pj_explain($$SELECT t, (SELECT count(*)
									   FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
									  WHERE c.tier = t AND c.active
									  GROUP BY c.region = 'east' ORDER BY 1 LIMIT 1) AS n
							 FROM generate_series(1, 5) t$$, true);
                                    pj_explain                                    
----------------------------------------------------------------------------------
 Function Scan on generate_series t
   SubPlan expr_1
     ->  Limit
           ->  Sort
                 ->  HashAggregate
                       ->  Custom Scan (LionProbeJoin) on pj_customers c
                             Probe Index: pj_customers_pkey
                             Probe Cond: (c.id = o.customer_id)
                             Lion Cond: (c.active AND (c.tier = t.t))
                             Lion Indexes: pj_customers_active, pj_customers_rt
                             Cell Predicates: (c.region = 'east'::text)
                             Cell Indexes: pj_customers_rt
                             Probes: 147000
                             Null Keys: 3000
                             Rows Removed by Lion Cond: 125341
                             Heap TIDs Skipped via VM: 18529
                             Heap TIDs Rechecked: 0
                             Rows Removed by Visibility: 0
                             Lion Sets: 3, 2470 members in 6 containers, 5 builds
                             ->  Seq Scan on pj_orders o
(20 rows)

-- ---------- 5. pages not all-visible ----------
-- A HOT update of rows (their note), a non-HOT one (their tier), deletes
-- and inserts, none of them vacuumed, on the first half of the table: the
-- rows on those pages are read from the heap, the versions the snapshot does
-- not see are not counted; the second half's pages are still all-visible.
UPDATE pj_customers SET note = 'hot' WHERE id % 11 = 0 AND id <= 3000;
UPDATE pj_customers SET tier = 1 + tier % 5 WHERE id % 13 = 0 AND id <= 3000;
DELETE FROM pj_customers WHERE id % 17 = 0 AND id <= 3000;
INSERT INTO pj_customers
SELECT i, 'north', 1 + i % 5, true, 'calm', '{1}', 'new'
  FROM generate_series(6001, 6100) i;
INSERT INTO pj_orders
SELECT 30000 + i, 6001 + i % 100, i % 3, i % 100 FROM generate_series(1, 500) i;
SELECT pj($$SELECT CASE WHEN c.region IN ('north', 'east') THEN 'ne'
						WHEN c.tier = 1 THEN 't1' ELSE 'other' END AS k,
				   o.channel, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.active AND c.tier < 4
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 9 rows
(1 row)

SELECT pj($$SELECT c.mood = 'busy' AS b, c.mood IN ('calm', 'gone') AS cg, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.mood IS NOT NULL GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 3 rows
(1 row)

SELECT pj($$SELECT c.tier = 3 AS t3, c.tags && '{1}' AS t1, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE NOT EXISTS (SELECT 1 FROM pj_blocked b WHERE b.customer_id = o.customer_id)
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 6 rows
(1 row)

SELECT * FROM pj_explain($$SELECT c.tier = 3 AS t3, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.active
							GROUP BY 1$$, true, false);
                      pj_explain                       
-------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: c.active
         Lion Indexes: pj_customers_active
         Cell Predicates: (c.tier = 3)
         Cell Indexes: pj_customers_rt
         Probes: > 0
         Null Keys: > 0
         Rows Removed by Lion Cond: > 0
         Heap TIDs Skipped via VM: > 0
         Heap TIDs Rechecked: > 0
         Rows Removed by Visibility: > 0
         Lion Sets: > 0, > 0 members in > 0 containers
         ->  Seq Scan on pj_orders o
(16 rows)

-- the deleted customers' orders are in no group
SELECT * FROM pj_val($$SELECT c.tier = 3 AS t3, count(*)
						 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
						WHERE c.active
						GROUP BY 1$$);
  pj_val   
-----------
 (f,14746)
 (t,3743)
(2 rows)

SET pg_lion.enable_probe_join = off;
SELECT c.tier = 3 AS t3, count(*)
  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
 WHERE c.active
 GROUP BY 1 ORDER BY 1;
 t3 | count 
----+-------
 f  | 14746
 t  |  3743
(2 rows)

RESET pg_lion.enable_probe_join;
-- vacuumed, every page all-visible again: no heap read
VACUUM (FREEZE) pj_customers;
SELECT * FROM pj_explain($$SELECT c.tier = 3 AS t3, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE c.active
							GROUP BY 1$$, true);
                     pj_explain                      
-----------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_customers c
         Probe Index: pj_customers_pkey
         Probe Cond: (c.id = o.customer_id)
         Lion Cond: c.active
         Lion Indexes: pj_customers_active
         Cell Predicates: (c.tier = 3)
         Cell Indexes: pj_customers_rt
         Probes: 29900
         Null Keys: 600
         Rows Removed by Lion Cond: 9947
         Heap TIDs Skipped via VM: 18489
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 2, 5043 members in 4 containers
         ->  Seq Scan on pj_orders o
(16 rows)

-- ---------- 6. chosen on price ----------
-- A dimension whose heap is many times the fact: core hashes the rows of its
-- WHERE, read from the heap by a bitmap scan; the node looks the fact's few
-- thousand keys up in the primary key and reads no heap at all.  Nothing is
-- disabled.
CREATE TABLE pj_accounts (
	id int PRIMARY KEY,
	segment int NOT NULL,	-- lion: ten values
	flag bool NOT NULL,		-- lion
	pad text
) WITH (autovacuum_enabled = off);
INSERT INTO pj_accounts
SELECT i, i % 10, i % 4 = 0, repeat('p', 80) FROM generate_series(1, 80000) i;
CREATE INDEX pj_accounts_segment ON pj_accounts USING lion (segment);
CREATE INDEX pj_accounts_flag ON pj_accounts USING lion (flag);
CREATE TABLE pj_events (id int NOT NULL, account_id int, kind int NOT NULL)
	WITH (autovacuum_enabled = off);
INSERT INTO pj_events
SELECT i, 1 + abs(hashint4(i)) % 80000, i % 3 FROM generate_series(1, 3000) i;
VACUUM (FREEZE, ANALYZE) pj_accounts, pj_events;
EXPLAIN (COSTS OFF)
SELECT a.flag, e.kind, count(*)
  FROM pj_events e JOIN pj_accounts a ON e.account_id = a.id
 WHERE a.segment IN (1, 2, 3, 4, 5, 6)
 GROUP BY 1, 2;
                            QUERY PLAN                             
-------------------------------------------------------------------
 HashAggregate
   Group Key: a.flag, e.kind
   ->  Custom Scan (LionProbeJoin) on pj_accounts a
         Probe Index: pj_accounts_pkey
         Probe Cond: (a.id = e.account_id)
         Lion Cond: (a.segment = ANY ('{1,2,3,4,5,6}'::integer[]))
         Lion Indexes: pj_accounts_segment
         Cell Predicates: a.flag
         Cell Indexes: pj_accounts_flag
         ->  Seq Scan on pj_events e
(10 rows)

SELECT pj($$SELECT a.flag, e.kind, count(*)
			  FROM pj_events e JOIN pj_accounts a ON e.account_id = a.id
			 WHERE a.segment IN (1, 2, 3, 4, 5, 6)
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 6 rows
(1 row)

SELECT * FROM pj_explain($$SELECT a.flag, e.kind, count(*)
							 FROM pj_events e JOIN pj_accounts a ON e.account_id = a.id
							WHERE a.segment IN (1, 2, 3, 4, 5, 6)
							GROUP BY 1, 2$$, true);
                            pj_explain                             
-------------------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_accounts a
         Probe Index: pj_accounts_pkey
         Probe Cond: (a.id = e.account_id)
         Lion Cond: (a.segment = ANY ('{1,2,3,4,5,6}'::integer[]))
         Lion Indexes: pj_accounts_segment
         Cell Predicates: a.flag
         Cell Indexes: pj_accounts_flag
         Probes: 3000
         Null Keys: 0
         Rows Removed by Lion Cond: 1216
         Heap TIDs Skipped via VM: 1784
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 2, 68000 members in 40 containers
         ->  Seq Scan on pj_events e
(16 rows)

DROP TABLE pj_events, pj_accounts;
-- ---------- 7. the parallel form ----------
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SELECT pj($$SELECT CASE WHEN c.region IN ('north', 'east') THEN 'ne'
						WHEN c.tier = 1 THEN 't1' ELSE 'other' END AS k,
				   o.channel, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.active AND c.tier < 4
			 GROUP BY 1, 2$$);
              pj              
------------------------------
 probe join, parallel, 9 rows
(1 row)

SELECT pj($$SELECT c.mood = 'idle' AS idle, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE NOT EXISTS (SELECT 1 FROM pj_blocked b WHERE b.customer_id = o.customer_id)
			 GROUP BY 1$$);
              pj              
------------------------------
 probe join, parallel, 2 rows
(1 row)

SELECT pj($$SELECT count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.region = 'south' AND o.amount < 30$$);
              pj              
------------------------------
 probe join, parallel, 1 rows
(1 row)

SELECT * FROM pj_explain($$SELECT c.mood = 'idle' AS idle, count(*)
							 FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
							WHERE NOT EXISTS (SELECT 1 FROM pj_blocked b WHERE b.customer_id = o.customer_id)
							GROUP BY 1$$);
                             pj_explain                              
---------------------------------------------------------------------
 Finalize HashAggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial HashAggregate
               ->  Custom Scan (LionProbeJoin) on pj_customers c
                     Probe Index: pj_customers_pkey
                     Probe Cond: (c.id = o.customer_id)
                     Cell Predicates: (c.mood = 'idle'::pj_mood)
                     Cell Indexes: pj_customers_mood
                     Anti Join Cond: (b.customer_id = o.customer_id)
                     ->  Parallel Seq Scan on pj_orders o
                     ->  Seq Scan on pj_blocked b
(12 rows)

RESET max_parallel_workers_per_gather;
RESET parallel_setup_cost;
RESET parallel_tuple_cost;
RESET min_parallel_table_scan_size;
SET max_parallel_workers_per_gather = 0;
-- ---------- 8. not offered ----------
-- a dimension column outside a predicate: in the GROUP BY, under a CASE's
-- THEN, in an aggregate
SELECT pj($$SELECT c.region, count(*) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.tier = 2 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 5 rows
(1 row)

SELECT pj($$SELECT CASE WHEN c.active THEN c.tier END AS t, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 6 rows
(1 row)

SELECT pj($$SELECT c.active, max(c.tier) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 3 rows
(1 row)

-- a predicate of a column no lion index has, in the GROUP BY or the WHERE
SELECT pj($$SELECT c.note = 'hot' AS h, count(*) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 2 rows
(1 row)

SELECT pj($$SELECT c.active, count(*) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.note LIKE 'n1%' GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 3 rows
(1 row)

-- a clause lion answers only through a range core derives from it: a LIKE
-- over a column of "C" collation is the range of its prefix, a lossy index
-- clause, and the range's set holds rows the pattern rejects (`k1...` not
-- ending in 7).  The node declines it, alone or beside an exact clause; the
-- same range written out is the clause itself, and the node takes it.
CREATE TABLE pj_codes (
	id int PRIMARY KEY,
	code text COLLATE "C" NOT NULL,	-- lion: 'k1' .. 'k6000'
	kind int NOT NULL				-- lion: 0 to 3
) WITH (autovacuum_enabled = off);
INSERT INTO pj_codes SELECT i, 'k' || i, i % 4 FROM generate_series(1, 6000) i;
CREATE INDEX pj_codes_code ON pj_codes USING lion (code);
CREATE INDEX pj_codes_kind ON pj_codes USING lion (kind);
VACUUM (FREEZE, ANALYZE) pj_codes;
SELECT pj($$SELECT d.kind = 1 AS k1, count(*) FROM pj_orders o JOIN pj_codes d ON o.customer_id = d.id
			 WHERE d.code LIKE 'k1%7' GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 2 rows
(1 row)

SELECT * FROM pj_val($$SELECT d.kind = 1 AS k1, count(*) FROM pj_orders o JOIN pj_codes d ON o.customer_id = d.id
						WHERE d.code LIKE 'k1%7' GROUP BY 1$$);
 pj_val  
---------
 (f,261)
 (t,261)
(2 rows)

SELECT pj($$SELECT d.kind = 1 AS k1, count(*) FROM pj_orders o JOIN pj_codes d ON o.customer_id = d.id
			 WHERE d.kind <> 0 AND d.code LIKE 'k2%' GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 2 rows
(1 row)

SELECT pj($$SELECT d.kind = 1 AS k1, count(*) FROM pj_orders o JOIN pj_codes d ON o.customer_id = d.id
			 WHERE d.code >= 'k1' AND d.code < 'k2' GROUP BY 1$$);
         pj         
--------------------
 probe join, 2 rows
(1 row)

SELECT * FROM pj_val($$SELECT d.kind = 1 AS k1, count(*) FROM pj_orders o JOIN pj_codes d ON o.customer_id = d.id
						WHERE d.code >= 'k1' AND d.code < 'k2' GROUP BY 1$$);
  pj_val  
----------
 (f,3984)
 (t,1340)
(2 rows)

DROP TABLE pj_codes;
-- a predicate no posting set decides: a range whose bound is no constant
SELECT pj($$SELECT c.tier >= CASE WHEN now() IS NULL THEN 9 ELSE 4 END AS high, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 2 rows
(1 row)

-- a predicate of both tables
SELECT pj($$SELECT c.tier = o.channel AS same, count(*)
			  FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 2 rows
(1 row)

-- an outer join, and a join on a column no unique index has
SELECT pj($$SELECT c.active, count(*) FROM pj_orders o LEFT JOIN pj_customers c ON o.customer_id = c.id
			 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 3 rows
(1 row)

SELECT pj($$SELECT c.active, count(*) FROM pj_orders o JOIN pj_customers c ON o.amount = c.tier
			 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 3 rows
(1 row)

-- nothing for a set to answer: core's nested loop is already this
SELECT pj($$SELECT o.channel, count(*) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 3 rows
(1 row)

-- the switch
SET pg_lion.enable_probe_join = off;
SELECT pj($$SELECT c.active, count(*) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 WHERE c.tier = 2 GROUP BY 1$$);
          pj           
-----------------------
 no probe join, 3 rows
(1 row)

RESET pg_lion.enable_probe_join;
-- ---------- 9. ranges ----------
-- A range of a scalar lion column is a cell predicate too (DESIGN.md §46,
-- "Ranges"): its column's outcomes are runs of its order - the gaps between
-- the predicates' constants, and the constants themselves - grouped by the
-- truths they give every predicate, NULL apart; each outcome but the
-- largest is a set of its runs, a range or an equality each.  A NULL row's
-- range is NULL, NaN is above every number, and an empty gap (between 2
-- and 3 of an int) is an empty set.
CREATE TABLE pj_ranged (
	id int PRIMARY KEY,
	score numeric,			-- lion: 0 to 0.999, NaN and NULL
	since date,				-- lion: 1990 to 2022, NULL
	size int NOT NULL,		-- lion: 1 to 7
	code text COLLATE "C",	-- lion: 'k0' .. 'k49', NULL
	grade pj_mood,			-- lion: an enum, NULL
	tags int[],				-- lion
	seg int NOT NULL		-- lion: the WHERE's
) WITH (autovacuum_enabled = off);
INSERT INTO pj_ranged
SELECT i,
	   CASE WHEN i % 41 = 0 THEN NULL WHEN i % 97 = 0 THEN 'NaN'
			ELSE round((abs(hashint4(i)) % 1000) / 1000.0, 3) END,
	   CASE WHEN i % 37 = 0 THEN NULL
			ELSE date '1990-01-01' + abs(hashint4(i + 1)) % 12000 END,
	   1 + abs(hashint4(i + 2)) % 7,
	   CASE WHEN i % 53 = 0 THEN NULL ELSE 'k' || abs(hashint4(i + 3)) % 50 END,
	   CASE WHEN i % 29 = 0 THEN NULL
			ELSE (enum_range(NULL::pj_mood))[1 + i % 4] END,
	   ARRAY[i % 6, 6 + i % 5],
	   i % 5
  FROM generate_series(1, 6000) i;
CREATE INDEX pj_ranged_score ON pj_ranged USING lion (score);
CREATE INDEX pj_ranged_since ON pj_ranged USING lion (since);
CREATE INDEX pj_ranged_size ON pj_ranged USING lion (size);
CREATE INDEX pj_ranged_code ON pj_ranged USING lion (code);
CREATE INDEX pj_ranged_grade ON pj_ranged USING lion (grade);
CREATE INDEX pj_ranged_tags ON pj_ranged USING lion (tags);
CREATE INDEX pj_ranged_seg ON pj_ranged USING lion (seg);
VACUUM (FREEZE, ANALYZE) pj_ranged;
-- one bound, NULL apart, beside a fact column
SELECT pj($$SELECT CASE WHEN d.score > 0.6 THEN 'hi' WHEN d.score IS NULL THEN 'none'
						ELSE 'lo' END AS k, o.channel, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 9 rows
(1 row)

SELECT * FROM pj_val($$SELECT d.score > 0.6 AS hi, count(*)
						 FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
						GROUP BY 1$$);
  pj_val   
-----------
 (,643)
 (f,16579)
 (t,11552)
(3 rows)

-- bounds of every strategy, BETWEEN, a constant on the left
SELECT pj($$SELECT CASE WHEN d.score >= 0.8 THEN 3 WHEN d.score >= 0.5 THEN 2
						WHEN 0.2 < d.score THEN 1 ELSE 0 END AS b,
				   d.score BETWEEN 0.25 AND 0.6 AS mid, d.score <= 0.5 AS le, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 GROUP BY 1, 2, 3$$);
         pj         
--------------------
 probe join, 8 rows
(1 row)

-- ranges beside equalities, IN, <>, NOT IN and IS DISTINCT FROM of the same
-- column, a constant both a bound and a value, empty gaps of an int
SELECT pj($$SELECT d.size >= 3 AS a, d.size = 3 AS b, d.size IN (2, 5) AS c,
				   d.size <> 4 AS e, d.size NOT IN (1, 7) AS g,
				   d.size IS DISTINCT FROM 6 AS h, d.size > 2 AS i, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 GROUP BY 1, 2, 3, 4, 5, 6, 7$$);
         pj         
--------------------
 probe join, 7 rows
(1 row)

-- a date, a text of "C" collation, an enum's order, each with NULLs
SELECT pj($$SELECT d.since >= DATE '2015-01-01' AS recent,
				   d.since < '2000-01-01' AS old, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 4 rows
(1 row)

SELECT pj($$SELECT d.code < 'k3' AS lo, d.code >= 'k45' AS hi, d.code = 'k2' AS k2, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 GROUP BY 1, 2, 3$$);
         pj         
--------------------
 probe join, 5 rows
(1 row)

SELECT pj($$SELECT d.grade > 'busy' AS late, d.grade <= 'calm' AS first, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 4 rows
(1 row)

-- a score of ranges, an overlap and an IN list, rounded, under a gate, in a
-- tier, with a range in the WHERE and the anti join
SELECT pj($$SELECT CASE WHEN (CASE WHEN d.code IS NOT NULL AND d.grade IN ('calm', 'busy')
								   THEN CAST(round(CAST(((0
										+ 5.0 * CASE WHEN d.score > 0.6 THEN 1 ELSE 0 END
										+ 3.0 * CASE WHEN d.tags && ARRAY[1, 2] THEN 1 ELSE 0 END
										+ 2.0 * CASE WHEN d.since >= DATE '2010-01-01' THEN 1 ELSE 0 END
										+ 1.0 * CASE WHEN d.size IN (2, 3) THEN 1 ELSE 0 END) * 100) / 11.0
										AS NUMERIC), 4) AS FLOAT) ELSE NULL END) >= 50 THEN 'high'
						WHEN (CASE WHEN d.code IS NOT NULL AND d.grade IN ('calm', 'busy')
								   THEN CAST(round(CAST(((0
										+ 5.0 * CASE WHEN d.score > 0.6 THEN 1 ELSE 0 END
										+ 3.0 * CASE WHEN d.tags && ARRAY[1, 2] THEN 1 ELSE 0 END
										+ 2.0 * CASE WHEN d.since >= DATE '2010-01-01' THEN 1 ELSE 0 END
										+ 1.0 * CASE WHEN d.size IN (2, 3) THEN 1 ELSE 0 END) * 100) / 11.0
										AS NUMERIC), 4) AS FLOAT) ELSE NULL END) IS NOT NULL THEN 'low'
						ELSE 'none' END AS lbl,
				   o.channel, count(*)
			  FROM pj_ranged d JOIN pj_orders o ON o.customer_id = d.id AND o.amount < 70
			 WHERE d.seg IN (1, 2, 3) AND d.size >= 2
			   AND NOT EXISTS (SELECT 1 FROM pj_blocked b
								WHERE b.customer_id = d.id AND b.reason = 'audit')
			 GROUP BY 1, 2$$);
         pj         
--------------------
 probe join, 9 rows
(1 row)

SELECT * FROM pj_explain($$SELECT CASE WHEN d.score > 0.6 THEN 'hi' WHEN d.score IS NULL THEN 'none'
									   ELSE 'lo' END AS k,
								  d.size BETWEEN 2 AND 4 AS mid, o.channel, count(*)
							 FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
							WHERE d.seg <> 0
							GROUP BY 1, 2, 3$$, true);
                                           pj_explain                                           
------------------------------------------------------------------------------------------------
 HashAggregate
   ->  Custom Scan (LionProbeJoin) on pj_ranged d
         Probe Index: pj_ranged_pkey
         Probe Cond: (d.id = o.customer_id)
         Lion Cond: (d.seg <> 0)
         Lion Indexes: pj_ranged_seg
         Cell Predicates: (d.score > 0.6), (d.score IS NULL), ((d.size >= 2) AND (d.size <= 4))
         Cell Indexes: pj_ranged_score, pj_ranged_size
         Probes: 29900
         Null Keys: 600
         Rows Removed by Lion Cond: 5725
         Heap TIDs Skipped via VM: 23049
         Heap TIDs Rechecked: 0
         Rows Removed by Visibility: 0
         Lion Sets: 4, 9897 members in 8 containers
         ->  Seq Scan on pj_orders o
(16 rows)

-- the parallel form
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SELECT pj($$SELECT CASE WHEN d.score >= 0.8 THEN 3 WHEN d.score >= 0.5 THEN 2
						WHEN d.score > 0.2 THEN 1 ELSE 0 END AS b,
				   d.since >= DATE '2015-01-01' AS recent, count(*)
			  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
			 WHERE NOT EXISTS (SELECT 1 FROM pj_blocked b WHERE b.customer_id = o.customer_id)
			 GROUP BY 1, 2$$);
              pj               
-------------------------------
 probe join, parallel, 12 rows
(1 row)

RESET max_parallel_workers_per_gather;
RESET parallel_setup_cost;
RESET parallel_tuple_cost;
RESET min_parallel_table_scan_size;
SET max_parallel_workers_per_gather = 0;
-- the range of section 8's customers, which a count by cells still declines
SELECT pj($$SELECT c.tier >= 4 AS high, count(*) FROM pj_orders o JOIN pj_customers c ON o.customer_id = c.id
			 GROUP BY 1$$);
         pj         
--------------------
 probe join, 2 rows
(1 row)

-- not offered: a Param's bound, which no outcome can be planned for
SET plan_cache_mode = force_generic_plan;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;
PREPARE pj_r(numeric) AS
	SELECT d.score > $1 AS hi, count(*)
	  FROM pj_orders o JOIN pj_ranged d ON o.customer_id = d.id
	 GROUP BY 1 ORDER BY 1;
SELECT count(*) FILTER (WHERE p LIKE '%LionProbeJoin%') AS probe_joins
  FROM pj_explain('EXECUTE pj_r(0.6)') p;
 probe_joins 
-------------
           0
(1 row)

EXECUTE pj_r(0.6);
 hi | count 
----+-------
 f  | 16579
 t  | 11552
    |   643
(3 rows)

DEALLOCATE pj_r;
RESET plan_cache_mode;
RESET enable_hashjoin;
RESET enable_mergejoin;
RESET enable_nestloop;
DROP TABLE pj_ranged;
DROP TABLE pj_orders, pj_customers, pj_blocked;
DROP TYPE pj_mood;
DROP FUNCTION pj(text), pj_val(text), pj_explain(text, boolean, boolean);