-- Postings round trip: every (term, tid, tf, dl) decoded from the index -- equals an independent computation from the heap through sift.tokenize, -- for one or several segments, in memory or through sorted runs. CREATE EXTENSION pg_sift; CREATE EXTENSION pg_sift_test; -- docs: a query returning (tid, body) of the indexed rows. The comparison runs in 4 batches of heap blocks, summed: the whole -- corpus is compared, but the joins and sorts of a batch fit a small temp file instead of half a gigabyte. CREATE FUNCTION roundtrip(idx regclass, docs text) RETURNS TABLE (postings bigint, missing bigint, extra bigint, differ bigint) LANGUAGE plpgsql AS $f$ DECLARE b int; r record; BEGIN postings := 0; missing := 0; extra := 0; differ := 0; FOR b IN 0..3 LOOP EXECUTE format($q$ WITH d AS (SELECT * FROM (%s) q WHERE (q.tid::text::point)[0]::int %% 4 = %s), tok AS (SELECT d.tid, t.token FROM d, sift.tokenize(d.body, %L) t), len AS (SELECT tid, least(count(*), 65535)::int AS dl FROM tok GROUP BY tid), want AS (SELECT tok.token AS term, tok.tid, least(count(*), 255)::int AS tf, len.dl FROM tok JOIN len USING (tid) GROUP BY 1, 2, 4), got AS (SELECT term, tid, tf, dl FROM sift._test_postings(%L) WHERE (tid::text::point)[0]::int %% 4 = %s) SELECT count(*) AS n, count(*) FILTER (WHERE g.term IS NULL) AS m, count(*) FILTER (WHERE w.term IS NULL) AS x, count(*) FILTER (WHERE g.tf <> w.tf OR g.dl <> w.dl) AS dd FROM want w FULL JOIN got g ON g.term = w.term AND g.tid = w.tid$q$, docs, b, idx, idx, b) INTO r; postings := postings + r.n; missing := missing + r.m; extra := extra + r.x; differ := differ + r.dd; END LOOP; RETURN NEXT; END $f$; -- 8,000 documents of about 900 bytes (4 superblocks: build_segments = 3 gets three segments, the groups of a GROUPED term are several), Zipf-like vocabulary of 20k -- words, a term in every document (GROUPED), repeated words (tf escapes, tf and dl saturation) SELECT setseed(0.42); setseed --------- (1 row) CREATE TABLE z (id int, body text, pad text); INSERT INTO z SELECT i, 'common ' || (SELECT string_agg('w' || floor(exp(random() * ln(20000)))::int, ' ') FROM generate_series(1, 1 + (random() * 60)::int + i % 2)), repeat('p', 700) FROM generate_series(1, 8000) i; ALTER TABLE z ALTER COLUMN pad SET STORAGE PLAIN; UPDATE z SET body = body || repeat(' rep', id % 40) WHERE id % 97 = 0; UPDATE z SET body = body || repeat(' many', 300) || repeat(' x', 70000) WHERE id = 5000; VACUUM FULL z; CREATE INDEX z_idx ON z USING sift (body); SELECT gen, ndocs, sum_dl, nterms, sb_lo, sb_hi FROM sift.segments('z_idx'); gen | ndocs | sum_dl | nterms | sb_lo | sb_hi -----+-------+--------+--------+-------+------- 1 | 8000 | 326190 | 18007 | 0 | 3 (1 row) SELECT * FROM sift.segment_postings('z_idx'); gen | df_class | terms | postings | inline_terms | list_terms | grouped_terms | codec_bytes | sidecar_bytes | bits_per_posting -----+----------+-------+----------+--------------+------------+---------------+-------------+---------------+-------------------- 1 | 1 | 3597 | 3597 | 3597 | 0 | 0 | 0 | 0 | 0 1 | 2 | 3524 | 7048 | 3524 | 0 | 0 | 0 | 0 | 0 1 | 3-15 | 9213 | 51997 | 0 | 9213 | 0 | 147578 | 131 | 22.72577264072927 1 | 16-127 | 1479 | 54858 | 0 | 1479 | 0 | 80456 | 1973 | 12.020708009770681 1 | 128-1K | 171 | 52382 | 0 | 171 | 0 | 49310 | 7467 | 8.67122293917758 1 | 1K-8K | 23 | 59900 | 0 | 23 | 0 | 31935 | 14685 | 6.226377295492488 (6 rows) SELECT sift.verify('z_idx'); verify -------- (1 row) SELECT * FROM roundtrip('z_idx', 'SELECT ctid AS tid, body FROM z'); postings | missing | extra | differ ----------+---------+-------+-------- 229782 | 0 | 0 | 0 (1 row) -- Bounded memory: 1 MB spills sorted runs that merge into one segment SET maintenance_work_mem = '1MB'; REINDEX INDEX z_idx; SELECT gen, ndocs, sum_dl, nterms FROM sift.segments('z_idx'); gen | ndocs | sum_dl | nterms -----+-------+--------+-------- 1 | 8000 | 326190 | 18007 (1 row) SELECT sift.verify('z_idx'); verify -------- (1 row) SELECT * FROM roundtrip('z_idx', 'SELECT ctid AS tid, body FROM z'); postings | missing | extra | differ ----------+---------+-------+-------- 229782 | 0 | 0 | 0 (1 row) -- Runs and forced heap ranges: several segments CREATE INDEX z_seg ON z USING sift (body) WITH (build_segments = 3); SELECT gen, ndocs, sum_dl, nterms, sb_lo, sb_hi FROM sift.segments('z_seg'); gen | ndocs | sum_dl | nterms | sb_lo | sb_hi -----+-------+--------+--------+-------+------- 1 | 2667 | 86640 | 12579 | 0 | 1 2 | 2673 | 85588 | 12580 | 1 | 2 3 | 2660 | 153962 | 12582 | 2 | 3 (3 rows) SELECT sift.verify('z_seg'); verify -------- (1 row) SELECT * FROM roundtrip('z_seg', 'SELECT ctid AS tid, body FROM z'); postings | missing | extra | differ ----------+---------+-------+-------- 229782 | 0 | 0 | 0 (1 row) RESET maintenance_work_mem; -- Partial and expression indexes, other analyzer options CREATE INDEX z_part ON z USING sift (body) WHERE id % 10 = 3; SELECT sift.verify('z_part'); verify -------- (1 row) SELECT * FROM roundtrip('z_part', 'SELECT ctid AS tid, body FROM z WHERE id % 10 = 3'); postings | missing | extra | differ ----------+---------+-------+-------- 22833 | 0 | 0 | 0 (1 row) CREATE INDEX z_expr ON z USING sift ((upper(substr(body, 1, 40)) || ' Ünïcödé ' || id)) WITH (stemmer = 'en', accent_folding = false); SELECT sift.verify('z_expr'); verify -------- (1 row) SELECT * FROM roundtrip('z_expr', $$SELECT ctid AS tid, upper(substr(body, 1, 40)) || ' Ünïcödé ' || id AS body FROM z$$); postings | missing | extra | differ ----------+---------+-------+-------- 79510 | 0 | 0 | 0 (1 row) -- UTF-8, emoji, duplicates CREATE TABLE u (body text); INSERT INTO u SELECT CASE i % 4 WHEN 0 THEN 'Crème brûlée ' || i WHEN 1 THEN '東京 🗼🇯🇵 👍🏽 ' || (i % 7) WHEN 2 THEN 'same same same' ELSE 'Džemal Straße fi ' || (i % 3) END FROM generate_series(1, 2000) i; CREATE INDEX u_idx ON u USING sift (body); SELECT sift.verify('u_idx'); verify -------- (1 row) SELECT * FROM roundtrip('u_idx', 'SELECT ctid AS tid, body FROM u'); postings | missing | extra | differ ----------+---------+-------+-------- 7000 | 0 | 0 | 0 (1 row) DROP TABLE z, u; DROP FUNCTION roundtrip; DROP EXTENSION pg_sift_test; DROP EXTENSION pg_sift;