SET enable_memoize = OFF;
SET
Timing is on.
DROP TABLE IF EXISTS fts_json_test;
DROP TABLE
Time: 1074.932 ms (00:01.075)
CREATE TABLE fts_json_test (id serial PRIMARY KEY, doc jsonb);
CREATE TABLE
Time: 8.082 ms
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;
INSERT 0 1000000
Time: 5153.778 ms (00:05.154)
VACUUM ANALYZE fts_json_test;
VACUUM
Time: 715.258 ms
SELECT '--- TEST 1: tsmatch (Targeted at $.comments[*].body) ---' as msg;
                           msg                            
----------------------------------------------------------
 --- TEST 1: tsmatch (Targeted at $.comments[*].body) ---
(1 row)

Time: 0.121 ms
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM fts_json_test
WHERE doc @? '$.comments[*] ? (@.body tsmatch "performance" tsconfig "english")';
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=32577.05..32577.06 rows=1 width=8) (actual time=1323.511..1324.508 rows=1.00 loops=1)
   Buffers: shared hit=14928 read=11598 written=97
   ->  Gather  (cost=32576.83..32577.04 rows=2 width=8) (actual time=1323.413..1324.504 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=14928 read=11598 written=97
         ->  Partial Aggregate  (cost=31576.83..31576.84 rows=1 width=8) (actual time=1316.188..1316.188 rows=1.00 loops=3)
               Buffers: shared hit=14928 read=11598 written=97
               ->  Parallel Seq Scan on fts_json_test  (cost=0.00..31524.33 rows=21000 width=0) (actual time=0.810..1315.379 rows=16666.67 loops=3)
                     Filter: (doc @? '$."comments"[*]?(@."body" tsmatch "performance" tsconfig "english")'::jsonpath)
                     Rows Removed by Filter: 316667
                     Buffers: shared hit=14928 read=11598 written=97
 Planning:
   Buffers: shared hit=19 read=24
 Planning Time: 5.292 ms
 Execution Time: 1324.540 ms
(16 rows)

Time: 1334.416 ms (00:01.334)
SELECT '--- TEST 2: jsonb_to_tsvector (Whole Document) ---' as msg;
                        msg                         
----------------------------------------------------
 --- TEST 2: jsonb_to_tsvector (Whole Document) ---
(1 row)

Time: 0.073 ms
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM fts_json_test
WHERE jsonb_to_tsvector('english', doc, '["all"]') @@ to_tsquery('english', 'performance');
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=136696.42..136696.43 rows=1 width=8) (actual time=2112.097..2112.726 rows=1.00 loops=1)
   Buffers: shared hit=14938 read=11578 written=1
   ->  Gather  (cost=136696.21..136696.42 rows=2 width=8) (actual time=2112.093..2112.724 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=14938 read=11578 written=1
         ->  Partial Aggregate  (cost=135696.21..135696.22 rows=1 width=8) (actual time=2110.459..2110.460 rows=1.00 loops=3)
               Buffers: shared hit=14938 read=11578 written=1
               ->  Parallel Seq Scan on fts_json_test  (cost=0.00..135691.00 rows=2083 width=0) (actual time=0.229..2109.659 rows=33333.33 loops=3)
                     Filter: (jsonb_to_tsvector('english'::regconfig, doc, '["all"]'::jsonb) @@ '''perform'''::tsquery)
                     Rows Removed by Filter: 300000
                     Buffers: shared hit=14938 read=11578 written=1
 Planning Time: 0.032 ms
 Execution Time: 2112.757 ms
(14 rows)

Time: 2113.625 ms (00:02.114)
SELECT '--- TEST 3: Lateral Join (Precision at high SQL complexity) ---' as msg;
                               msg                               
-----------------------------------------------------------------
 --- TEST 3: Lateral Join (Precision at high SQL complexity) ---
(1 row)

Time: 0.079 ms
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');
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11078366.72..11078366.73 rows=1 width=8) (actual time=1054.605..1055.495 rows=1.00 loops=1)
   Buffers: shared hit=14946 read=11570 written=137
   ->  Gather  (cost=11078366.51..11078366.72 rows=2 width=8) (actual time=1054.597..1055.492 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=14946 read=11570 written=137
         ->  Partial Aggregate  (cost=11077366.51..11077366.52 rows=1 width=8) (actual time=1053.148..1053.148 rows=1.00 loops=3)
               Buffers: shared hit=14946 read=11570 written=137
               ->  Nested Loop  (cost=0.01..11076324.84 rows=416667 width=0) (actual time=0.235..1052.641 rows=16666.67 loops=3)
                     Buffers: shared hit=14946 read=11570 written=137
                     ->  Parallel Seq Scan on fts_json_test t  (cost=0.00..30482.67 rows=416667 width=181) (actual time=0.058..14.846 rows=333333.33 loops=3)
                           Buffers: shared hit=14746 read=11570 written=137
                     ->  Function Scan on jsonb_array_elements c  (cost=0.01..26.50 rows=1 width=0) (actual time=0.003..0.003 rows=0.05 loops=1000000)
                           Filter: (to_tsvector('english'::regconfig, (value ->> 'body'::text)) @@ '''perform'''::tsquery)
                           Rows Removed by Filter: 2
                           Buffers: shared hit=200
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.057 ms
 Execution Time: 1055.518 ms
(20 rows)

Time: 1057.057 ms (00:01.057)