-- pg_stat_user_indexes visibility: every query path that reads the bm25 index
-- to answer a query must register an index scan (idx_scan) and the index entries
-- it produced (idx_tup_read).  For each path we reset stats, force the plan,
-- run one query, flush, and read the counters.  The dataset is fixed so the
-- counts are deterministic: 'quick & fox' matches 'quick brown fox' and
-- 'quick fox runs' -- exactly half of the 4000 rows = 2000.
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE ss (id int, body text);
INSERT INTO ss SELECT g, (ARRAY['quick brown fox','lazy dog','quick fox runs','brown bear'])[1+g%4]
  FROM generate_series(1, 4000) g;
CREATE INDEX ss_fts ON ss USING fts (to_ftsdoc(body));
-- 1) Bitmap Index Scan (the common @@@ path); idx_tup_read comes from index_getbitmap
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

SET enable_seqscan = off;
SET enable_bitmapscan = on;
SET enable_indexscan = on;
EXPLAIN (COSTS OFF) SELECT count(*) FROM (SELECT id FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox')) q;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on ss
         Recheck Cond: (to_ftsdoc(body) @@@ '(''quick'' & ''fox'')'::ftsquery)
         ->  Bitmap Index Scan on ss_fts
               Index Cond: (to_ftsdoc(body) @@@ '(''quick'' & ''fox'')'::ftsquery)
(5 rows)

SELECT count(*) FROM (SELECT id FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox')) q;
 count 
-------
  2000
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan | idx_tup_read 
----------+--------------
        1 |         2000
(1 row)

-- 2) Plain Index Scan (@@@ with bitmap disabled); idx_tup_read from index_getnext_tid
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

SET enable_bitmapscan = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM (SELECT id FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox')) q;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Aggregate
   ->  Index Scan using ss_fts on ss
         Index Cond: (to_ftsdoc(body) @@@ '(''quick'' & ''fox'')'::ftsquery)
(3 rows)

SELECT count(*) FROM (SELECT id FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox')) q;
 count 
-------
  2000
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan | idx_tup_read 
----------+--------------
        1 |         2000
(1 row)

RESET enable_bitmapscan;
-- 3) count(*) pushdown (Custom Scan FtsCount)
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

EXPLAIN (COSTS OFF) SELECT count(*) FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox');
       QUERY PLAN       
------------------------
 Custom Scan (FtsCount)
(1 row)

SELECT count(*) FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox');
 count 
-------
  2000
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan | idx_tup_read 
----------+--------------
        1 |         2000
(1 row)

-- 4) fts_search() native top-k (k=10 -> 10 index entries returned)
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

SELECT count(*) FROM fts_search('ss_fts', to_ftsquery('quick & fox'), 10);
 count 
-------
    10
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan | idx_tup_read 
----------+--------------
        1 |           10
(1 row)

-- 5) fts_count() native count
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

SELECT fts_count('ss_fts', to_ftsquery('quick & fox'));
 fts_count 
-----------
      2000
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan | idx_tup_read 
----------+--------------
        1 |         2000
(1 row)

-- Restore planner settings for the ranked cases below (default costing lets the
-- ordered index scan win; enabling seq scan also avoids the version-specific
-- "Disabled:" EXPLAIN annotation on PG18+ for the bare-ORDER-BY case).
RESET enable_seqscan;
RESET enable_bitmapscan;
RESET enable_indexscan;
-- 6) Ranked index-ordering scan: a WHERE @@@ restricts to the match set and
--    ORDER BY <=> is served in score order straight from the index (the
--    bm25_gettuple ranked path).  LIMIT 5 -> 5 index entries returned.
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

EXPLAIN (COSTS OFF)
  SELECT id FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox')
  ORDER BY to_ftsdoc(body) <=> to_ftsquery('quick & fox') LIMIT 5;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Limit
   ->  Index Scan using ss_fts on ss
         Index Cond: (to_ftsdoc(body) @@@ '(''quick'' & ''fox'')'::ftsquery)
         Order By: (to_ftsdoc(body) <=> '(''quick'' & ''fox'')'::ftsquery)
(4 rows)

SELECT count(*) FROM (
  SELECT id FROM ss WHERE to_ftsdoc(body) @@@ to_ftsquery('quick & fox')
  ORDER BY to_ftsdoc(body) <=> to_ftsquery('quick & fox') LIMIT 5) q;
 count 
-------
     5
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan | idx_tup_read 
----------+--------------
        1 |            5
(1 row)

-- 7) A bare ORDER BY <=> with no @@@ filter cannot use the index: ranking the
--    whole corpus would also need the non-matching documents (all at maximum
--    distance), which the posting lists do not carry -- so it is a Sort over a
--    Seq Scan and idx_scan correctly stays 0.
SELECT pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 row)

EXPLAIN (COSTS OFF) SELECT id FROM ss ORDER BY to_ftsdoc(body) <=> to_ftsquery('quick fox') LIMIT 5;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((to_ftsdoc(body) <=> '(''quick'' & ''fox'')'::ftsquery))
         ->  Seq Scan on ss
(4 rows)

SELECT count(*) FROM (SELECT id FROM ss ORDER BY to_ftsdoc(body) <=> to_ftsquery('quick fox') LIMIT 5) q;
 count 
-------
     5
(1 row)

SELECT pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

SELECT idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'ss_fts';
 idx_scan 
----------
        0
(1 row)

DROP TABLE ss;