-- Full-text search vs LIKE, DDIA 2e Ch. 4.
-- 5,000,000 short documents from a small vocabulary; ~0.1% contain the rare word
-- 'kestrel'. Find them with a substring scan (ILIKE '%kestrel%', no usable index)
-- and with an inverted index (a tsvector column + GIN index).
--
--   docker cp full_text_search.sql ddia-ch4:/tmp/
--   docker exec ddia-ch4 psql -U postgres -d study -f /tmp/full_text_search.sql

CREATE TABLE docs (id bigint, body text);
INSERT INTO docs
SELECT g, concat_ws(' ',
    (ARRAY['alpha','bravo','charlie','delta','echo','foxtrot','golf','hotel'])[(abs(hashint8(g))    % 8) + 1],
    (ARRAY['india','juliet','kilo','lima','mike','november','oscar','papa'])[(abs(hashint8(g*3)) % 8) + 1],
    (ARRAY['quebec','romeo','sierra','tango','uniform','victor','whiskey','xray'])[(abs(hashint8(g*7)) % 8) + 1],
    (ARRAY['yankee','zulu','lorem','ipsum','dolor','amet','consectetur','elit'])[(abs(hashint8(g*11)) % 8) + 1],
    CASE WHEN abs(hashint8(g)) % 1000 = 0 THEN 'kestrel' ELSE 'sit' END)
FROM generate_series(1, 5000000) g;
ANALYZE docs;

\echo '########## substring scan: WHERE body ILIKE ''%kestrel%'' (no usable index) ##########'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT count(*) FROM docs WHERE body ILIKE '%kestrel%';

\echo '########## inverted index: tsvector + GIN, WHERE body_tsv @@ to_tsquery(''kestrel'') ##########'
ALTER TABLE docs ADD COLUMN body_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', body)) STORED;
CREATE INDEX docs_gin ON docs USING gin (body_tsv);
ANALYZE docs;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT count(*) FROM docs WHERE body_tsv @@ to_tsquery('simple', 'kestrel');
