-- lion_index_count(): heap-skipping count(*) (DESIGN.md section 9). -- -- Every assertion here is "the function agrees with the equivalent SELECT -- under the same snapshot". The counting must therefore never be allowed to -- answer the reference query as well, so the phase-2b pushdown is switched -- off for this file. LOAD makes the GUC exist before we set it: the library -- is otherwise only loaded when the planner first opens a lion index. \set VERBOSITY terse SET client_min_messages = warning; LOAD 'pg_lion'; SET pg_lion.enable_count_pushdown = off; /* * The visibility map is what makes this feature worth having, and a heap page * only becomes all-visible once the inserting transaction's commit record has * reached disk. The dev cluster runs with synchronous_commit = off, so ask * for flushed commits here; otherwise whether VACUUM can set the bits depends * on how fast the WAL writer happens to be. */ SET synchronous_commit = on; CREATE EXTENSION IF NOT EXISTS pg_lion; /* * lion_ccmp() compares lion_index_count() against count(*) for one key. * 'val' is a SQL expression, so cross-type calls can be written out in full. */ CREATE OR REPLACE FUNCTION lion_ccmp(idx text, tbl text, col text, val text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE a bigint; b bigint; BEGIN EXECUTE format('SELECT lion_index_count(%L::regclass, %s)', idx, val) INTO a; EXECUTE format('SELECT count(*) FROM %s WHERE %I = %s', tbl, col, val) INTO b; IF a IS DISTINCT FROM b THEN RETURN format('MISMATCH roaring=%s select=%s', a, b); END IF; RETURN format('ok %s', a); END $$; CREATE OR REPLACE FUNCTION lion_ccmp2(idx1 text, val1 text, idx2 text, val2 text, tbl text, col1 text, col2 text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE a bigint; b bigint; BEGIN EXECUTE format('SELECT lion_index_count(%L::regclass, %s, %L::regclass, %s)', idx1, val1, idx2, val2) INTO a; EXECUTE format('SELECT count(*) FROM %s WHERE %I = %s AND %I = %s', tbl, col1, val1, col2, val2) INTO b; IF a IS DISTINCT FROM b THEN RETURN format('MISMATCH roaring=%s select=%s', a, b); END IF; RETURN format('ok %s', a); END $$; /* * 200k rows over ~1100 heap pages, i.e. about 18 containers per full-table * posting set. c10 (20000 rows per key) spills to a chain of container * pages; c5000 (40 rows per key) stays inline in its entry tuple; both paths * of lion_count.c are therefore exercised by the queries below. */ CREATE TABLE lion_cnt AS SELECT i, (i % 10)::int4 AS c10, ((i::bigint * 7919) % 5000)::int4 AS c5000, ((i::bigint * 104729) % 100000)::int8 AS c100k, ('key ' || ((i::bigint * 31) % 1000))::text AS t1000, (i % 7)::int4 AS m7, (i % 3 = 0) AS b3 FROM generate_series(1, 200000) i; CREATE INDEX lion_cnt_c10 ON lion_cnt USING lion (c10); CREATE INDEX lion_cnt_c5000 ON lion_cnt USING lion (c5000); CREATE INDEX lion_cnt_c100k ON lion_cnt USING lion (c100k); CREATE INDEX lion_cnt_t1000 ON lion_cnt USING lion (t1000); CREATE INDEX lion_cnt_m7 ON lion_cnt USING lion (m7); CREATE INDEX lion_cnt_b3 ON lion_cnt USING lion (b3); ANALYZE lion_cnt; -- chain entries (20000 TIDs per key) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '0'); lion_ccmp ----------- ok 20000 (1 row) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '7'); lion_ccmp ----------- ok 20000 (1 row) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '9'); lion_ccmp ----------- ok 20000 (1 row) -- the widest posting sets of all: two thirds / one third of the table SELECT lion_ccmp('lion_cnt_b3', 'lion_cnt', 'b3', 'true'); lion_ccmp ----------- ok 66666 (1 row) SELECT lion_ccmp('lion_cnt_b3', 'lion_cnt', 'b3', 'false'); lion_ccmp ----------- ok 133334 (1 row) -- inline entries (40 TIDs per key) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '0'); lion_ccmp ----------- ok 40 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '1234'); lion_ccmp ----------- ok 40 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '4999'); lion_ccmp ----------- ok 40 (1 row) /* * Two TIDs per key: c100k's posting sets are sparse segments, not containers * (DESIGN.md section 13), so the set cursor has to present each of their * container keys as a temporary container of its own. */ SELECT containers, sparse_segments > 0 AS has_segments, sparse_members = ntids AS every_tid_in_a_segment FROM lion_index_stats('lion_cnt_c100k'); containers | has_segments | every_tid_in_a_segment ------------+--------------+------------------------ 0 | t | t (1 row) SELECT lion_ccmp('lion_cnt_c100k', 'lion_cnt', 'c100k', '29'); lion_ccmp ----------- ok 2 (1 row) SELECT lion_ccmp('lion_cnt_c100k', 'lion_cnt', 'c100k', '99729'); lion_ccmp ----------- ok 2 (1 row) -- by-reference keys SELECT lion_ccmp('lion_cnt_t1000', 'lion_cnt', 't1000', '''key 0''::text'); lion_ccmp ----------- ok 200 (1 row) SELECT lion_ccmp('lion_cnt_t1000', 'lion_cnt', 't1000', '''key 777''::text'); lion_ccmp ----------- ok 200 (1 row) -- keys that match nothing at all SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '-1'); lion_ccmp ----------- ok 0 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '1000000'); lion_ccmp ----------- ok 0 (1 row) SELECT lion_ccmp('lion_cnt_t1000', 'lion_cnt', 't1000', '''no such key''::text'); lion_ccmp ----------- ok 0 (1 row) -- cross-type keys resolved through the shared integer operator family SELECT lion_ccmp('lion_cnt_c100k', 'lion_cnt', 'c100k', '29::int4'); lion_ccmp ----------- ok 2 (1 row) SELECT lion_ccmp('lion_cnt_c100k', 'lion_cnt', 'c100k', '29::int2'); lion_ccmp ----------- ok 2 (1 row) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '7::int8'); lion_ccmp ----------- ok 20000 (1 row) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '7::int2'); lion_ccmp ----------- ok 20000 (1 row) /* * Two keys at once: the merge of two posting sets. Each key is resolved * against its own index (key2 is anycompatible, not key1's anyelement, so the * two need not share a type - test/sql/keytypes.sql counts an enum with a * bool); the index each one is looked up in may of course be on a different * (cross-type compatible) type, as the c100k case below shows. */ SELECT lion_ccmp2('lion_cnt_c10', '3', 'lion_cnt_m7', '2', 'lion_cnt', 'c10', 'm7'); lion_ccmp2 ------------ ok 2857 (1 row) SELECT lion_ccmp2('lion_cnt_m7', '0', 'lion_cnt_c10', '9', 'lion_cnt', 'm7', 'c10'); lion_ccmp2 ------------ ok 2857 (1 row) -- chain entry AND inline entry (every c5000 = 1234 row has c10 = 6) SELECT lion_ccmp2('lion_cnt_c10', '6', 'lion_cnt_c5000', '1234', 'lion_cnt', 'c10', 'c5000'); lion_ccmp2 ------------ ok 40 (1 row) SELECT lion_ccmp2('lion_cnt_c10', '3', 'lion_cnt_c5000', '1234', 'lion_cnt', 'c10', 'c5000'); lion_ccmp2 ------------ ok 0 (1 row) -- an int8 index probed with an int4 constant, ANDed with an int4 index SELECT lion_ccmp2('lion_cnt_c10', '1', 'lion_cnt_c100k', '29', 'lion_cnt', 'c10', 'c100k'); lion_ccmp2 ------------ ok 2 (1 row) SELECT lion_ccmp2('lion_cnt_c10', '9', 'lion_cnt_c100k', '29', 'lion_cnt', 'c10', 'c100k'); lion_ccmp2 ------------ ok 0 (1 row) -- an intersection that is empty although both keys exist SELECT lion_ccmp2('lion_cnt_c10', '1', 'lion_cnt_c5000', '0', 'lion_cnt', 'c10', 'c5000'); lion_ccmp2 ------------ ok 0 (1 row) -- one key that does not exist at all SELECT lion_ccmp2('lion_cnt_c10', '1', 'lion_cnt_c5000', '1000000', 'lion_cnt', 'c10', 'c5000'); lion_ccmp2 ------------ ok 0 (1 row) -- An empty table, and a table whose only key has one row. CREATE TABLE lion_cnt_tiny (k int4); CREATE INDEX lion_cnt_tiny_k ON lion_cnt_tiny USING lion (k); SELECT lion_ccmp('lion_cnt_tiny_k', 'lion_cnt_tiny', 'k', '1'); lion_ccmp ----------- ok 0 (1 row) DROP INDEX lion_cnt_tiny_k; INSERT INTO lion_cnt_tiny VALUES (1); CREATE INDEX lion_cnt_tiny_k ON lion_cnt_tiny USING lion (k); SELECT lion_ccmp('lion_cnt_tiny_k', 'lion_cnt_tiny', 'k', '1'); lion_ccmp ----------- ok 1 (1 row) SELECT lion_ccmp('lion_cnt_tiny_k', 'lion_cnt_tiny', 'k', '2'); lion_ccmp ----------- ok 0 (1 row) /* * The visibility map interlock. How much of a freshly loaded heap is * all-visible before the first VACUUM is NOT deterministic: on-access pruning * (heap_page_prune_opt(), which sets visibility map bits as well as pruning) * runs during every sequential scan, so it depends on what has read the table * and on the oldest snapshot in the cluster while it did -- running this file * on its own and running it after another test file give different answers. * What must hold either way is that the count is right and that the two * numbers agree with each other: a posting set with no dead TIDs in it has * every member either on a block the map answered or in the recheck list, so * "nothing was skipped" and "everything was rechecked" are the same * statement. The states where the numbers themselves are pinned down are * asserted below, after VACUUM and after the DELETE. */ SELECT count = 20000 AS count_ok, tids_rechecked <= 20000 AS no_tid_rechecked_twice, (blocks_skipped = 0) = (tids_rechecked = 20000) AS map_and_recheck_agree FROM lion_index_count_stats('lion_cnt_c10', 0); count_ok | no_tid_rechecked_twice | map_and_recheck_agree ----------+------------------------+----------------------- t | t | t (1 row) VACUUM (FREEZE) lion_cnt; SELECT count = 20000 AS count_ok, blocks_skipped > 0 AS skipped_some, tids_rechecked = 0 AS nothing_rechecked FROM lion_index_count_stats('lion_cnt_c10', 0); count_ok | skipped_some | nothing_rechecked ----------+--------------+------------------- t | t | t (1 row) /* * After a DELETE the affected heap pages lose their all-visible bit, so those * blocks come back through the recheck path and the deleted rows are not * counted -- while the index still lists them, because VACUUM has not run. */ DELETE FROM lion_cnt WHERE i % 4 = 0; SELECT count = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS count_ok, tids_rechecked > 0 AS rechecked_some FROM lion_index_count_stats('lion_cnt_c10', 0); count_ok | rechecked_some ----------+---------------- t | t (1 row) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '0'); lion_ccmp ----------- ok 10000 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '1234'); lion_ccmp ----------- ok 40 (1 row) SELECT lion_ccmp('lion_cnt_t1000', 'lion_cnt', 't1000', '''key 777''::text'); lion_ccmp ----------- ok 200 (1 row) SELECT lion_ccmp2('lion_cnt_c10', '3', 'lion_cnt_m7', '2', 'lion_cnt', 'c10', 'm7'); lion_ccmp2 ------------ ok 2857 (1 row) -- ... and after VACUUM the dead TIDs are gone and the map answers again. VACUUM (FREEZE) lion_cnt; SELECT count = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS count_ok, blocks_skipped > 0 AS skipped_some, tids_rechecked = 0 AS nothing_rechecked FROM lion_index_count_stats('lion_cnt_c10', 0); count_ok | skipped_some | nothing_rechecked ----------+--------------+------------------- t | t | t (1 row) SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '0'); lion_ccmp ----------- ok 10000 (1 row) SELECT lion_ccmp('lion_cnt_b3', 'lion_cnt', 'b3', 'true'); lion_ccmp ----------- ok 50000 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '1234'); lion_ccmp ----------- ok 40 (1 row) /* * Snapshot behaviour. A cross-session test needs isolationtester (see * test/isolation/count_snapshot.spec); what a single session can show is that * the count uses the *active* snapshot, so inside a REPEATABLE READ * transaction it tracks the transaction's own changes and forgets them again * when they are rolled back. */ BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT lion_index_count('lion_cnt_c10', 0) = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS agrees; agrees -------- t (1 row) DELETE FROM lion_cnt WHERE c10 = 0 AND i % 30 = 10; SELECT lion_index_count('lion_cnt_c10', 0) = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS agrees_after_own_delete; agrees_after_own_delete ------------------------- t (1 row) SAVEPOINT s; DELETE FROM lion_cnt WHERE c10 = 0 AND i % 30 = 20; SELECT lion_index_count('lion_cnt_c10', 0) = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS agrees_in_subxact; agrees_in_subxact ------------------- t (1 row) ROLLBACK TO s; SELECT lion_index_count('lion_cnt_c10', 0) = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS agrees_after_rollback_to; agrees_after_rollback_to -------------------------- t (1 row) ROLLBACK; SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '0'); lion_ccmp ----------- ok 10000 (1 row) BEGIN ISOLATION LEVEL READ COMMITTED; SELECT lion_index_count('lion_cnt_c10', 0) = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS agrees; agrees -------- t (1 row) DELETE FROM lion_cnt WHERE c10 = 0 AND i % 50 = 30; SELECT lion_index_count('lion_cnt_c10', 0) = (SELECT count(*) FROM lion_cnt WHERE c10 = 0) AS agrees_after_own_delete; agrees_after_own_delete ------------------------- t (1 row) ROLLBACK; -- Serializable transactions must take the same predicate locks an -- index-only scan would, and must still produce the right answer. BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT lion_ccmp('lion_cnt_c10', 'lion_cnt', 'c10', '0'); lion_ccmp ----------- ok 10000 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '1234'); lion_ccmp ----------- ok 40 (1 row) COMMIT; /* * Bounded recheck batches (2026-09-20 review, finding 6). * * The candidate TIDs of blocks that are not all-visible used to be kept until * the whole merge was over: 20 million of them - which a hot standby produces * for any 20-million-row posting set, because it never trusts the visibility * map - meant a 192 MiB array. The list is now flushed whenever it reaches a * budget taken from work_mem, at a block boundary so that no heap block is * visited twice. With the minimum work_mem the budget is about 10900 TIDs, * so the counts below take several batches each, and every answer and every * statistic must be exactly what one single batch produces. */ DELETE FROM lion_cnt WHERE i % 37 = 0; -- every heap page is dirty again CREATE TEMP TABLE lion_wm (q text, budget text, cnt bigint, blocks_skipped bigint, tids_rechecked bigint, blocks_rechecked bigint, cache_hits bigint); SET work_mem = '64kB'; INSERT INTO lion_wm SELECT 'b3f', 'min', * FROM lion_index_count_stats('lion_cnt_b3', false); INSERT INTO lion_wm SELECT 'c10', 'min', * FROM lion_index_count_stats('lion_cnt_c10', 0); SELECT lion_ccmp('lion_cnt_b3', 'lion_cnt', 'b3', 'false'); lion_ccmp ----------- ok 97297 (1 row) SELECT lion_ccmp('lion_cnt_c5000', 'lion_cnt', 'c5000', '1234'); lion_ccmp ----------- ok 39 (1 row) SELECT lion_ccmp2('lion_cnt_c10', '3', 'lion_cnt_m7', '2', 'lion_cnt', 'c10', 'm7'); lion_ccmp2 ------------ ok 2780 (1 row) RESET work_mem; INSERT INTO lion_wm SELECT 'b3f', 'big', * FROM lion_index_count_stats('lion_cnt_b3', false); INSERT INTO lion_wm SELECT 'c10', 'big', * FROM lion_index_count_stats('lion_cnt_c10', 0); -- one answer per query, whatever the budget was SELECT q, count(DISTINCT (cnt, blocks_skipped, tids_rechecked, blocks_rechecked)) AS answers FROM lion_wm GROUP BY q ORDER BY q; q | answers -----+--------- b3f | 1 c10 | 1 (2 rows) SELECT cnt = (SELECT count(*) FROM lion_cnt WHERE NOT b3) AS count_ok, tids_rechecked > 40000 AS enough_tids_for_several_batches FROM lion_wm WHERE q = 'b3f' AND budget = 'min'; count_ok | enough_tids_for_several_batches ----------+--------------------------------- t | t (1 row) -- a single count visits every heap block once, so it never has a cache hit SELECT count(*) FILTER (WHERE cache_hits <> 0) AS single_counts_with_cache_hits FROM lion_wm; single_counts_with_cache_hits ------------------------------- 0 (1 row) DROP TABLE lion_wm; /* * The batched heap recheck. * * Blocks that are not all-visible are visited one BLOCK at a time: the buffer * is read and share-locked once and every TID of the posting set that lives * on it is resolved under that one lock (lion_count.c, lion_recheck_heap_heap). * lion_index_count_stats() reports both numbers, so a posting set that is * dense enough to put many members on one page must report far fewer blocks * than TIDs -- that ratio IS the batching. * * The table is also the HOT test. fillfactor leaves room on every page, and * payload is in no index, so UPDATEs of it are HOT: the index keeps pointing * at the root of the chain and the recheck has to follow the chain to find * the version this snapshot can see. That is what heap_hot_search_buffer() * does, and getting it wrong would show up as a count that disagrees with * count(*) -- or, with a broken chain walk, as a count that agrees today and * not after the second UPDATE. */ CREATE TABLE lion_cnt_hot (id int4 NOT NULL, k int4 NOT NULL, g int4 NOT NULL, payload int4 NOT NULL) WITH (fillfactor = 50); INSERT INTO lion_cnt_hot SELECT i, i % 5, i % 37, 0 FROM generate_series(1, 20000) i; CREATE INDEX lion_cnt_hot_k ON lion_cnt_hot USING lion (k); CREATE INDEX lion_cnt_hot_g ON lion_cnt_hot USING lion (g); VACUUM (FREEZE, ANALYZE) lion_cnt_hot; -- everything all-visible: no heap visit, so no blocks either SELECT count = 4000 AS count_ok, blocks_skipped > 0 AS skipped_some, tids_rechecked = 0 AS nothing_rechecked, blocks_rechecked = 0 AS no_blocks_rechecked FROM lion_index_count_stats('lion_cnt_hot_k', 0); count_ok | skipped_some | nothing_rechecked | no_blocks_rechecked ----------+--------------+-------------------+--------------------- t | t | t | t (1 row) -- HOT updates: the chains the recheck has to walk. A HOT update adds no -- index entry, which is exactly why the index's TID count must not move. UPDATE lion_cnt_hot SET payload = payload + 1 WHERE id % 3 = 0; UPDATE lion_cnt_hot SET payload = payload + 1 WHERE id % 6 = 0; SELECT ntids = 20000 AS index_unchanged_by_hot_updates FROM lion_index_stats('lion_cnt_hot_k'); index_unchanged_by_hot_updates -------------------------------- t (1 row) DELETE FROM lion_cnt_hot WHERE id % 7 = 0; /* * Every page has been touched by now, so the whole posting set comes back * through the recheck: 4000 TIDs on a couple of hundred blocks. */ SELECT count = (SELECT count(*) FROM lion_cnt_hot WHERE k = 0) AS count_ok, tids_rechecked = 4000 AS all_rechecked, blocks_rechecked > 0 AS visited_blocks, blocks_rechecked * 10 < tids_rechecked AS batched_by_block FROM lion_index_count_stats('lion_cnt_hot_k', 0); count_ok | all_rechecked | visited_blocks | batched_by_block ----------+---------------+----------------+------------------ t | t | t | t (1 row) SELECT lion_ccmp('lion_cnt_hot_k', 'lion_cnt_hot', 'k', '0'); lion_ccmp ----------- ok 3429 (1 row) SELECT lion_ccmp('lion_cnt_hot_k', 'lion_cnt_hot', 'k', '3'); lion_ccmp ----------- ok 3429 (1 row) SELECT lion_ccmp('lion_cnt_hot_g', 'lion_cnt_hot', 'g', '0'); lion_ccmp ----------- ok 463 (1 row) SELECT lion_ccmp2('lion_cnt_hot_k', '1', 'lion_cnt_hot_g', '11', 'lion_cnt_hot', 'k', 'g'); lion_ccmp2 ------------ ok 93 (1 row) -- A third update on top of the deleted rows: longer chains, and roots whose -- chain ends in a dead tuple. UPDATE lion_cnt_hot SET payload = payload + 1 WHERE id % 11 = 0; SELECT lion_ccmp('lion_cnt_hot_k', 'lion_cnt_hot', 'k', '0'); lion_ccmp ----------- ok 3429 (1 row) SELECT lion_ccmp('lion_cnt_hot_k', 'lion_cnt_hot', 'k', '4'); lion_ccmp ----------- ok 3428 (1 row) /* * The same thing in a SERIALIZABLE transaction. The recheck resolves each * TID with heap_hot_search_buffer(), which is the function the table AM's own * fetch uses, so it takes the same per-tuple predicate locks; the all-visible * blocks that are never looked at are locked page-wise. Either way a * serializable reader must leave SIReadLocks behind on the table. */ BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT lion_ccmp('lion_cnt_hot_k', 'lion_cnt_hot', 'k', '0'); lion_ccmp ----------- ok 3429 (1 row) SELECT lion_ccmp2('lion_cnt_hot_k', '2', 'lion_cnt_hot_g', '5', 'lion_cnt_hot', 'k', 'g'); lion_ccmp2 ------------ ok 92 (1 row) SELECT count(*) > 0 AS took_predicate_locks FROM pg_locks WHERE mode = 'SIReadLock' AND relation = 'lion_cnt_hot'::regclass; took_predicate_locks ---------------------- t (1 row) COMMIT; -- ... and once more with every block all-visible again. VACUUM (FREEZE) lion_cnt_hot; BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT lion_ccmp('lion_cnt_hot_k', 'lion_cnt_hot', 'k', '0'); lion_ccmp ----------- ok 3429 (1 row) SELECT count(*) > 0 AS took_predicate_locks FROM pg_locks WHERE mode = 'SIReadLock' AND relation = 'lion_cnt_hot'::regclass; took_predicate_locks ---------------------- t (1 row) COMMIT; SELECT count = (SELECT count(*) FROM lion_cnt_hot WHERE k = 0) AS count_ok, tids_rechecked = 0 AS nothing_rechecked, blocks_rechecked = 0 AS no_blocks_rechecked FROM lion_index_count_stats('lion_cnt_hot_k', 0); count_ok | nothing_rechecked | no_blocks_rechecked ----------+-------------------+--------------------- t | t | t (1 row) /* * The GROUP BY path of DESIGN.md section 10 intersects the same WHERE posting * sets with every group, and lion_count.c answers those from a materialized * copy after the first group (the group's own set is still read page by page, * which is what keeps the visibility-map interlock). The pushdown is what * drives that code, so this one section turns it on -- and compares it * against the same query with the pushdown off, which is the reference. */ SET pg_lion.enable_count_pushdown = on; CREATE TEMP TABLE lion_grp_on AS SELECT g, count(*) AS n FROM lion_cnt_hot WHERE k = 0 GROUP BY g; CREATE TEMP TABLE lion_grp_on2 AS SELECT g, count(*) AS n FROM lion_cnt_hot GROUP BY g; SET pg_lion.enable_count_pushdown = off; CREATE TEMP TABLE lion_grp_off AS SELECT g, count(*) AS n FROM lion_cnt_hot WHERE k = 0 GROUP BY g; CREATE TEMP TABLE lion_grp_off2 AS SELECT g, count(*) AS n FROM lion_cnt_hot GROUP BY g; SELECT (SELECT count(*) FROM (SELECT * FROM lion_grp_on EXCEPT ALL SELECT * FROM lion_grp_off) x) AS extra_rows, (SELECT count(*) FROM (SELECT * FROM lion_grp_off EXCEPT ALL SELECT * FROM lion_grp_on) x) AS missing_rows, (SELECT count(*) FROM lion_grp_on) AS groups; extra_rows | missing_rows | groups ------------+--------------+-------- 0 | 0 | 37 (1 row) SELECT (SELECT count(*) FROM (SELECT * FROM lion_grp_on2 EXCEPT ALL SELECT * FROM lion_grp_off2) x) AS extra_rows, (SELECT count(*) FROM (SELECT * FROM lion_grp_off2 EXCEPT ALL SELECT * FROM lion_grp_on2) x) AS missing_rows, (SELECT count(*) FROM lion_grp_on2) AS groups; extra_rows | missing_rows | groups ------------+--------------+-------- 0 | 0 | 37 (1 row) -- and again with the dirty heap under it, so the materialized WHERE set is -- ANDed against groups whose blocks all go through the recheck DELETE FROM lion_cnt_hot WHERE id % 13 = 0; SET pg_lion.enable_count_pushdown = on; CREATE TEMP TABLE lion_grp_on3 AS SELECT g, count(*) AS n FROM lion_cnt_hot WHERE k = 0 GROUP BY g; SET pg_lion.enable_count_pushdown = off; CREATE TEMP TABLE lion_grp_off3 AS SELECT g, count(*) AS n FROM lion_cnt_hot WHERE k = 0 GROUP BY g; SELECT (SELECT count(*) FROM (SELECT * FROM lion_grp_on3 EXCEPT ALL SELECT * FROM lion_grp_off3) x) AS extra_rows, (SELECT count(*) FROM (SELECT * FROM lion_grp_off3 EXCEPT ALL SELECT * FROM lion_grp_on3) x) AS missing_rows, (SELECT sum(n) FROM lion_grp_on3) AS total; extra_rows | missing_rows | total ------------+--------------+------- 0 | 0 | 3165 (1 row) DROP TABLE lion_grp_on, lion_grp_on2, lion_grp_on3, lion_grp_off, lion_grp_off2, lion_grp_off3; /* * The per-query visibility cache (DESIGN.md section 9). * * A grouped count rechecks the dirty heap blocks of every group separately, * so 200 groups used to fetch the same dirty pages 200 times. A TID's * visibility under one MVCC snapshot cannot change while that snapshot is * held, so the answer for every root line pointer of a page is resolved once * and reused: the block visits of the later groups become bitmap lookups with * no buffer access at all. * * lion_index_count_group_stats(idx, use_cache) is the SQL image of the * GROUP BY driver -- the same entry scan, one count per group, one cache for * the whole run -- so both halves of the A/B fit in one query. What must * hold: * * - the answer does not depend on the cache, and is the answer of the * equivalent GROUP BY; * - the number of block visits does not depend on the cache either; what * changes is how many of them are fetches (blocks_rechecked) and how many * are cache hits; * - the fetches collapse to about two per dirty page (the first visit only * records the block, the second resolves it), whatever the group count. */ CREATE TEMP TABLE lion_vc (idx text, mode text, groups bigint, cnt bigint, blocks_skipped bigint, tids_rechecked bigint, blocks_rechecked bigint, cache_hits bigint, cache_full bigint); INSERT INTO lion_vc SELECT 'hot_g', 'nocache', * FROM lion_index_count_group_stats('lion_cnt_hot_g', false); INSERT INTO lion_vc SELECT 'hot_g', 'cached', * FROM lion_index_count_group_stats('lion_cnt_hot_g', true); INSERT INTO lion_vc SELECT 'hot_k', 'nocache', * FROM lion_index_count_group_stats('lion_cnt_hot_k', false); INSERT INTO lion_vc SELECT 'hot_k', 'cached', * FROM lion_index_count_group_stats('lion_cnt_hot_k', true); -- one answer per index, whether or not the cache was used ... SELECT idx, count(DISTINCT (groups, cnt, blocks_skipped, tids_rechecked)) AS answers, count(DISTINCT blocks_rechecked + cache_hits) AS block_visits FROM lion_vc GROUP BY idx ORDER BY idx; idx | answers | block_visits -------+---------+-------------- hot_g | 1 | 1 hot_k | 1 | 1 (2 rows) -- ... and it is the answer of the equivalent GROUP BY SELECT idx, cnt = (SELECT count(*) FROM lion_cnt_hot) AS rows_ok, groups = CASE idx WHEN 'hot_g' THEN (SELECT count(*) FROM (SELECT g FROM lion_cnt_hot GROUP BY g) x) WHEN 'hot_k' THEN (SELECT count(*) FROM (SELECT k FROM lion_cnt_hot GROUP BY k) x) END AS groups_ok FROM lion_vc WHERE mode = 'cached' ORDER BY idx; idx | rows_ok | groups_ok -------+---------+----------- hot_g | t | t hot_k | t | t (2 rows) /* * The cache is used and it replaces fetches with hits. How big the win is * depends on how many groups there are to share the answer: hot_g has 37 * groups and hot_k has 5, and the cached run fetches each dirty page about * twice either way, so the ratio is the group count minus a constant. */ SELECT idx, c.cache_hits > 0 AS cache_used, c.cache_full = 0 AS budget_was_enough, n.cache_hits = 0 AS nocache_has_no_hits, n.blocks_rechecked > c.blocks_rechecked AS fewer_fetches, n.blocks_rechecked > 5 * c.blocks_rechecked AS many_fewer_fetches FROM (SELECT * FROM lion_vc WHERE mode = 'cached') c JOIN (SELECT * FROM lion_vc WHERE mode = 'nocache') n USING (idx) ORDER BY idx; idx | cache_used | budget_was_enough | nocache_has_no_hits | fewer_fetches | many_fewer_fetches -------+------------+-------------------+---------------------+---------------+-------------------- hot_g | t | t | t | t | t hot_k | t | t | t | t | f (2 rows) DELETE FROM lion_vc; /* * Serializable isolation. A cache hit calls neither * HeapCheckForSerializableConflictOut() nor PredicateLockTID(), so the page * whose answer was resolved is predicate-locked once, the way an index-only * scan locks a page it skips. The result must be the same and the locks must * be there (SSI may coarsen page locks into a relation lock, which is why * only their presence is asserted). */ BEGIN ISOLATION LEVEL SERIALIZABLE; INSERT INTO lion_vc SELECT 'hot_g', 'serializable', * FROM lion_index_count_group_stats('lion_cnt_hot_g', true); SELECT cnt = (SELECT count(*) FROM lion_cnt_hot) AS rows_ok, cache_hits > 0 AS cache_used FROM lion_vc WHERE mode = 'serializable'; rows_ok | cache_used ---------+------------ t | t (1 row) SELECT count(*) > 0 AS took_predicate_locks FROM pg_locks WHERE mode = 'SIReadLock' AND relation = 'lion_cnt_hot'::regclass; took_predicate_locks ---------------------- t (1 row) COMMIT; /* * A cache that fills up. The budget is work_mem (in entries), and when it is * reached nothing more is inserted: the blocks that did not get in are * fetched per batch, exactly as they were before the cache existed, and * cache_full counts those visits. lion_cnt is 200k rows over ~1600 heap * pages, every one of them dirtied by the DELETE above, which is well past * what 64kB of entries holds. */ SET work_mem = '64kB'; INSERT INTO lion_vc SELECT 'c10', 'full', * FROM lion_index_count_group_stats('lion_cnt_c10', true); RESET work_mem; INSERT INTO lion_vc SELECT 'c10', 'cached', * FROM lion_index_count_group_stats('lion_cnt_c10', true); SELECT count(DISTINCT (groups, cnt, blocks_skipped, tids_rechecked)) AS answers, count(DISTINCT blocks_rechecked + cache_hits) AS block_visits FROM lion_vc WHERE idx = 'c10'; answers | block_visits ---------+-------------- 1 | 1 (1 row) SELECT cnt = (SELECT count(*) FROM lion_cnt) AS rows_ok, groups = 10 AS groups_ok FROM lion_vc WHERE idx = 'c10' AND mode = 'full'; rows_ok | groups_ok ---------+----------- t | t (1 row) SELECT (SELECT cache_full FROM lion_vc WHERE idx='c10' AND mode='full') > 0 AS ran_out_of_budget, (SELECT cache_full FROM lion_vc WHERE idx='c10' AND mode='cached') = 0 AS budget_was_enough, (SELECT cache_hits FROM lion_vc WHERE idx='c10' AND mode='full') > 0 AS still_cached_something, (SELECT blocks_rechecked FROM lion_vc WHERE idx='c10' AND mode='full') > (SELECT blocks_rechecked FROM lion_vc WHERE idx='c10' AND mode='cached') AS a_small_cache_fetches_more; ran_out_of_budget | budget_was_enough | still_cached_something | a_small_cache_fetches_more -------------------+-------------------+------------------------+---------------------------- t | t | t | t (1 row) DROP TABLE lion_vc; -- the cache is on by default, and the function is STRICT like the others SELECT count = (SELECT count(*) FROM lion_cnt) AS rows_ok, cache_hits > 0 AS cache_used FROM lion_index_count_group_stats('lion_cnt_c10'); rows_ok | cache_used ---------+------------ t | t (1 row) SELECT lion_index_count_group_stats(NULL) IS NULL AS null_index; null_index ------------ t (1 row) /* Error cases. */ -- wrong key type SELECT lion_index_count('lion_cnt_c10', 'nope'::text); ERROR: type text cannot be compared with index "lion_cnt_c10" SELECT lion_index_count('lion_cnt_t1000', 1); ERROR: type integer cannot be compared with index "lion_cnt_t1000" -- not a lion index CREATE INDEX lion_cnt_btree ON lion_cnt (c10); SELECT lion_index_count('lion_cnt_btree', 0); ERROR: index "lion_cnt_btree" is not a lion index -- not an index at all SELECT lion_index_count('lion_cnt', 0); ERROR: "lion_cnt" is not an index -- two indexes on different tables CREATE TABLE lion_cnt_other (k int4); CREATE INDEX lion_cnt_other_k ON lion_cnt_other USING lion (k); SELECT lion_index_count('lion_cnt_c10', 0, 'lion_cnt_other_k', 0); ERROR: indexes "lion_cnt_c10" and "lion_cnt_other_k" are not on the same table -- a materialized view created WITH NO DATA: its heap and indexes are empty, -- and the query refuses to run; so does every count through it, rather than -- answering 0 (2026-09-25 review) CREATE MATERIALIZED VIEW lion_cnt_mv AS SELECT i % 10 AS k FROM generate_series(1, 1000) i WITH NO DATA; CREATE INDEX lion_cnt_mv_k ON lion_cnt_mv USING lion (k); SELECT count(*) FROM lion_cnt_mv WHERE k = 1; ERROR: materialized view "lion_cnt_mv" has not been populated SELECT lion_index_count('lion_cnt_mv_k', 1); ERROR: materialized view "lion_cnt_mv" has not been populated SELECT lion_index_count('lion_cnt_mv_k', 1, 'lion_cnt_mv_k', 2); ERROR: materialized view "lion_cnt_mv" has not been populated SELECT count FROM lion_index_count_stats('lion_cnt_mv_k', 1); ERROR: materialized view "lion_cnt_mv" has not been populated SELECT lion_index_count_any('lion_cnt_mv_k', ARRAY[1, 2]); ERROR: materialized view "lion_cnt_mv" has not been populated SELECT groups FROM lion_index_count_group_stats('lion_cnt_mv_k'); ERROR: materialized view "lion_cnt_mv" has not been populated REFRESH MATERIALIZED VIEW lion_cnt_mv; SELECT lion_ccmp('lion_cnt_mv_k', 'lion_cnt_mv', 'k', '1'); lion_ccmp ----------- ok 100 (1 row) SELECT lion_index_count_any('lion_cnt_mv_k', ARRAY[1, 2]) AS count_any; count_any ----------- 200 (1 row) DROP MATERIALIZED VIEW lion_cnt_mv; /* * Indexes that exist but that this transaction may not use (2026-09-20 * review, finding 2). A direct SQL count was handed an index nobody vetted, * so it makes the checks get_relation_info() makes for a query; the * cross-session indcheckxmin case, which is the one that produced a wrong * answer, is in test/isolation/count_checkxmin.spec. */ CREATE TABLE lion_cnt_elig (k int4); INSERT INTO lion_cnt_elig SELECT i % 3 FROM generate_series(1, 300) i; CREATE INDEX lion_cnt_elig_k ON lion_cnt_elig USING lion (k); SELECT lion_index_count('lion_cnt_elig_k', 1); lion_index_count ------------------ 100 (1 row) UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'lion_cnt_elig_k'::regclass; SELECT lion_index_count('lion_cnt_elig_k', 1); ERROR: cannot count through index "lion_cnt_elig_k" because the index is not valid SELECT lion_index_count('lion_cnt_elig_k', 1, 'lion_cnt_elig_k', 1); ERROR: cannot count through index "lion_cnt_elig_k" because the index is not valid -- the verifier shares the check SELECT lion_index_verify('lion_cnt_elig_k', true); ERROR: cannot verify index "lion_cnt_elig_k" against the heap because the index is not valid UPDATE pg_index SET indisvalid = true, indisready = false WHERE indexrelid = 'lion_cnt_elig_k'::regclass; SELECT lion_index_count('lion_cnt_elig_k', 1); ERROR: cannot count through index "lion_cnt_elig_k" because the index is not ready for queries UPDATE pg_index SET indisready = true WHERE indexrelid = 'lion_cnt_elig_k'::regclass; SELECT lion_index_count('lion_cnt_elig_k', 1); lion_index_count ------------------ 100 (1 row) SELECT lion_index_verify('lion_cnt_elig_k', true); lion_index_verify ------------------- (1 row) DROP TABLE lion_cnt_elig; -- NULL arguments: a NULL key counts 0, as `WHERE c10 = NULL` does, after -- the same checks as any key; a NULL index names none, and answers NULL SELECT lion_index_count('lion_cnt_c10', NULL::int4) AS null_key, (SELECT count(*) FROM lion_cnt WHERE c10 = NULL::int4) AS query; null_key | query ----------+------- 0 | 0 (1 row) SELECT lion_index_count('lion_cnt_c10', NULL) AS null_literal; null_literal -------------- 0 (1 row) SELECT lion_index_count(NULL, 0) IS NULL AS null_index; null_index ------------ t (1 row) SELECT lion_index_count('lion_cnt_c10', 1, 'lion_cnt_m7', NULL::int4) AS null_key2; null_key2 ----------- 0 (1 row) SELECT * FROM lion_index_count_stats('lion_cnt_c10', NULL::int4); count | blocks_skipped | tids_rechecked | blocks_rechecked | cache_hits -------+----------------+----------------+------------------+------------ 0 | 0 | 0 | 0 | 0 (1 row) SELECT lion_index_count('lion_cnt_c10', NULL::text); ERROR: type text cannot be compared with index "lion_cnt_c10" -- The key is taken as `col = key` takes it: the parser's operator, and the -- key coerced to its input as the query would coerce it (DESIGN.md section 9, -- "SQL surface"). Each count beside the query it stands for. CREATE TABLE lion_cnt_cast (n numeric, f4 float4, i int4, t text, b char(4)); INSERT INTO lion_cnt_cast SELECT g % 10, (g % 10) / 10.0, g % 10, 't' || (g % 10), 'b' || (g % 3) FROM generate_series(1, 1000) g; CREATE INDEX lion_cnt_cast_n ON lion_cnt_cast USING lion (n); CREATE INDEX lion_cnt_cast_f4 ON lion_cnt_cast USING lion (f4); CREATE INDEX lion_cnt_cast_i ON lion_cnt_cast USING lion (i); CREATE INDEX lion_cnt_cast_t ON lion_cnt_cast USING lion (t); CREATE INDEX lion_cnt_cast_b ON lion_cnt_cast USING lion (b); VACUUM ANALYZE lion_cnt_cast; -- an int key on a numeric column: `n = 1` casts the key to numeric SELECT lion_index_count('lion_cnt_cast_n', 1) AS count, (SELECT count(*) FROM lion_cnt_cast WHERE n = 1) AS query; count | query -------+------- 100 | 100 (1 row) SELECT lion_index_count_any('lion_cnt_cast_n', ARRAY[1, 2]) AS count, (SELECT count(*) FROM lion_cnt_cast WHERE n = ANY (ARRAY[1, 2])) AS query; count | query -------+------- 200 | 200 (1 row) -- a numeric key on a float4 column: `f4 = 0.1` is `float4 = float8`, which -- no float4 value satisfies, and `f4 = 0.5` is exact in both SELECT lion_index_count('lion_cnt_cast_f4', 0.1) AS count, (SELECT count(*) FROM lion_cnt_cast WHERE f4 = 0.1) AS query; count | query -------+------- 0 | 0 (1 row) SELECT lion_index_count('lion_cnt_cast_f4', 0.5) AS count, (SELECT count(*) FROM lion_cnt_cast WHERE f4 = 0.5) AS query; count | query -------+------- 100 | 100 (1 row) SELECT lion_index_count('lion_cnt_cast_f4', 0.1::float4) AS count, (SELECT count(*) FROM lion_cnt_cast WHERE f4 = 0.1::float4) AS query; count | query -------+------- 100 | 100 (1 row) -- untyped literals take the column's type, as in the query SELECT lion_index_count('lion_cnt_cast_f4', '0.1') AS count, (SELECT count(*) FROM lion_cnt_cast WHERE f4 = '0.1') AS query; count | query -------+------- 100 | 100 (1 row) SELECT lion_index_count('lion_cnt_cast_i', '3') AS count, (SELECT count(*) FROM lion_cnt_cast WHERE i = '3') AS query; count | query -------+------- 100 | 100 (1 row) SELECT lion_index_count('lion_cnt_cast_t', 't3') AS count, (SELECT count(*) FROM lion_cnt_cast WHERE t = 't3') AS query; count | query -------+------- 100 | 100 (1 row) SELECT lion_index_count('lion_cnt_cast_b', 'b1 ') AS count, (SELECT count(*) FROM lion_cnt_cast WHERE b = 'b1 ') AS query; count | query -------+------- 334 | 334 (1 row) SELECT lion_index_count('lion_cnt_cast_i', 3, 'lion_cnt_cast_t', 't3') AS count, (SELECT count(*) FROM lion_cnt_cast WHERE i = 3 AND t = 't3') AS query; count | query -------+------- 100 | 100 (1 row) SELECT lion_index_count_any('lion_cnt_cast_i', '{3,4,NULL}') AS count, (SELECT count(*) FROM lion_cnt_cast WHERE i = ANY ('{3,4,NULL}')) AS query; count | query -------+------- 200 | 200 (1 row) SELECT lion_index_count_any('lion_cnt_cast_i', NULL::int[]) AS count, (SELECT count(*) FROM lion_cnt_cast WHERE i = ANY (NULL::int[])) AS query; count | query -------+------- 0 | 0 (1 row) -- where the query would cast the COLUMN, no count is made: `i = 1.5` is -- `numeric = numeric` on i::numeric, and 1.5 cast to integer would count -- the rows of 2 \set VERBOSITY default SELECT lion_index_count('lion_cnt_cast_i', 1.5); ERROR: type numeric cannot be compared with index "lion_cnt_cast_i" DETAIL: `col = key` would compare them with operator =(numeric,numeric), which is not the equality of the index's type integer. HINT: Cast the key to type integer. SELECT lion_index_count('lion_cnt_cast_b', 'b1'::text); ERROR: type text cannot be compared with index "lion_cnt_cast_b" DETAIL: `col = key` would compare them with operator =(text,text), which is not the equality of the index's type character. HINT: Cast the key to type character. \set VERBOSITY terse SELECT lion_index_count('lion_cnt_cast_i', 'x'); ERROR: invalid input syntax for type integer: "x" SELECT lion_index_count_any('lion_cnt_cast_i', 3); ERROR: the keys of a count through index "lion_cnt_cast_i" must be an array, not type integer DROP TABLE lion_cnt_cast; DROP TABLE lion_cnt_other; DROP TABLE lion_cnt_hot; DROP TABLE lion_cnt_tiny; DROP TABLE lion_cnt; /* * DROP EXTENSION + CREATE EXTENSION in one backend (2026-09-20 review, * finding 7). CASCADE takes every lion index with it, so recreating the * extension legitimately gives the access method a NEW pg_am Oid. The count * functions used to cache the old one in a process-local static and then * reject every index with "is not a lion index"; the Oid now comes from * the AMNAME syscache, which is invalidated properly. (This is the last * thing this file does: the CASCADE drops the indexes of every lion index * in the database.) */ CREATE TABLE lion_cnt_re (k int4); INSERT INTO lion_cnt_re SELECT i % 10 FROM generate_series(1, 1000) i; CREATE INDEX lion_cnt_re_k ON lion_cnt_re USING lion (k); SELECT lion_index_count('lion_cnt_re_k', 3); lion_index_count ------------------ 100 (1 row) DROP EXTENSION pg_lion CASCADE; CREATE EXTENSION pg_lion; SELECT to_regclass('lion_cnt_re_k') IS NULL AS index_dropped_by_cascade; index_dropped_by_cascade -------------------------- t (1 row) CREATE INDEX lion_cnt_re_k ON lion_cnt_re USING lion (k); SELECT lion_index_count('lion_cnt_re_k', 3); lion_index_count ------------------ 100 (1 row) SELECT lion_index_verify('lion_cnt_re_k', true); lion_index_verify ------------------- (1 row) SELECT count(*) FROM lion_cnt_re WHERE k = 3; count ------- 100 (1 row) DROP TABLE lion_cnt_re; DROP FUNCTION lion_ccmp(text, text, text, text); DROP FUNCTION lion_ccmp2(text, text, text, text, text, text, text);