-- count(*) over the index (SiftCount): visibility map pages from bitmaps, other pages through -- the heap, equal to a sequential count under dead, deleted and aborted rows CREATE EXTENSION pg_sift; CREATE TABLE c (id int PRIMARY KEY, body text, grp int); INSERT INTO c SELECT i, (ARRAY['quick brown fox', 'lazy dog', 'the quick fox', 'brown dog', 'fox'])[1 + i % 5] || ' w' || i % 7, i % 3 FROM generate_series(1, 2000) i; INSERT INTO c VALUES (2001, NULL, 0), (2002, '', 0); CREATE INDEX c_idx ON c USING sift (body) WITH (positions = on); VACUUM ANALYZE c; -- the count through the index and by evaluating every document CREATE FUNCTION via_scan(q text) RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE n bigint; BEGIN EXECUTE 'SELECT count(*) FROM c WHERE body @@> $1' INTO n USING q; RETURN n; END $$; CREATE FUNCTION via_seq(q text) RETURNS bigint LANGUAGE sql SET sift.enable_count = off SET sift.enable_scan = off SET enable_bitmapscan = off SET enable_indexscan = off RETURN (SELECT count(*) FROM c WHERE body @@> q); CREATE FUNCTION same(q text) RETURNS text LANGUAGE sql RETURN via_scan(q) || CASE WHEN via_scan(q) = via_seq(q) THEN ' ok' ELSE ' MISMATCH ' || via_seq(q) END; CREATE FUNCTION sift_plan(q text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE l text; r text := ''; BEGIN FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP r := r || l || E'\n'; END LOOP; RETURN rtrim(r, E'\n'); END $$; CREATE FUNCTION all_same() RETURNS TABLE (q text, result text) LANGUAGE sql AS $$ SELECT q, same(q) FROM (VALUES ('fox'), ('quick fox'), ('fox NOT quick'), ('dog OR fox'), ('"quick fox"'), ('"brown fox" OR dog'), ('fo*'), ('/d.g/'), ('lazzy~1'), ('w3'), ('nothing'), ('fox^2')) v(q) $$; SELECT * FROM all_same(); q | result --------------------+--------- fox | 1200 ok quick fox | 800 ok fox NOT quick | 400 ok dog OR fox | 2000 ok "quick fox" | 400 ok "brown fox" OR dog | 1200 ok fo* | 1200 ok /d.g/ | 800 ok lazzy~1 | 400 ok w3 | 286 ok nothing | 0 ok fox^2 | 1200 ok (12 rows) SELECT sift_plan('SELECT count(*) FROM c WHERE body @@> ''fox'''); sift_plan ------------------------- Custom Scan (SiftCount)+ Index: c_idx + Query: 'fox'::text (1 row) SELECT count(*) FROM c WHERE body @@> NULL::text; count ------- 0 (1 row) -- dead rows: deleted but not vacuumed (the deleted pages leave the visibility map, the others stay) DELETE FROM c WHERE id % 10 = 0; SELECT * FROM all_same(); q | result --------------------+--------- fox | 1000 ok quick fox | 600 ok fox NOT quick | 400 ok dog OR fox | 1800 ok "quick fox" | 400 ok "brown fox" OR dog | 1000 ok fo* | 1000 ok /d.g/ | 800 ok lazzy~1 | 400 ok w3 | 257 ok nothing | 0 ok fox^2 | 1000 ok (12 rows) DELETE FROM c WHERE id BETWEEN 1500 AND 1800; SELECT * FROM all_same(); q | result --------------------+--------- fox | 850 ok quick fox | 510 ok fox NOT quick | 340 ok dog OR fox | 1530 ok "quick fox" | 340 ok "brown fox" OR dog | 850 ok fo* | 850 ok /d.g/ | 680 ok lazzy~1 | 340 ok w3 | 218 ok nothing | 0 ok fox^2 | 850 ok (12 rows) -- uncommitted and rolled back deletes BEGIN; DELETE FROM c WHERE body @@> 'fox'; SELECT count(*) FROM c WHERE body @@> 'fox'; count ------- 0 (1 row) SAVEPOINT s1; DELETE FROM c; SELECT count(*) FROM c WHERE body @@> 'dog'; count ------- 0 (1 row) ROLLBACK TO s1; SELECT count(*) FROM c WHERE body @@> 'dog'; count ------- 680 (1 row) ROLLBACK; SELECT * FROM all_same(); q | result --------------------+--------- fox | 850 ok quick fox | 510 ok fox NOT quick | 340 ok dog OR fox | 1530 ok "quick fox" | 340 ok "brown fox" OR dog | 850 ok fo* | 850 ok /d.g/ | 680 ok lazzy~1 | 340 ok w3 | 218 ok nothing | 0 ok fox^2 | 850 ok (12 rows) -- not every statement is a plain count: other aggregates, groups, filters keep the other plans SELECT sift_plan('SELECT count(*), max(id) FROM c WHERE body @@> ''fox'''); sift_plan ----------------------------------- Aggregate + -> Custom Scan (SiftScan) on c+ Index: c_idx + Query: 'fox'::text (1 row) SELECT sift_plan('SELECT grp, count(*) FROM c WHERE body @@> ''fox'' GROUP BY grp'); sift_plan ----------------------------------- HashAggregate + Group Key: grp + -> Custom Scan (SiftScan) on c+ Index: c_idx + Query: 'fox'::text (1 row) SELECT sift_plan('SELECT count(*) FROM c WHERE body @@> ''fox'' AND grp = 1'); sift_plan ----------------------------------- Aggregate + -> Custom Scan (SiftScan) on c+ Index: c_idx + Query: 'fox'::text (1 row) SELECT count(*) FROM c WHERE body @@> 'fox' AND grp = 1; count ------- 283 (1 row) SELECT count(*), count(id) FROM c WHERE body @@> 'fox'; count | count -------+------- 850 | 850 (1 row) SET sift.enable_count = off; SELECT sift_plan('SELECT count(*) FROM c WHERE body @@> ''fox''') !~ 'SiftCount' AS other_plan; other_plan ------------ t (1 row) RESET sift.enable_count; -- vacuum clears the deleted documents' bits (dirty superblocks, pages back in the visibility map), and -- inserted or updated rows put pages out of it again next to them VACUUM c; SELECT * FROM all_same(); q | result --------------------+--------- fox | 850 ok quick fox | 510 ok fox NOT quick | 340 ok dog OR fox | 1530 ok "quick fox" | 340 ok "brown fox" OR dog | 850 ok fo* | 850 ok /d.g/ | 680 ok lazzy~1 | 340 ok w3 | 218 ok nothing | 0 ok fox^2 | 850 ok (12 rows) INSERT INTO c SELECT 3000 + i, 'lazy fox w' || i, 1 FROM generate_series(1, 30) i; UPDATE c SET grp = grp + 1 WHERE id % 97 = 0; SELECT * FROM all_same(); q | result --------------------+--------- fox | 880 ok quick fox | 510 ok fox NOT quick | 370 ok dog OR fox | 1560 ok "quick fox" | 340 ok "brown fox" OR dog | 850 ok fo* | 880 ok /d.g/ | 680 ok lazzy~1 | 370 ok w3 | 219 ok nothing | 0 ok fox^2 | 880 ok (12 rows) VACUUM c; SELECT * FROM all_same(); q | result --------------------+--------- fox | 880 ok quick fox | 510 ok fox NOT quick | 370 ok dog OR fox | 1560 ok "quick fox" | 340 ok "brown fox" OR dog | 850 ok fo* | 880 ok /d.g/ | 680 ok lazzy~1 | 370 ok w3 | 219 ok nothing | 0 ok fox^2 | 880 ok (12 rows) -- without positions the heap decides the phrases: the same counts, through dead rows and new ones DROP INDEX c_idx; CREATE INDEX c_idx ON c USING sift (body) WITH (positions = off); VACUUM ANALYZE c; SELECT * FROM all_same(); q | result --------------------+--------- fox | 880 ok quick fox | 510 ok fox NOT quick | 370 ok dog OR fox | 1560 ok "quick fox" | 340 ok "brown fox" OR dog | 850 ok fo* | 880 ok /d.g/ | 680 ok lazzy~1 | 370 ok w3 | 219 ok nothing | 0 ok fox^2 | 880 ok (12 rows) DELETE FROM c WHERE id % 13 = 0; SELECT * FROM all_same(); q | result --------------------+--------- fox | 812 ok quick fox | 471 ok fox NOT quick | 341 ok dog OR fox | 1440 ok "quick fox" | 314 ok "brown fox" OR dog | 785 ok fo* | 812 ok /d.g/ | 628 ok lazzy~1 | 341 ok w3 | 201 ok nothing | 0 ok fox^2 | 812 ok (12 rows) INSERT INTO c SELECT 4000 + i, 'brown fox w' || i, 2 FROM generate_series(1, 30) i; SELECT * FROM all_same(); q | result --------------------+--------- fox | 842 ok quick fox | 471 ok fox NOT quick | 371 ok dog OR fox | 1470 ok "quick fox" | 314 ok "brown fox" OR dog | 815 ok fo* | 842 ok /d.g/ | 628 ok lazzy~1 | 341 ok w3 | 202 ok nothing | 0 ok fox^2 | 842 ok (12 rows) VACUUM c; SELECT * FROM all_same(); q | result --------------------+--------- fox | 842 ok quick fox | 471 ok fox NOT quick | 371 ok dog OR fox | 1470 ok "quick fox" | 314 ok "brown fox" OR dog | 815 ok fo* | 842 ok /d.g/ | 628 ok lazzy~1 | 341 ok w3 | 202 ok nothing | 0 ok fox^2 | 842 ok (12 rows) DROP TABLE c CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to function via_seq(text) drop cascades to function same(text) DROP FUNCTION via_scan(text), sift_plan(text), all_same(); DROP EXTENSION pg_sift;