-- CREATE INDEX: segments, statistics, options and sift.verify on small
-- corpora; the index answers queries as a bitmap scan.
CREATE EXTENSION pg_sift;
-- Empty table: metapage and directory only
CREATE TABLE t0 (body text);
CREATE INDEX t0_idx ON t0 USING sift (body);
SELECT count(*) FROM sift.segments('t0_idx');
 count 
-------
     0
(1 row)

SELECT sift.verify('t0_idx');
 verify 
--------
 
(1 row)

-- One document
CREATE TABLE t1 (body text);
INSERT INTO t1 VALUES ('Hello, World! hello');
CREATE INDEX t1_idx ON t1 USING sift (body);
SELECT gen, kind, ndocs, sum_dl, avgdl, sb_lo, sb_hi, dirty, nterms, avgdl_ref
FROM sift.segments('t1_idx');
 gen |   kind    | ndocs | sum_dl | avgdl | sb_lo | sb_hi | dirty | nterms | avgdl_ref 
-----+-----------+-------+--------+-------+-------+-------+-------+--------+-----------
   1 | immutable |     1 |      3 |     3 |     0 |     0 | f     |      2 |         3
(1 row)

SELECT df_class, terms, postings, inline_terms, list_terms, grouped_terms
FROM sift.segment_postings('t1_idx');
 df_class | terms | postings | inline_terms | list_terms | grouped_terms 
----------+-------+----------+--------------+------------+---------------
 1        |     2 |        2 |            2 |          0 |             0
(1 row)

SELECT sift.verify('t1_idx');
 verify 
--------
 
(1 row)

-- NULLs are not indexed; empty and punctuation-only strings are documents
-- without terms (they count in N), duplicates are separate documents
CREATE TABLE t2 (id int, body text);
INSERT INTO t2 VALUES (1, NULL), (2, ''), (3, '  ...  '), (4, 'same text'),
	(5, 'same text'), (6, 'Same TEXT!'), (7, NULL);
CREATE INDEX t2_idx ON t2 USING sift (body);
SELECT ndocs, sum_dl, nterms FROM sift.segments('t2_idx');
 ndocs | sum_dl | nterms 
-------+--------+--------
     5 |      6 |      2
(1 row)

SELECT sift.verify('t2_idx');
 verify 
--------
 
(1 row)

-- Only documents without terms: a segment with an empty dictionary
CREATE TABLE te (body text);
INSERT INTO te VALUES (''), ('!!!'), (NULL), ('   ');
CREATE INDEX te_idx ON te USING sift (body);
SELECT ndocs, sum_dl, nterms, avgdl_ref FROM sift.segments('te_idx');
 ndocs | sum_dl | nterms | avgdl_ref 
-------+--------+--------+-----------
     3 |      0 |      0 |         0
(1 row)

SELECT count(*) FROM sift.segment_postings('te_idx');
 count 
-------
     0
(1 row)

SELECT sift.verify('te_idx');
 verify 
--------
 
(1 row)

-- UTF-8: accents, CJK, emoji, flags, a 300-byte token (truncated to 255)
CREATE TABLE t3 (body text);
INSERT INTO t3 VALUES ('Crème brûlée à Paris'), ('東京タワー 🗼 and 🇯🇵 flags 👍🏽'),
	('Straße STRASSE Džemal'), (repeat('é', 150)), ('naïve café ☕☕');
CREATE INDEX t3_idx ON t3 USING sift (body);
SELECT ndocs, sum_dl, nterms FROM sift.segments('t3_idx');
 ndocs | sum_dl | nterms 
-------+--------+--------
     5 |     20 |     18
(1 row)

SELECT sift.verify('t3_idx');
 verify 
--------
 
(1 row)

-- Options are stored at build time; changing them needs REINDEX
CREATE INDEX t3_opts ON t3 USING sift (body)
	WITH (k1 = 2.0, b = 0.5, stemmer = 'en', accent_folding = false);
SELECT ndocs, sum_dl, nterms FROM sift.segments('t3_opts');
 ndocs | sum_dl | nterms 
-------+--------+--------
     5 |     20 |     18
(1 row)

SELECT sift.verify('t3_opts');
 verify 
--------
 
(1 row)

ALTER INDEX t3_opts SET (k1 = 1.0);
SELECT sift.verify('t3_opts');
ERROR:  sift index "t3_opts" was built with other options
HINT:  REINDEX the index after changing k1, b or analyzer options.
REINDEX INDEX t3_opts;
SELECT sift.verify('t3_opts');
 verify 
