-- Disable memoization to see true execution cost on unique-ish data
SET enable_memoize = OFF;
\timing on

-- 1. Setup: Create a dataset where structure matters
CREATE TABLE fts_json_test (id serial PRIMARY KEY, doc jsonb);

-- 1000000 rows.
-- 'performance' is in the TITLE (wrong place) for rows % 10.
-- 'performance' is in a COMMENT (right place) for rows % 20.
INSERT INTO fts_json_test (doc)
SELECT jsonb_build_object(
               'title', CASE WHEN i % 10 = 0 THEN 'database performance optimization' ELSE 'standard database' END,
               'metadata', jsonb_build_object('tags', jsonb_build_array('tech', 'sql')),
               'comments', jsonb_build_array(
                       jsonb_build_object('body', 'this is a comment'),
                       jsonb_build_object('body', CASE WHEN i % 20 = 0 THEN 'i saw a performance gain' ELSE 'no gain' END)
                           )
       )
FROM generate_series(1, 1000000) i;

VACUUM ANALYZE fts_json_test;

--------------------------------------------------------------------------------
-- TEST 1: The New `tsmatch` (Precision + Usability)
--------------------------------------------------------------------------------
SELECT '--- TEST 1: tsmatch (Targeted at $.comments[*].body) ---' as msg;
-- Correct Result: 500 rows
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM fts_json_test
WHERE doc @? '$.comments[*] ? (@.body tsmatch "performance" tsconfig "english")';


--------------------------------------------------------------------------------
-- TEST 2: The Sledgehammer (jsonb_to_tsvector)
--------------------------------------------------------------------------------
SELECT '--- TEST 2: jsonb_to_tsvector (Whole Document) ---' as msg;
-- Result: 15000 rows (INCORRECT - includes titles)
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM fts_json_test
WHERE jsonb_to_tsvector('english', doc, '["all"]') @@ to_tsquery('english', 'performance');


--------------------------------------------------------------------------------
-- TEST 3: The Manual Way (Lateral Join)
--------------------------------------------------------------------------------
SELECT '--- TEST 3: Lateral Join (Precision at high SQL complexity) ---' as msg;
-- Result: 500 rows (Correct, but complex SQL)
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM fts_json_test t,
     jsonb_array_elements(t.doc->'comments') AS c
WHERE to_tsvector('english', c->>'body') @@ to_tsquery('english', 'performance');
