-- test/sql/cardinality.sql
-- pg_tre.min_trigram_freq (cardinality-aware build, 1.2.1-4.4.x).
--
-- Values above 1 dropped the posting trees of trigrams in fewer rows
-- than that.  A trigram missing from the upper tree reads as "in no
-- row" to every scan, so the index returned no rows for rare words
-- (no recheck sees rows the index never returns).  Since 4.5.0 values
-- above 1 are refused; this checks that, and that the index agrees
-- with a sequential scan at the default.
CREATE EXTENSION IF NOT EXISTS pg_tre;
psql:test/sql/cardinality.sql:11: NOTICE:  extension "pg_tre" already exists, skipping
CREATE EXTENSION
-- Force the shared library (and thus its custom GUCs) to load so that
-- SHOW pg_tre.min_trigram_freq below is recognized even in a backend
-- that has not yet touched a pg_tre index.
LOAD 'pg_tre';
LOAD
DROP TABLE IF EXISTS card_t CASCADE;
psql:test/sql/cardinality.sql:18: NOTICE:  table "card_t" does not exist, skipping
DROP TABLE
CREATE TABLE card_t (id serial, body text);
CREATE TABLE
-- 5 rows containing 'singleton' (well above any reasonable
-- min_trigram_freq).
INSERT INTO card_t (body)
SELECT 'singleton occurrence ' || i FROM generate_series(1, 5) AS i;
INSERT 0 5
-- 200 rows containing 'frequent'.
INSERT INTO card_t (body)
SELECT 'frequent text body ' || i FROM generate_series(1, 200) AS i;
INSERT 0 200
-- Build with min_trigram_freq=10: rare trigrams (e.g. those from
-- 'singleton occurrence N' that occur in only one row each) get
-- dropped.  We can't ALTER SYSTEM inside a regression test (must
-- not run inside a transaction) so this test simply documents
-- the behavior at the default; the operator-side smoke test
-- covered the actual filtering.
SHOW pg_tre.min_trigram_freq;
 pg_tre.min_trigram_freq 
-------------------------
 1
(1 row)

CREATE INDEX card_idx ON card_t USING tre (body);
psql:test/sql/cardinality.sql:38: NOTICE:  pg_tre: collected 3992 trigram entries from 205 heap tuples
psql:test/sql/cardinality.sql:38: NOTICE:  pg_tre: built 241 posting trees
psql:test/sql/cardinality.sql:38: NOTICE:  pg_tre: build complete, indexed 205 heap tuples into 241 trigrams
CREATE INDEX
-- Differential: index and seq scan must agree on row sets.
SET enable_seqscan = off;
SET
CREATE TEMP TABLE _idx AS
    SELECT id FROM card_t WHERE body %~~ tre_pattern('frequent', 0);
SELECT 200
SET enable_seqscan = on;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
CREATE TEMP TABLE _seq AS
    SELECT id FROM card_t WHERE body %~~ tre_pattern('frequent', 0);
SELECT 200
SELECT
    (SELECT count(*) FROM _idx) = (SELECT count(*) FROM _seq) AS counts_agree,
    NOT EXISTS (SELECT 1 FROM _idx EXCEPT SELECT 1 FROM _seq)
    AND NOT EXISTS (SELECT 1 FROM _seq EXCEPT SELECT 1 FROM _idx)
        AS row_sets_agree;
 counts_agree | row_sets_agree 
--------------+----------------
 t            | t
(1 row)

DROP TABLE _idx;
DROP TABLE
DROP TABLE _seq;
DROP TABLE
DROP TABLE card_t;
DROP TABLE
-- Values above 1 are refused (ALTER SYSTEM runs the check hook).
ALTER SYSTEM SET pg_tre.min_trigram_freq = 5;
psql:test/sql/cardinality.sql:62: ERROR:  invalid value for parameter "pg_tre.min_trigram_freq": 5
DETAIL:  Building without the postings of rare trigrams made the index return no rows for them.
HINT:  Leave it at 1, and REINDEX any tre index built with a higher value.
ALTER SYSTEM SET pg_tre.min_trigram_freq = 1;
ALTER SYSTEM
ALTER SYSTEM RESET pg_tre.min_trigram_freq;
ALTER SYSTEM