--------
 
(1 row)

-- Partial and expression indexes
CREATE INDEX t2_part ON t2 USING sift (body) WHERE id > 4;
SELECT ndocs, sum_dl, nterms FROM sift.segments('t2_part');
 ndocs | sum_dl | nterms 
-------+--------+--------
     2 |      4 |      2
(1 row)

SELECT sift.verify('t2_part');
 verify 
--------
 
(1 row)

CREATE INDEX t2_expr ON t2 USING sift ((coalesce(body, 'was null') || ' ' || id));
SELECT ndocs, sum_dl, nterms FROM sift.segments('t2_expr');
 ndocs | sum_dl | nterms 
-------+--------+--------
     7 |     17 |     11
(1 row)

SELECT sift.verify('t2_expr');
 verify 
--------
 
(1 row)

-- Several segments from one build (heap ranges), I1 checked by verify
CREATE TABLE t4 (id int, body text);
INSERT INTO t4 SELECT i, 'doc ' || i || ' word' || (i % 7) FROM generate_series(1, 3000) i;
CREATE INDEX t4_idx ON t4 USING sift (body) WITH (build_segments = 3);
SELECT gen, ndocs, sum_dl, nterms, sb_lo, sb_hi FROM sift.segments('t4_idx');
 gen | ndocs | sum_dl | nterms | sb_lo | sb_hi 
-----+-------+--------+--------+-------+-------
   1 |   943 |   2829 |    951 |     0 |     0
   2 |  1099 |   3297 |   1107 |     0 |     0
   3 |   958 |   2874 |    966 |     0 |     0
(3 rows)

SELECT sum(ndocs) = (SELECT count(*) FROM t4) AS all_docs FROM sift.segments('t4_idx');
 all_docs 
----------
 t
(1 row)

SELECT sift.verify('t4_idx');
 verify 
--------
 
(1 row)

-- HOT chains: the build reports root tids, out of order within a page
CREATE TABLE t5 (id int, body text) WITH (fillfactor = 50);
INSERT INTO t5 SELECT i, 'version one of ' || i FROM generate_series(1, 200) i;
UPDATE t5 SET body = 'version two of ' || id WHERE id % 3 = 0;
UPDATE t5 SET body = 'version three of ' || id WHERE id % 5 = 0;
CREATE INDEX t5_idx ON t5 USING sift (body);
SELECT ndocs, sum_dl, nterms FROM sift.segments('t5_idx');
 ndocs | sum_dl | nterms 
-------+--------+--------
   200 |    800 |    205
(1 row)

SELECT sift.verify('t5_idx');
 verify 
--------
 
(1 row)

-- UNLOGGED tables use the init fork for the empty index
CREATE UNLOGGED TABLE tu (body text);
INSERT INTO tu VALUES ('unlogged words'), ('more unlogged words');
CREATE INDEX tu_idx ON tu USING sift (body);
SELECT ndocs, nterms FROM sift.segments('tu_idx');
 ndocs | nterms 
-------+--------
     2 |      3
(1 row)

SELECT sift.verify('tu_idx');
 verify 
--------
 
(1 row)

-- REINDEX rebuilds into a new relfilenode; CONCURRENTLY validates through ambulkdelete
REINDEX TABLE t2;
SELECT sift.verify('t2_idx');
 verify 
--------
 
(1 row)

REINDEX INDEX CONCURRENTLY t4_idx;
SELECT sift.verify('t4_idx', true);
 verify 
--------
 
(1 row)

CREATE INDEX CONCURRENTLY t1_cic ON t1 USING sift (body);
SELECT sift.verify('t1_cic', true);
 verify 
--------
 
(1 row)

DROP INDEX t1_cic;
-- Only heap tables, one column, sift indexes for the functions
CREATE INDEX t2_multi ON t2 USING sift (body, body);
ERROR:  access method "sift" does not support multicolumn indexes
SELECT sift.verify('t2'::regclass);
ERROR:  "t2" is not an index
CREATE INDEX t2_btree ON t2 (id);
SELECT * FROM sift.segments('t2_btree');
ERROR:  "t2_btree" is not a sift index
-- The index and a seq scan agree
SET enable_seqscan = off;
SELECT count(*) FROM t2 WHERE body @@> 'same';
 count 
-------
     3
(1 row)

RESET enable_seqscan;
SELECT count(*) FROM t2 WHERE body @@> 'same';
 count 
-------
     3
(1 row)

DROP TABLE t0, t1, t2, te, t3, t4, t5, tu;
DROP EXTENSION pg_sift;