-- Here's a table to place our test data. I include a "body" column and even a "post_id" column to more simulate a real world scenario
-- Unlogged for speedier insertion of test data
CREATE UNLOGGED TABLE posts (post_id serial, body text, fts_doc tsvector);



-- May take a few minutes to run the following command for generating test data.
-- Pretend the random hashes produced are words. For our test you can query 'c4ca4238a0b923820dcc509a6f75849b' which will almost certainly always be one of the random "words" found in the posts
INSERT INTO posts (body, fts_doc)
SELECT body, to_tsvector(body)
FROM (
    SELECT 
    (
        SELECT string_agg(x.word, ' ')
        FROM (
            SELECT md5(trunc(random() * 100 + 1)::text) AS word -- For random "word" out of set of 100 possible words
            FROM generate_series(1, 10) -- For 10 word posts
            WHERE series.val = series.val -- For sake of ensuring subquery expression is considered volatile and returns different result for each row generated by the outer generate_series
        ) x
    ) AS body
    FROM generate_series(1, 1000000) AS series(val)
) z;



-- Create full-text search index
CREATE INDEX idx_posts_gin_fts_doc ON posts USING gin (fts_doc);



-- Disable sequential scans to ensure the GIN index's bitmap scan is always used
SET enable_seqscan = OFF;



-- Run these SET and EXPLAIN ANALYZE SELECT commands together to observe speed of execution when the soft limit is 10.
-- 'c4ca4238a0b923820dcc509a6f75849b' = (SELECT md5('1') AS word) 
SET gin_fuzzy_search_limit = 10; EXPLAIN ANALYZE SELECT COUNT(*) FROM posts
WHERE fts_doc @@ to_tsquery('c4ca4238a0b923820dcc509a6f75849b');
 
-- Run these SET and EXPLAIN ANALYZE SELECT commands and compare the execution speed to that of the previous pair of commands.
SET gin_fuzzy_search_limit = 10000; EXPLAIN ANALYZE SELECT COUNT(*) FROM
posts WHERE fts_doc @@ to_tsquery('c4ca4238a0b923820dcc509a6f75849b');