-- Range predicates over the sorted directory (DESIGN.md §28). -- -- `<`, `<=`, `>=`, `>` and BETWEEN on a scalar lion column are answered by a -- bounded walk of the directory: by the bitmap scan, and by the count -- pushdown, where the range bounds the entry walk that drives the count. -- Every answer is checked against a SEQUENTIAL SCAN, as a multiset in both -- directions, so a wrong answer fails even if it happens to be stable. \set VERBOSITY terse SET client_min_messages = warning; LOAD 'pg_lion'; CREATE EXTENSION IF NOT EXISTS pg_lion; CREATE EXTENSION IF NOT EXISTS citext; CREATE EXTENSION IF NOT EXISTS pg_lion_citext; CREATE EXTENSION IF NOT EXISTS pg_buffercache; RESET client_min_messages; -- VACUUM can only set all-visible once the commit record is on disk SET synchronous_commit = on; -- the plan-choice pins below must not depend on ANALYZE's sample SET default_statistics_target = 1000; SET max_parallel_workers_per_gather = 0; /* * lion_rq() runs a query as a BITMAP scan - every other scan disabled and * the pushdown off - and again as a sequential scan, and compares the two. * It reports whether a bitmap index scan was used at all (a collation the * index cannot answer, say, leaves only the sequential scan). */ CREATE FUNCTION lion_rq(q text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE ln text; bitmap boolean := false; nrows bigint; ndiff bigint; BEGIN PERFORM set_config('pg_lion.enable_count_pushdown', 'off', true); PERFORM set_config('enable_seqscan', 'off', true); PERFORM set_config('pg_lion.enable_plain_scan', 'off', true); PERFORM set_config('pg_lion.pushdown_margin', '1000', true); PERFORM set_config('enable_bitmapscan', 'on', true); FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP IF ln LIKE '%Bitmap Index Scan%' THEN bitmap := true; END IF; END LOOP; EXECUTE format('CREATE TEMP TABLE lion_rq_on AS SELECT s::text AS r FROM (%s) s', q); PERFORM set_config('enable_seqscan', 'on', true); PERFORM set_config('enable_bitmapscan', 'off', true); EXECUTE format('CREATE TEMP TABLE lion_rq_off AS SELECT s::text AS r FROM (%s) s', q); PERFORM set_config('enable_bitmapscan', 'on', true); PERFORM set_config('pg_lion.enable_plain_scan', 'on', true); PERFORM set_config('pg_lion.pushdown_margin', '1', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); EXECUTE 'SELECT count(*) FROM lion_rq_on' INTO nrows; EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM lion_rq_on EXCEPT ALL SELECT * FROM lion_rq_off) a)' ' + (SELECT count(*) FROM (SELECT * FROM lion_rq_off EXCEPT ALL SELECT * FROM lion_rq_on) b)' INTO ndiff; EXECUTE 'DROP TABLE lion_rq_on, lion_rq_off'; IF ndiff <> 0 THEN RETURN format('MISMATCH: %s rows differ', ndiff); END IF; RETURN format('%s, %s rows', CASE WHEN bitmap THEN 'bitmap' ELSE 'no bitmap' END, nrows); END $$; /* * lion_rc() runs a query through the count pushdown - with every other scan * disabled when force is set, so that a shape the cost model would not pick * is still EXERCISED - and again as a sequential scan with the pushdown off, * and compares the two. It reports whether the node was used. */ CREATE FUNCTION lion_rc(q text, force boolean DEFAULT true) RETURNS text LANGUAGE plpgsql AS $$ DECLARE ln text; pushed boolean := false; nrows bigint; ndiff bigint; BEGIN IF force THEN PERFORM set_config('enable_seqscan', 'off', true); PERFORM set_config('enable_bitmapscan', 'off', true); PERFORM set_config('pg_lion.enable_plain_scan', 'off', true); -- lion's walk of a btree is lion's plan too, which the margin does -- not mark the count down against PERFORM set_config('pg_lion.enable_btree_scan', 'off', true); PERFORM set_config('pg_lion.pushdown_margin', '1000', true); END IF; PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP IF ln LIKE '%Custom Scan (LionCount)%' THEN pushed := true; END IF; END LOOP; EXECUTE format('CREATE TEMP TABLE lion_rc_on AS SELECT s::text AS r FROM (%s) s', q); PERFORM set_config('pg_lion.enable_count_pushdown', 'off', true); PERFORM set_config('enable_seqscan', '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 lion_rc_off AS SELECT s::text AS r FROM (%s) s', q); PERFORM set_config('enable_bitmapscan', 'on', true); PERFORM set_config('enable_indexscan', 'on', true); PERFORM set_config('pg_lion.enable_plain_scan', 'on', true); PERFORM set_config('pg_lion.enable_btree_scan', 'on', true); PERFORM set_config('pg_lion.pushdown_margin', '1', true); PERFORM set_config('enable_indexonlyscan', 'on', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); EXECUTE 'SELECT count(*) FROM lion_rc_on' INTO nrows; EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM lion_rc_on EXCEPT ALL SELECT * FROM lion_rc_off) a)' ' + (SELECT count(*) FROM (SELECT * FROM lion_rc_off EXCEPT ALL SELECT * FROM lion_rc_on) b)' INTO ndiff; EXECUTE 'DROP TABLE lion_rc_on, lion_rc_off'; IF ndiff <> 0 THEN RETURN format('MISMATCH: %s rows differ', ndiff); END IF; RETURN format('%s, %s rows', CASE WHEN pushed THEN 'pushed down' ELSE 'not pushed down' END, nrows); END $$; /* The same through a generic prepared plan, whose bounds stay Params. */ CREATE FUNCTION lion_rc_prep(q text, args text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE ln text; pushed boolean := false; nrows bigint; ndiff bigint; BEGIN PERFORM set_config('plan_cache_mode', 'force_generic_plan', true); PERFORM set_config('enable_seqscan', 'off', true); PERFORM set_config('enable_bitmapscan', 'off', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); EXECUTE 'PREPARE lion_rcp_on AS ' || q; FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) EXECUTE lion_rcp_on(' || args || ')' LOOP IF ln LIKE '%Custom Scan (LionCount)%' THEN pushed := true; END IF; END LOOP; EXECUTE format('CREATE TEMP TABLE lion_rc_on AS EXECUTE lion_rcp_on(%s)', args); PERFORM set_config('enable_seqscan', 'on', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'off', true); EXECUTE 'PREPARE lion_rcp_off AS ' || q; EXECUTE format('CREATE TEMP TABLE lion_rc_off AS EXECUTE lion_rcp_off(%s)', args); PERFORM set_config('enable_bitmapscan', 'on', true); PERFORM set_config('pg_lion.enable_count_pushdown', 'on', true); EXECUTE 'DEALLOCATE lion_rcp_on'; EXECUTE 'DEALLOCATE lion_rcp_off'; EXECUTE 'SELECT count(*) FROM lion_rc_on' INTO nrows; EXECUTE 'SELECT (SELECT count(*) FROM (SELECT * FROM lion_rc_on EXCEPT ALL SELECT * FROM lion_rc_off) a)' ' + (SELECT count(*) FROM (SELECT * FROM lion_rc_off EXCEPT ALL SELECT * FROM lion_rc_on) b)' INTO ndiff; EXECUTE 'DROP TABLE lion_rc_on, lion_rc_off'; IF ndiff <> 0 THEN RETURN format('MISMATCH: %s rows differ', ndiff); END IF; RETURN format('%s, %s rows', CASE WHEN pushed THEN 'pushed down' ELSE 'not pushed down' END, nrows); END $$; /* Which plan the cost model picks, with nothing disabled: its scan nodes. */ CREATE FUNCTION lion_rpick(q text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE ln text; nodes text := ''; BEGIN FOR ln IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP IF ln ~ '(Custom Scan \(LionCount\)|Index Only Scan|Index Scan|Bitmap Index Scan|Seq Scan)' THEN nodes := nodes || CASE WHEN nodes = '' THEN '' ELSE '; ' END || regexp_replace(regexp_replace(ln, '^[ ->]*', ''), ' using \S+', ''); END IF; END LOOP; RETURN nodes; END $$; -- EXPLAIN in a form every supported release prints the same way (the -- definition test/sql/citext.sql uses). CREATE OR REPLACE FUNCTION lion_explain_norm(q text, opts text DEFAULT 'COSTS OFF') RETURNS SETOF text LANGUAGE plpgsql AS $$ DECLARE l text; BEGIN PERFORM set_config('enable_sort', 'off', true); FOR l IN EXECUTE 'EXPLAIN (' || opts || ') ' || q LOOP CONTINUE WHEN l ~ '^\s*Disabled: true$'; l := regexp_replace(l, '(InitPlan|SubPlan) (\d+)', '\1 expr_\2', 'g'); RETURN NEXT regexp_replace(l, 'rows=(\d+)\.00 ', 'rows=\1 ', 'g'); END LOOP; PERFORM set_config('enable_sort', 'on', true); END $$; -- ---------- the opclasses ---------- -- strategies 6..9, and `<>`'s 10 (DESIGN.md §35), on every ordered scalar -- class, nowhere else SELECT o.amopstrategy, count(DISTINCT c.opcname) AS classes FROM pg_amop o JOIN pg_opclass c ON c.opcfamily = o.amopfamily WHERE o.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'lion') AND c.opcmethod = o.amopmethod GROUP BY 1 ORDER BY 1; amopstrategy | classes --------------+--------- 1 | 31 2 | 1 3 | 1 4 | 1 5 | 1 6 | 29 7 | 29 8 | 29 9 | 29 10 | 29 11 | 1 12 | 1 13 | 1 14 | 1 15 | 1 16 | 1 17 | 1 18 | 1 (18 rows) SELECT c.opcname FROM pg_opclass c WHERE c.opcmethod = (SELECT oid FROM pg_am WHERE amname = 'lion') AND NOT EXISTS (SELECT 1 FROM pg_amop o WHERE o.amopfamily = c.opcfamily AND o.amopstrategy = 6) ORDER BY 1; opcname -------------------- array_ops cid_ops jsonb_contains_ops tsvector_ops xid_ops (5 rows) -- every range operator of a family sits beside a proc 4 for its type pair SELECT count(*) AS range_members, count(*) FILTER (WHERE EXISTS ( SELECT 1 FROM pg_amproc p WHERE p.amprocfamily = o.amopfamily AND p.amprocnum = 4 AND p.amproclefttype = o.amoplefttype AND p.amprocrighttype = o.amoprighttype)) AS with_proc4 FROM pg_amop o WHERE o.amopmethod = (SELECT oid FROM pg_am WHERE amname = 'lion') AND o.amopstrategy BETWEEN 6 AND 9; range_members | with_proc4 ---------------+------------ 168 | 168 (1 row) SELECT amvalidate(c.oid) AS valid, count(*) FROM pg_opclass c WHERE c.opcmethod = (SELECT oid FROM pg_am WHERE amname = 'lion') GROUP BY 1; valid | count -------+------- t | 34 (1 row) -- the validator refuses a range strategy without proc 4, and one in a multi-key class CREATE OPERATOR FAMILY lion_rfam USING lion; CREATE OPERATOR CLASS lion_rcls FOR TYPE int4 USING lion FAMILY lion_rfam AS OPERATOR 1 = (int4, int4), OPERATOR 6 < (int4, int4), FUNCTION 1 hashint4(int4); SELECT amvalidate(oid) FROM pg_opclass WHERE opcname = 'lion_rcls'; INFO: operator family "lion_rfam" of access method lion has range operator(s) for types integer and integer but no support function 4 amvalidate ------------ f (1 row) DROP OPERATOR FAMILY lion_rfam USING lion; -- ---------- the data ---------- CREATE TYPE lion_mood AS ENUM ('sad', 'ok', 'happy', 'ecstatic'); CREATE TABLE lion_r ( id int NOT NULL, k int NOT NULL, -- 0 .. 199 x int NOT NULL, -- 0 .. 9 y int, -- 0 .. 6, NULL every 11th row i2 int2, i4 int4, i8 int8, f4 float4, f8 float8, t text, vc varchar(10), n numeric, d date, ts timestamp, tz timestamptz, u uuid, b bool, e lion_mood, ci citext ); INSERT INTO lion_r SELECT i, i % 200, i % 10, CASE WHEN i % 11 = 0 THEN NULL ELSE i % 7 END, CASE WHEN i % 17 = 0 THEN NULL ELSE (i % 100 - 50)::int2 END, CASE WHEN i % 19 = 0 THEN NULL ELSE i % 100 END, CASE WHEN i % 23 = 0 THEN NULL ELSE (i % 60 - 10)::int8 * 1000 END, CASE WHEN i % 13 = 0 THEN NULL ELSE ('{-Infinity,-1.5,-0,0,0.25,0.5,1,2.5,NaN,Infinity}'::float4[])[i % 10 + 1] END, CASE WHEN i % 13 = 0 THEN NULL ELSE ('{-Infinity,-1.5,-0,0,0.25,0.5,1,2.5,NaN,Infinity}'::float8[])[i % 10 + 1] END, CASE WHEN i % 29 = 0 THEN NULL ELSE chr(97 + i % 26) || (i % 7) END, CASE WHEN i % 29 = 0 THEN NULL ELSE chr(97 + i % 26) || (i % 7) END, CASE WHEN i % 31 = 0 THEN NULL ELSE round((i % 40)::numeric / 8, 3) END, CASE WHEN i % 37 = 0 THEN NULL ELSE date '2024-01-01' + i % 90 END, CASE WHEN i % 37 = 0 THEN NULL ELSE timestamp '2024-01-01' + (i % 48) * interval '1 hour' END, CASE WHEN i % 37 = 0 THEN NULL ELSE timestamptz '2024-01-01 00:00:00+00' + (i % 48) * interval '30 minutes' END, CASE WHEN i % 41 = 0 THEN NULL ELSE md5((i % 50)::text)::uuid END, CASE i % 3 WHEN 0 THEN true WHEN 1 THEN false ELSE NULL END, CASE WHEN i % 43 = 0 THEN NULL ELSE (enum_range(NULL::lion_mood))[i % 4 + 1] END, CASE WHEN i % 47 = 0 THEN NULL WHEN i % 2 = 0 THEN upper(chr(97 + i % 26)) ELSE chr(97 + i % 26) END FROM generate_series(1, 20000) i; CREATE INDEX lion_r_k ON lion_r USING lion (k); CREATE INDEX lion_r_x ON lion_r USING lion (x); CREATE INDEX lion_r_y ON lion_r USING lion (y); CREATE INDEX lion_r_i2 ON lion_r USING lion (i2); CREATE INDEX lion_r_i4 ON lion_r USING lion (i4); CREATE INDEX lion_r_i8 ON lion_r USING lion (i8); CREATE INDEX lion_r_f4 ON lion_r USING lion (f4); CREATE INDEX lion_r_f8 ON lion_r USING lion (f8); CREATE INDEX lion_r_t ON lion_r USING lion (t); CREATE INDEX lion_r_vc ON lion_r USING lion (vc); CREATE INDEX lion_r_n ON lion_r USING lion (n); CREATE INDEX lion_r_d ON lion_r USING lion (d); CREATE INDEX lion_r_ts ON lion_r USING lion (ts); CREATE INDEX lion_r_tz ON lion_r USING lion (tz); CREATE INDEX lion_r_u ON lion_r USING lion (u); CREATE INDEX lion_r_b ON lion_r USING lion (b); CREATE INDEX lion_r_e ON lion_r USING lion (e); CREATE INDEX lion_r_ci ON lion_r USING lion (ci); VACUUM (FREEZE, ANALYZE) lion_r; -- ---------- 1. every strategy, on int4 (the bitmap scan) ---------- SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 10'); lion_rq ------------------- bitmap, 1896 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 <= 10'); lion_rq ------------------- bitmap, 2086 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 90'); lion_rq ------------------- bitmap, 1705 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 >= 90'); lion_rq ------------------- bitmap, 1894 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 BETWEEN 10 AND 20'); lion_rq ------------------- bitmap, 2084 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 >= 10 AND i4 < 20'); lion_rq ------------------- bitmap, 1894 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 10 AND i4 <= 20'); lion_rq ------------------- bitmap, 1894 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE 10 < i4'); -- commuted lion_rq -------------------- bitmap, 16862 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE 20 >= i4 AND 10 <= i4'); lion_rq ------------------- bitmap, 2084 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 5 AND i4 < 5'); -- empty lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 BETWEEN 20 AND 10'); -- inverted lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 >= 7 AND i4 <= 7'); -- equal bounds lion_rq ------------------ bitmap, 190 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 7 AND i4 <= 7'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 5 AND i4 > 50 AND i4 < 60 AND i4 <= 55'); lion_rq ------------------ bitmap, 947 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 >= 60 AND i4 > 50 AND i4 < 70'); lion_rq ------------------- bitmap, 1895 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < -1000'); -- below the keys lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > -1000'); lion_rq -------------------- bitmap, 18948 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 1000000'); -- above them lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 2147483647'); lion_rq -------------------- bitmap, 18948 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > -2147483648'); lion_rq -------------------- bitmap, 18948 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 50 AND i4 = 20'); -- equality answers lion_rq ------------------ bitmap, 190 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 50 AND i4 = 70'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 50 AND i4 IN (3, 60, 49)'); lion_rq ------------------ bitmap, 379 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 50 AND i4 IS NOT NULL'); lion_rq ------------------- bitmap, 9476 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 50 AND i4 IS NULL'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < NULL::int4'); lion_rq ------------------- no bitmap, 0 rows (1 row) -- `< ANY (array)` reaches the index as an array key too: one walk per element SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < ANY (''{3, 50}'')'); lion_rq ------------------- bitmap, 9476 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 >= ANY (ARRAY[90, NULL, 95])'); lion_rq ------------------- bitmap, 1894 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > ANY (''{}''::int[])'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < ANY (''{3, 50}'') AND i4 > 40'); lion_rq ------------------- bitmap, 1706 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 <= ALL (''{3, 50}'')'); lion_rq --------------------- no bitmap, 759 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i8 > ANY (''{2000, 40000}''::int4[])'); lion_rq -------------------- bitmap, 14978 rows (1 row) -- ... which is ONE walk, to the widest element: the largest for < and <=, -- the smallest for >= and >, whatever the order and NULLs of the array SELECT lion_rq('SELECT id FROM lion_r WHERE i4 <= ANY (ARRAY[NULL, 40, 7, 63, 12]::int4[])'); lion_rq -------------------- bitmap, 12128 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > ANY (ARRAY[70, NULL, 55, 90]::int4[])'); lion_rq ------------------- bitmap, 8336 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < ANY (ARRAY[NULL, NULL]::int4[])'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 >= ANY (ARRAY[70, 5000000000, -3]::int8[])'); lion_rq -------------------- bitmap, 18948 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < ANY (ARRAY(SELECT g FROM generate_series(1, 60) g))'); lion_rq -------------------- bitmap, 11370 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t < ANY (''{c, k2, b}''::text[])'); lion_rq ------------------- bitmap, 7643 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t >= ANY (''{x, m, y}''::text[])'); lion_rq -------------------- bitmap, 10396 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 > ANY (''{NaN, 1, -Infinity}''::float8[])'); lion_rq -------------------- bitmap, 16615 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE d < ANY (''{2024-01-05, 2024-02-01, 2024-01-20}''::date[])'); lion_rq ------------------- bitmap, 6716 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE ci > ANY (''{m, B, x}''::citext[])'); lion_rq -------------------- bitmap, 18069 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE e < ANY (''{ok, happy}''::lion_mood[])'); lion_rq ------------------- bitmap, 9768 rows (1 row) SELECT * FROM lion_explain_norm('SELECT id FROM lion_r WHERE i4 BETWEEN 10 AND 20') AS p("QUERY PLAN"); QUERY PLAN ------------------------------------------------- Bitmap Heap Scan on lion_r Recheck Cond: ((i4 >= 10) AND (i4 <= 20)) -> Bitmap Index Scan on lion_r_i4 Index Cond: ((i4 >= 10) AND (i4 <= 20)) (4 rows) -- ---------- 2. cross-type integer bounds: int2, int4, int8 ---------- SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 5000000000'); -- int8 bound, every key below lion_rq -------------------- bitmap, 18948 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > 5000000000'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 > -5000000000 AND i4 < 3::int8'); lion_rq ------------------ bitmap, 570 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 BETWEEN 10::int2 AND 20::int8'); lion_rq ------------------- bitmap, 2084 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i2 < 40000'); -- int4 bound on int2 lion_rq -------------------- bitmap, 18824 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i2 > 40000'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i2 >= -10 AND i2 < 10::int8'); lion_rq ------------------- bitmap, 3764 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i2 < -32768'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i8 > 2::int2 AND i8 <= 30000::int4'); lion_rq ------------------- bitmap, 9566 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i8 < -9223372036854775807'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i8 >= 9223372036854775807'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE 3::int8 > i4'); -- commuted cross-type lion_rq ------------------ bitmap, 570 rows (1 row) -- ---------- 3. floats: -0, NaN, Infinity, float4 against float8 ---------- SELECT lion_rq('SELECT id FROM lion_r WHERE f8 < 0'); lion_rq ------------------- bitmap, 3693 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 <= 0'); lion_rq ------------------- bitmap, 7385 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 >= ''-0'''); lion_rq -------------------- bitmap, 14769 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 > ''-0'''); lion_rq -------------------- bitmap, 11077 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 >= ''NaN'''); lion_rq ------------------- bitmap, 1846 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 < ''NaN'''); lion_rq -------------------- bitmap, 16616 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 > ''Infinity'''); lion_rq ------------------- bitmap, 1846 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 >= ''Infinity'''); lion_rq ------------------- bitmap, 3692 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 <= ''-Infinity'''); lion_rq ------------------- bitmap, 1847 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 BETWEEN ''-Infinity'' AND ''Infinity'''); lion_rq -------------------- bitmap, 16616 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f4 < 0.5::float8'); lion_rq ------------------- bitmap, 9231 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f4 > 0.25::float8 AND f4 <= ''NaN''::float8'); lion_rq ------------------- bitmap, 9231 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f8 >= 0.5::float4'); lion_rq ------------------- bitmap, 9231 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f4 < ''-0''::float4'); lion_rq ------------------- bitmap, 3693 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE f4 BETWEEN ''-0''::float4 AND 0::float8'); lion_rq ------------------- bitmap, 3692 rows (1 row) -- ---------- 4. text and varchar under the default collation, and "C" ---------- SELECT lion_rq('SELECT id FROM lion_r WHERE t < ''k'''); lion_rq ------------------- bitmap, 7430 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t BETWEEN ''b'' AND ''d3'''); lion_rq ------------------- bitmap, 1912 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t >= ''x'''); lion_rq ------------------- bitmap, 2228 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t > ''z9'''); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t < '''''); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE vc < ''k'''); -- varchar through text_ops lion_rq ------------------- bitmap, 7430 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE vc >= ''m'' AND vc < ''n''::text'); lion_rq ------------------ bitmap, 742 rows (1 row) -- a collation the index was not built under: not answered by it SELECT lion_rq('SELECT id FROM lion_r WHERE t < ''k'' COLLATE "C"'); lion_rq ---------------------- no bitmap, 7430 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t < ''k'' COLLATE "POSIX"'); lion_rq ---------------------- no bitmap, 7430 rows (1 row) CREATE INDEX lion_r_tc ON lion_r USING lion (t COLLATE "C"); SELECT lion_rq('SELECT id FROM lion_r WHERE t < ''k'' COLLATE "C"'); lion_rq ------------------- bitmap, 7430 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t COLLATE "C" BETWEEN ''b'' AND ''d'''); lion_rq ------------------- bitmap, 1487 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t < ''k'' COLLATE "POSIX"'); lion_rq ---------------------- no bitmap, 7430 rows (1 row) DROP INDEX lion_r_tc; -- ---------- 5. citext, numeric, dates and times, uuid, bool, enum ---------- SELECT lion_rq('SELECT id FROM lion_r WHERE ci < ''M'''); lion_rq ------------------- bitmap, 9038 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE ci < ''m'''); lion_rq ------------------- bitmap, 9038 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE ci BETWEEN ''C'' AND ''e'''); lion_rq ------------------- bitmap, 2262 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE ci >= ''Z'''); lion_rq ------------------ bitmap, 753 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE n > 1.5'); lion_rq -------------------- bitmap, 13064 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE n BETWEEN 1.0 AND 2.00'); lion_rq ------------------- bitmap, 4355 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE n >= 2 AND n < 2.125'); lion_rq ------------------ bitmap, 484 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE n < 0'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE d >= ''2024-01-10'' AND d < ''2024-02-01'''); lion_rq ------------------- bitmap, 4764 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE d > ''2024-03-01'''); lion_rq ------------------- bitmap, 6264 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE d < ''2023-01-01'''); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE ts BETWEEN ''2024-01-01 05:00'' AND ''2024-01-01 17:30'''); lion_rq ------------------- bitmap, 5275 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE ts > ''2024-01-02 12:00'''); lion_rq ------------------- bitmap, 4452 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE tz >= ''2024-01-01 06:00:00+00'' AND tz < ''2024-01-01 12:00:00+00'''); lion_rq ------------------- bitmap, 4868 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE tz < ''2024-01-01 01:00:00-02'''); lion_rq ------------------- bitmap, 2434 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE u < ''80000000-0000-0000-0000-000000000000'''); lion_rq ------------------- bitmap, 8975 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE u >= ''c0000000-0000-0000-0000-000000000000'''); lion_rq ------------------- bitmap, 6636 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE b < true'); lion_rq ------------------- bitmap, 6667 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE b >= false'); lion_rq -------------------- bitmap, 13333 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE b > true'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE e > ''ok'''); lion_rq ------------------- bitmap, 9767 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE e BETWEEN ''sad'' AND ''happy'''); lion_rq -------------------- bitmap, 14652 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE e < ''sad'''); lion_rq ---------------- bitmap, 0 rows (1 row) -- ---------- 6. the count pushdown: count(*) over a range ---------- SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k BETWEEN 20 AND 40'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k > 180'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k >= 180 AND k <= 180'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k > 100 AND k < 50'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 1000000'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k > 5000000000'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE 30 >= k'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(k), count(*) FROM lion_r WHERE k < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(i4) FROM lion_r WHERE i4 < 20'); -- nullable, but not in range lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE i4 < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE i2 >= -10 AND i2 < 10::int8'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE f8 < 0'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE f8 >= ''NaN'''); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE t BETWEEN ''b'' AND ''d3'''); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE ci < ''M'''); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE n BETWEEN 1.0 AND 2.00'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE d >= ''2024-01-10'' AND d < ''2024-02-01'''); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE tz >= ''2024-01-01 06:00:00+00'''); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE u < ''80000000-0000-0000-0000-000000000000'''); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE b < true'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE e > ''ok'''); lion_rc --------------------- pushed down, 1 rows (1 row) -- ... ANDed with filters on other columns, which stay sources SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND x = 3'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k BETWEEN 10 AND 150 AND x IN (1, 2, 7)'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND y IS NULL'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND y IS NOT NULL'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND k IS NOT NULL'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND (x = 1 OR y = 2)'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND x = 3 AND y = 4 AND i8 = 5000'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND x = 11'); -- a source with no entry lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(y) FROM lion_r WHERE k < 50 AND y IS NOT NULL'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT * FROM lion_explain_norm('SELECT count(*) FROM lion_r WHERE k BETWEEN 20 AND 40 AND x = 3') AS p("QUERY PLAN"); QUERY PLAN ------------------------------------------------------------------ Custom Scan (LionCount) Lion Indexes: lion_r_k (k >= 20 AND k <= 40), lion_r_x (x = 3) (2 rows) -- a GROUP BY the planner folds to one group has no row when it is empty SELECT lion_rc('SELECT x, count(*) FROM lion_r WHERE x = 3 AND k < 50 GROUP BY x'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT x, count(*) FROM lion_r WHERE x = 3 AND k > 1000 GROUP BY x'); lion_rc --------------------- pushed down, 0 rows (1 row) -- ---------- 7. GROUP BY k WHERE , and count(DISTINCT) ---------- SELECT lion_rc('SELECT k, count(*) FROM lion_r WHERE k < 20 GROUP BY k'); lion_rc ---------------------- pushed down, 20 rows (1 row) SELECT lion_rc('SELECT k, count(*) FROM lion_r WHERE k BETWEEN 20 AND 40 AND x = 3 GROUP BY k'); lion_rc --------------------- pushed down, 2 rows (1 row) SELECT lion_rc('SELECT d, count(*) FROM lion_r WHERE d >= ''2024-02-01'' GROUP BY d'); lion_rc ---------------------- pushed down, 59 rows (1 row) SELECT lion_rc('SELECT t, count(*) FROM lion_r WHERE t < ''c'' GROUP BY t'); lion_rc ---------------------- pushed down, 14 rows (1 row) SELECT lion_rc('SELECT i4, count(*), count(i4) FROM lion_r WHERE i4 > 90 GROUP BY i4'); lion_rc --------------------- pushed down, 9 rows (1 row) SELECT lion_rc('SELECT k, count(*) FROM lion_r WHERE k > 100 AND k < 50 GROUP BY k'); lion_rc --------------------- pushed down, 0 rows (1 row) SELECT lion_rc('SELECT k, count(*) FROM lion_r WHERE k < 20 GROUP BY k HAVING count(*) > 99'); lion_rc ---------------------- pushed down, 20 rows (1 row) -- the walk is in key order, so ORDER BY k needs no Sort SELECT * FROM lion_explain_norm('SELECT k, count(*) FROM lion_r WHERE k BETWEEN 20 AND 40 GROUP BY k ORDER BY k') AS p("QUERY PLAN"); QUERY PLAN ------------------------------------------------ Custom Scan (LionCount) Lion Indexes: lion_r_k (k >= 20 AND k <= 40) Group Key: k (3 rows) SELECT k, count(*) FROM lion_r WHERE k BETWEEN 20 AND 25 GROUP BY k ORDER BY k; k | count ----+------- 20 | 100 21 | 100 22 | 100 23 | 100 24 | 100 25 | 100 (6 rows) SELECT lion_rc('SELECT count(DISTINCT k) FROM lion_r WHERE k < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT k) FROM lion_r WHERE k BETWEEN 20 AND 40 AND x = 3'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT k), count(*) FROM lion_r WHERE k < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT i4) FROM lion_r WHERE i4 >= 95'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT x, count(DISTINCT k) FROM lion_r WHERE x < 3 GROUP BY x'); lion_rc --------------------- pushed down, 3 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT k) FROM lion_r WHERE x = 3 AND k > 190 GROUP BY x'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT * FROM lion_explain_norm('SELECT count(DISTINCT k) FROM lion_r WHERE k < 20') AS p("QUERY PLAN"); QUERY PLAN ----------------------------------- Custom Scan (LionCount) Lion Indexes: lion_r_k (k < 20) Distinct Key: k (3 rows) -- ---------- 8. ranges that do not drive: sources since DESIGN.md §32 ---------- SELECT lion_rc('SELECT x, count(*) FROM lion_r WHERE k < 20 GROUP BY x'); -- range not the driver lion_rc ---------------------- pushed down, 10 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT x) FROM lion_r WHERE k < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 20 AND i4 < 20'); -- two range columns lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 20 AND k = 5'); -- beside = on k lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 20 AND k IN (5, 6)'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT x, y, count(*) FROM lion_r WHERE x < 5 GROUP BY x, y'); lion_rc ---------------------- pushed down, 40 rows (1 row) -- declined shapes (the ordinary plan answers them): an OR with nothing that -- holds the §9 pin beside it (§32), and the rest SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 20 OR x = 3'); -- under an OR lion_rc ------------------------- not pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE t < ''k'' COLLATE "C"'); -- collation mismatch lion_rc ------------------------- not pushed down, 1 rows (1 row) SELECT lion_rc('SELECT t, count(*) FROM lion_r WHERE t < ''k'' COLLATE "C" GROUP BY t'); lion_rc -------------------------- not pushed down, 70 rows (1 row) SELECT lion_rc('SELECT n, count(*) FROM lion_r WHERE n > 1 GROUP BY n'); -- numeric is not printable lion_rc -------------------------- not pushed down, 31 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE n > 1'); -- ... but countable lion_rc --------------------- pushed down, 1 rows (1 row) -- an FK-side join's fact filter is a source (DESIGN.md §27), a range too (§32) CREATE TABLE lion_rd (pk int PRIMARY KEY, name text NOT NULL); INSERT INTO lion_rd SELECT i, 'n' || (i % 3) FROM generate_series(0, 9) i; ANALYZE lion_rd; SET enable_hashjoin = off; SET enable_mergejoin = off; SET enable_nestloop = off; SELECT lion_rc('SELECT d.name, count(*) FROM lion_r f JOIN lion_rd d ON f.x = d.pk WHERE f.k < 20 GROUP BY d.name'); lion_rc --------------------- pushed down, 3 rows (1 row) SELECT lion_rc('SELECT d.name, count(*) FROM lion_r f JOIN lion_rd d ON f.x = d.pk WHERE f.y = 2 GROUP BY d.name'); lion_rc --------------------- pushed down, 3 rows (1 row) RESET enable_hashjoin; RESET enable_mergejoin; RESET enable_nestloop; -- ---------- 9. parameters ---------- SELECT lion_rc_prep('SELECT count(*) FROM lion_r WHERE k >= $1 AND k < $2', '20, 40'); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SELECT lion_rc_prep('SELECT count(*) FROM lion_r WHERE k >= $1 AND k < $2', '40, 20'); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SELECT lion_rc_prep('SELECT count(*) FROM lion_r WHERE k >= $1 AND k < $2', 'NULL, 40'); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SELECT lion_rc_prep('SELECT count(*) FROM lion_r WHERE k >= $1 AND k < $2 AND x = $3', '20, 140, 3'); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SELECT lion_rc_prep('SELECT k, count(*) FROM lion_r WHERE k < $1 GROUP BY k', '15'); lion_rc_prep ---------------------- pushed down, 15 rows (1 row) SELECT lion_rc_prep('SELECT k, count(*) FROM lion_r WHERE k < $1 GROUP BY k', 'NULL'); lion_rc_prep --------------------- pushed down, 0 rows (1 row) SELECT lion_rc_prep('SELECT count(DISTINCT k) FROM lion_r WHERE k > $1', '150'); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SELECT lion_rc_prep('SELECT count(*) FROM lion_r WHERE i4 < $1::int8', '5000000000'); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SELECT lion_rc_prep('SELECT count(*) FROM lion_r WHERE d BETWEEN $1 AND $2', '''2024-01-05'', ''2024-01-20'''); lion_rc_prep --------------------- pushed down, 1 rows (1 row) SET plan_cache_mode = force_generic_plan; PREPARE lion_rp_q(int, int) AS SELECT count(*) FROM lion_r WHERE k >= $1 AND k < $2; SELECT * FROM lion_explain_norm('EXECUTE lion_rp_q(20, 40)') AS p("QUERY PLAN"); QUERY PLAN ----------------------------------------------- Custom Scan (LionCount) Lion Indexes: lion_r_k (k >= $1 AND k < $2) (2 rows) EXECUTE lion_rp_q(20, 40); count ------- 2000 (1 row) DEALLOCATE lion_rp_q; RESET plan_cache_mode; -- an exec Param from a nested loop: the node is rescanned per outer row SET enable_hashjoin = off; SET enable_mergejoin = off; SELECT lion_rc('SELECT v.lo, c.n FROM (VALUES (0, 10), (50, 60), (195, 1000), (300, 310), (9, 3)) v(lo, hi), LATERAL (SELECT count(*) AS n FROM lion_r WHERE k >= v.lo AND k < v.hi) c'); lion_rc --------------------- pushed down, 5 rows (1 row) SELECT v.lo, c.n FROM (VALUES (0, 10), (50, 60), (195, 1000), (300, 310), (9, 3)) v(lo, hi), LATERAL (SELECT count(*) AS n FROM lion_r WHERE k >= v.lo AND k < v.hi) c ORDER BY 1; lo | n -----+------ 0 | 1000 9 | 0 50 | 1000 195 | 500 300 | 0 (5 rows) RESET enable_hashjoin; RESET enable_mergejoin; -- ---------- 10. a multicolumn index ---------- CREATE TABLE lion_rm (id int NOT NULL, a int NOT NULL, b int, c text); INSERT INTO lion_rm SELECT i, i % 50, CASE WHEN i % 9 = 0 THEN NULL ELSE i % 13 END, chr(97 + i % 26) FROM generate_series(1, 10000) i; CREATE INDEX lion_rm_abc ON lion_rm USING lion (a, b, c); VACUUM (FREEZE, ANALYZE) lion_rm; SELECT lion_rq('SELECT id FROM lion_rm WHERE a BETWEEN 3 AND 7'); lion_rq ------------------- bitmap, 1000 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE b > 10'); lion_rq ------------------- bitmap, 1368 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE c >= ''x'''); lion_rq ------------------- bitmap, 1152 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a BETWEEN 3 AND 7 AND b = 2'); lion_rq ----------------- bitmap, 68 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < 5 AND b > 7'); lion_rq ------------------ bitmap, 343 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < 5 AND b > 7 AND c < ''m'''); lion_rq ------------------ bitmap, 132 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < 25 AND b IS NULL'); lion_rq ------------------ bitmap, 555 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a > 3 AND a = 5 AND c = ''f'''); lion_rq ----------------- bitmap, 16 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < 25 AND b IN (1, 2) AND c > ''q'''); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE (a < 5 AND b = 1) OR (a > 45 AND c = ''a'')'); lion_rq ----------------- bitmap, 97 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < 0 AND b = 1'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < ANY (''{3, 7}'') AND b = 2'); lion_rq ----------------- bitmap, 98 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a > ANY (''{40, 45}'') AND b IS NOT NULL'); lion_rq ------------------- bitmap, 1600 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a < ANY (''{-40, -45}'') AND b = 3'); lion_rq ---------------- bitmap, 0 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rm WHERE a >= ANY (''{48, NULL}'') AND b = 3 AND c > ''m'''); lion_rq ----------------- bitmap, 14 rows (1 row) -- ... and `< ANY` there is answered by its walk, intersected with the other -- columns' bitmap, not dropped for the recheck: it used to be, and the heap -- threw away every row of b = 3 (685, 586 and 329 rows below), while the cost -- model and the plain scan took it as answered. Nothing is removed now. BEGIN; SET LOCAL enable_seqscan = off; SET LOCAL pg_lion.enable_plain_scan = off; SET LOCAL pg_lion.pushdown_margin = 1000; SET LOCAL pg_lion.enable_count_pushdown = off; SELECT l FROM lion_explain_norm('SELECT id FROM lion_rm WHERE a < ANY (''{-40, -45}'') AND b = 3', 'ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF') AS l WHERE l ~ '(Bitmap Heap Scan|Rows Removed by Index Recheck)'; l ----------------------------------------------------- Bitmap Heap Scan on lion_rm (actual rows=0 loops=1) (1 row) SELECT l FROM lion_explain_norm('SELECT id FROM lion_rm WHERE a < ANY (''{3, 7}'') AND b = 2', 'ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF') AS l WHERE l ~ '(Bitmap Heap Scan|Rows Removed by Index Recheck)'; l ------------------------------------------------------ Bitmap Heap Scan on lion_rm (actual rows=98 loops=1) (1 row) SELECT l FROM lion_explain_norm('SELECT id FROM lion_rm WHERE a >= ANY (''{48, NULL}'') AND b = 3 AND c > ''m''', 'ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF') AS l WHERE l ~ '(Bitmap Heap Scan|Rows Removed by Index Recheck)'; l ------------------------------------------------------ Bitmap Heap Scan on lion_rm (actual rows=14 loops=1) (1 row) ROLLBACK; SELECT lion_rc('SELECT count(*) FROM lion_rm WHERE a < 20'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_rm WHERE a < 20 AND b = 2'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_rm WHERE c BETWEEN ''d'' AND ''k'' AND a = 7'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT a, count(*) FROM lion_rm WHERE a >= 40 AND b IS NOT NULL GROUP BY a'); lion_rc ---------------------- pushed down, 10 rows (1 row) SELECT lion_rc('SELECT b, count(*) FROM lion_rm WHERE b < 5 GROUP BY b'); lion_rc --------------------- pushed down, 5 rows (1 row) SELECT * FROM lion_explain_norm('SELECT count(*) FROM lion_rm WHERE a < 20 AND b = 2') AS p("QUERY PLAN"); QUERY PLAN --------------------------------------------------------------- Custom Scan (LionCount) Lion Indexes: lion_rm_abc.a (a < 20), lion_rm_abc.b (b = 2) (2 rows) -- ---------- 11. a partial index ---------- CREATE TABLE lion_rpi (id int NOT NULL, a int NOT NULL, flag bool NOT NULL); INSERT INTO lion_rpi SELECT i, i % 100, i % 4 = 0 FROM generate_series(1, 8000) i; CREATE INDEX lion_rpi_a ON lion_rpi USING lion (a) WHERE flag; VACUUM ANALYZE lion_rpi; SELECT lion_rq('SELECT id FROM lion_rpi WHERE a < 30 AND flag'); lion_rq ------------------ bitmap, 640 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rpi WHERE a BETWEEN 30 AND 70 AND flag'); lion_rq ------------------ bitmap, 800 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rpi WHERE a < 30'); -- predicate not implied lion_rq ---------------------- no bitmap, 2400 rows (1 row) -- ---------- 11b. an UNORDERED column: every entry is tested with the operator ---------- -- A proc 4 no btree family sorts with leaves the directory in hash order -- (DESIGN.md §21, rule 3), so the walk cannot be bounded; it tests each -- entry of the column with the range's operator instead (§28). CREATE FUNCTION lion_rcmp(int4, int4) RETURNS int4 LANGUAGE sql IMMUTABLE STRICT AS 'SELECT btint4cmp($1, $2)'; CREATE OPERATOR FAMILY lion_runord USING lion; CREATE OPERATOR CLASS lion_runord_ops FOR TYPE int4 USING lion FAMILY lion_runord AS OPERATOR 1 = (int4, int4), OPERATOR 6 < (int4, int4), OPERATOR 7 <= (int4, int4), OPERATOR 8 >= (int4, int4), OPERATOR 9 > (int4, int4), FUNCTION 1 hashint4(int4), FUNCTION 4 lion_rcmp(int4, int4); CREATE TABLE lion_ru (id int NOT NULL, a int NOT NULL); INSERT INTO lion_ru SELECT i, i % 97 FROM generate_series(1, 5000) i; CREATE INDEX lion_ru_a ON lion_ru USING lion (a lion_runord_ops); VACUUM ANALYZE lion_ru; SELECT ordered FROM lion_index_stats('lion_ru_a'); ordered --------- f (1 row) SELECT lion_rq('SELECT id FROM lion_ru WHERE a BETWEEN 10 AND 20'); lion_rq ------------------ bitmap, 572 rows (1 row) SELECT lion_rq('SELECT id FROM lion_ru WHERE a > 90'); lion_rq ------------------ bitmap, 306 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_ru WHERE a < 30'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT a, count(*) FROM lion_ru WHERE a >= 50 AND a < 60 GROUP BY a'); lion_rc ---------------------- pushed down, 10 rows (1 row) DROP TABLE lion_ru; DROP OPERATOR FAMILY lion_runord USING lion; DROP FUNCTION lion_rcmp(int4, int4); -- ---------- 12. a partitioned table ---------- CREATE TABLE lion_rpt (id int NOT NULL, k int NOT NULL, x int NOT NULL) PARTITION BY RANGE (id); CREATE TABLE lion_rpt_1 PARTITION OF lion_rpt FOR VALUES FROM (0) TO (5000); CREATE TABLE lion_rpt_2 (x int NOT NULL, k int NOT NULL, id int NOT NULL); -- other numbering ALTER TABLE lion_rpt ATTACH PARTITION lion_rpt_2 FOR VALUES FROM (5000) TO (20000); INSERT INTO lion_rpt SELECT i, i % 100, i % 7 FROM generate_series(1, 12000) i; CREATE INDEX lion_rpt_k ON lion_rpt USING lion (k); CREATE INDEX lion_rpt_x ON lion_rpt USING lion (x); VACUUM (FREEZE, ANALYZE) lion_rpt; SELECT lion_rc('SELECT count(*) FROM lion_rpt WHERE k < 30'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_rpt WHERE k BETWEEN 30 AND 60 AND x = 2'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT k, count(*) FROM lion_rpt WHERE k > 90 GROUP BY k'); lion_rc --------------------- pushed down, 9 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT k) FROM lion_rpt WHERE k < 30'); -- declined (§26) lion_rc ------------------------- not pushed down, 1 rows (1 row) SELECT lion_rq('SELECT id FROM lion_rpt WHERE k BETWEEN 30 AND 33'); lion_rq ------------------ bitmap, 480 rows (1 row) SELECT * FROM lion_explain_norm('SELECT k, count(*) FROM lion_rpt WHERE k > 90 GROUP BY k') AS p("QUERY PLAN"); QUERY PLAN -------------------------------------------- Finalize HashAggregate Group Key: lion_rpt.k -> Custom Scan (LionCount) Partitions: lion_rpt_1, lion_rpt_2 Lion Indexes: (k > 90) Group Key: k (6 rows) -- ---------- 13. a dirty heap, then a clean one ---------- DELETE FROM lion_r WHERE id % 5 = 0; UPDATE lion_r SET k = (k + 7) % 200, i4 = i4 + 1, d = d + 3, t = t || 'u' WHERE id % 7 = 0; INSERT INTO lion_r (id, k, x, i4, d, t) SELECT 20000 + i, i % 13, i % 10, i % 100 - 5, date '2024-02-15', 'm' || i % 5 FROM generate_series(1, 500) i; SELECT lion_rq('SELECT id FROM lion_r WHERE k BETWEEN 20 AND 40'); lion_rq ------------------- bitmap, 1615 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 10'); lion_rq ------------------- bitmap, 1564 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE d >= ''2024-02-10'' AND d < ''2024-03-01'''); lion_rq ------------------- bitmap, 3956 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t > ''m'''); lion_rq ------------------- bitmap, 8816 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k BETWEEN 20 AND 40'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND x = 3'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT k, count(*) FROM lion_r WHERE k < 20 GROUP BY k'); lion_rc ---------------------- pushed down, 20 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT k) FROM lion_r WHERE k < 20 AND x = 2'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE i4 < 10'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT d, count(*) FROM lion_r WHERE d >= ''2024-02-10'' GROUP BY d'); lion_rc ---------------------- pushed down, 53 rows (1 row) VACUUM (FREEZE) lion_r; SELECT lion_rq('SELECT id FROM lion_r WHERE k BETWEEN 20 AND 40'); lion_rq ------------------- bitmap, 1615 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE i4 < 10'); lion_rq ------------------- bitmap, 1564 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE d >= ''2024-02-10'' AND d < ''2024-03-01'''); lion_rq ------------------- bitmap, 3956 rows (1 row) SELECT lion_rq('SELECT id FROM lion_r WHERE t > ''m'''); lion_rq ------------------- bitmap, 8816 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k BETWEEN 20 AND 40'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE k < 50 AND x = 3'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT k, count(*) FROM lion_r WHERE k < 20 GROUP BY k'); lion_rc ---------------------- pushed down, 20 rows (1 row) SELECT lion_rc('SELECT count(DISTINCT k) FROM lion_r WHERE k < 20 AND x = 2'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_r WHERE i4 < 10'); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT d, count(*) FROM lion_r WHERE d >= ''2024-02-10'' GROUP BY d'); lion_rc ---------------------- pushed down, 53 rows (1 row) -- a clean heap is counted from the visibility map SELECT l AS line FROM lion_explain_norm('SELECT count(*) FROM lion_r WHERE k BETWEEN 20 AND 40', 'ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF') AS l WHERE l ~ 'Rechecked|Summed'; line ---------------------------- Heap TIDs Rechecked: 0 Heap Blocks Rechecked: 0 Posting Sets Summed: 21 (3 rows) SELECT lion_index_verify('lion_r_k', true); lion_index_verify ------------------- (1 row) SELECT lion_index_verify('lion_r_d', true); lion_index_verify ------------------- (1 row) -- ---------- 14. pins: a GROUP BY parked mid-walk holds one entry's ---------- CREATE FUNCTION lion_rpinned(idx regclass) RETURNS bigint LANGUAGE sql AS $$ SELECT count(*) FROM pg_buffercache WHERE reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database()) AND relfilenode = pg_relation_filenode(idx) AND pinning_backends > 0 $$; BEGIN; SET LOCAL enable_seqscan = off; SET LOCAL enable_bitmapscan = off; DECLARE lion_rcur CURSOR FOR SELECT k, count(*) FROM lion_r WHERE k BETWEEN 0 AND 199 GROUP BY k; FETCH 3 FROM lion_rcur; k | count ---+------- 0 | 52 1 | 139 2 | 125 (3 rows) SELECT lion_rpinned('lion_r_k') <= 1 AS one_entry_pinned; one_entry_pinned ------------------ t (1 row) FETCH 100 FROM lion_rcur \g /dev/null SELECT lion_rpinned('lion_r_k') <= 1 AS one_entry_pinned; one_entry_pinned ------------------ t (1 row) COMMIT; -- ---------- 15. plan choice, nothing disabled ---------- -- lo: 200 values; uq: a timestamp that is unique and in heap order CREATE TABLE lion_rp (id int NOT NULL, lo int NOT NULL, uq timestamp NOT NULL, pad text); INSERT INTO lion_rp SELECT i, (i * 7919) % 200, timestamp '2024-01-01' + i * interval '1 second', repeat('x', 40) FROM generate_series(1, 100000) i; CREATE INDEX lion_rp_lo_lion ON lion_rp USING lion (lo); CREATE INDEX lion_rp_lo_bt ON lion_rp (lo); CREATE INDEX lion_rp_uq_lion ON lion_rp USING lion (uq); NOTICE: lion index "lion_rp_uq_lion": column "uq" has about one row per key CREATE INDEX lion_rp_uq_bt ON lion_rp (uq); VACUUM (FREEZE, ANALYZE) lion_rp; SELECT lion_rpick('SELECT count(*) FROM lion_rp WHERE lo BETWEEN 10 AND 30'); lion_rpick ------------------------- Custom Scan (LionCount) (1 row) SELECT lion_rpick('SELECT count(*) FROM lion_rp WHERE lo < 100'); lion_rpick ------------------------- Custom Scan (LionCount) (1 row) SELECT lion_rpick('SELECT sum(length(pad)) FROM lion_rp WHERE lo BETWEEN 10 AND 11'); lion_rpick -------------------------------------- Bitmap Index Scan on lion_rp_lo_lion (1 row) SELECT lion_rpick('SELECT count(*) FROM lion_rp WHERE uq BETWEEN ''2024-01-01 01:00'' AND ''2024-01-01 02:00'''); lion_rpick ---------------------------- Index Only Scan on lion_rp (1 row) SELECT lion_rpick('SELECT sum(length(pad)) FROM lion_rp WHERE uq BETWEEN ''2024-01-01 01:00'' AND ''2024-01-01 02:00'''); lion_rpick ----------------------- Index Scan on lion_rp (1 row) SELECT lion_rpick('SELECT count(*) FROM lion_rp WHERE uq > ''2024-01-01 12:00'''); lion_rpick ---------------------------- Index Only Scan on lion_rp (1 row) SELECT lion_rpick('SELECT lo, count(*) FROM lion_rp WHERE lo BETWEEN 10 AND 30 GROUP BY lo'); lion_rpick ------------------------- Custom Scan (LionCount) (1 row) SELECT lion_rpick('SELECT uq, count(*) FROM lion_rp WHERE uq BETWEEN ''2024-01-01 01:00'' AND ''2024-01-01 02:00'' GROUP BY uq'); lion_rpick ---------------------------- Index Only Scan on lion_rp (1 row) SELECT lion_rc('SELECT count(*) FROM lion_rp WHERE lo BETWEEN 10 AND 30', false); lion_rc --------------------- pushed down, 1 rows (1 row) SELECT lion_rc('SELECT count(*) FROM lion_rp WHERE uq BETWEEN ''2024-01-01 01:00'' AND ''2024-01-01 02:00'''); lion_rc --------------------- pushed down, 1 rows (1 row) -- `< ANY` over a unique column: the bitmap index scan emits each matching row -- once, not once per element (200 near-equal bounds used to emit 200 times) BEGIN; DROP INDEX lion_rp_uq_bt; SET LOCAL enable_seqscan = off; SET LOCAL pg_lion.enable_plain_scan = off; SET LOCAL pg_lion.pushdown_margin = 1000; SELECT regexp_replace(l, 'rows=(\d+)(\.00)? ', 'rows=\1 ') AS line FROM lion_explain_norm('SELECT id FROM lion_rp WHERE uq <= ANY (ARRAY(SELECT timestamp ''2024-01-01 01:00'' + g * interval ''1 second'' FROM generate_series(1, 200) g))', 'ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF') AS l WHERE l ~ 'Bitmap Index Scan'; line ----------------------------------------------------------------------- -> Bitmap Index Scan on lion_rp_uq_lion (actual rows=3800 loops=1) (1 row) SELECT lion_rq('SELECT id FROM lion_rp WHERE uq <= ANY (ARRAY(SELECT timestamp ''2024-01-01 01:00'' + g * interval ''1 second'' FROM generate_series(1, 200) g))'); lion_rq ------------------- bitmap, 3800 rows (1 row) ROLLBACK; -- every page dirty, and lo's rows scattered over all of them: each entry of -- the walk rechecks pages the others recheck too, which the model charges -- (DESIGN.md §28), so the bitmap scan answers the count UPDATE lion_rp SET pad = 'y' || pad WHERE id % 50 = 7; ANALYZE lion_rp; SELECT lion_rpick('SELECT count(*) FROM lion_rp WHERE lo BETWEEN 10 AND 30'); lion_rpick -------------------------------------- Bitmap Index Scan on lion_rp_lo_lion (1 row) SELECT lion_rc('SELECT count(*) FROM lion_rp WHERE lo BETWEEN 10 AND 30'); lion_rc --------------------- pushed down, 1 rows (1 row) DROP FUNCTION lion_rpinned(regclass); DROP TABLE lion_r, lion_rd, lion_rm, lion_rpi, lion_rpt, lion_rp; DROP TYPE lion_mood; DROP FUNCTION lion_rq(text); DROP FUNCTION lion_rc(text, boolean); DROP FUNCTION lion_rc_prep(text, text); DROP FUNCTION lion_rpick(text);