-- test/sql/multi_leaf.sql
-- Regression test for multi-leaf posting trees (Phase 4.2).
--
-- Objective: Force a trigram to exceed the single-leaf posting budget
-- (~8 KB) so the build splits it across multiple leaves linked by
-- right_link.  Verify that index scans return the same rows as seq-scans.
-- Load the extension (requires preload).
CREATE EXTENSION IF NOT EXISTS pg_tre;
psql:test/sql/multi_leaf.sql:9: NOTICE:  extension "pg_tre" already exists, skipping
CREATE EXTENSION
-- Setup: generate 100K rows where the trigram 'the' appears in every row.
-- At ~8 bytes per TID after sparsemap compression, 100K TIDs = ~800 KB,
-- far exceeding the ~8 KB single-leaf budget.
DROP TABLE IF EXISTS multi_leaf_test CASCADE;
psql:test/sql/multi_leaf.sql:14: NOTICE:  table "multi_leaf_test" does not exist, skipping
DROP TABLE
CREATE TABLE multi_leaf_test (id serial PRIMARY KEY, body text);
CREATE TABLE
-- Insert 100K rows with 'the' trigram present in every row.
INSERT INTO multi_leaf_test (body)
SELECT 'The quick brown fox jumps over the lazy dog. Row ' || i
FROM generate_series(1, 100000) AS i;
INSERT 0 100000
-- Create pg_tre index (should split 'the' posting across multiple leaves).
-- Suppress the version-varying build-progress NOTICEs (trigram/posting
-- counts) here and for the later REINDEX so the expected file is stable
-- across PG majors.
SET client_min_messages = warning;
SET
CREATE INDEX multi_leaf_idx ON multi_leaf_test USING tre (body);
CREATE INDEX
RESET client_min_messages;
RESET
-- Verify index built successfully (no errcode_program_limit_exceeded).
SELECT pg_relation_size('multi_leaf_idx') > 0 AS index_exists;
 index_exists 
--------------
 t
(1 row)

-- Plan-shape probe: assert the forced scan is what we expect, via a
-- single text token (no exact plan-shape dump, which differs across
-- PG majors).
CREATE FUNCTION ml_uses_index(q text) RETURNS boolean
LANGUAGE plpgsql AS $$
DECLARE r record; txt text;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (FORMAT JSON) ' || q LOOP
    txt := r."QUERY PLAN"::text;
  END LOOP;
  -- Scan the whole plan tree (robust to Aggregate/Gather wrapping and
  -- parallel plans) for an index or bitmap scan node.
  RETURN txt ILIKE '%Index Scan%' OR txt ILIKE '%Bitmap%Scan%';
END $$;
CREATE FUNCTION
-- Differential test 1: exact match for 'the' (should return all 100K rows).
-- Compare index scan vs seq-scan.
SET enable_seqscan = off;
SET
SELECT CASE WHEN ml_uses_index($$SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0)$$)
            THEN 'idx_forced_uses_index' ELSE 'idx_forced_seq' END AS plan1;
         plan1         
-----------------------
 idx_forced_uses_index
(1 row)

SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0);
 count  
--------
 100000
(1 row)

SET enable_seqscan = on;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
SELECT CASE WHEN ml_uses_index($$SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0)$$)
            THEN 'seq_forced_uses_index' ELSE 'seq_forced_seq' END AS plan2;
     plan2      
----------------
 seq_forced_seq
(1 row)

SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0);
 count  
--------
 100000
(1 row)

SET enable_indexscan = on;
SET
SET enable_bitmapscan = on;
SET
-- Differential test 2: pattern that matches a subset of rows.
SET enable_seqscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('Row 12[0-9][0-9][0-9]', 0);
 count 
-------
  1000
(1 row)

SET enable_seqscan = on;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('Row 12[0-9][0-9][0-9]', 0);
 count 
-------
  1000
(1 row)

SET enable_indexscan = on;
SET
SET enable_bitmapscan = on;
SET
-- Differential test 3: approximate match (k=1).
SET enable_seqscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('quik', 1);  -- should match 'quick'
 count  
--------
 100000
(1 row)

SET enable_seqscan = on;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('quik', 1);
 count  
--------
 100000
(1 row)

SET enable_indexscan = on;
SET
SET enable_bitmapscan = on;
SET
-- Differential test 4: non-matching pattern (should return 0 rows).
SET enable_seqscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('xyzabc', 0);
 count 
-------
     0
(1 row)

SET enable_seqscan = on;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('xyzabc', 0);
 count 
-------
     0
(1 row)

-- Reset planner settings.
SET enable_indexscan = on;
SET
SET enable_bitmapscan = on;
SET
SET enable_seqscan = on;
SET
-- Differential equivalence check: index scan and seq-scan must agree on
-- every row.  This catches regressions like the multi-leaf right-link
-- chain bug where index scans silently returned a subset of the heap
-- match-set.  We compare the full id sets, not just COUNT(*), so a
-- spurious-match regression also fails.
SET enable_seqscan = off;
SET
CREATE TEMP TABLE multi_leaf_idx_ids AS
    SELECT id FROM multi_leaf_test
    WHERE body %~~ tre_pattern('the', 0);
SELECT 100000
SET enable_seqscan = on;
SET
SET enable_indexscan = off;
SET
SET enable_bitmapscan = off;
SET
CREATE TEMP TABLE multi_leaf_seq_ids AS
    SELECT id FROM multi_leaf_test
    WHERE body %~~ tre_pattern('the', 0);
SELECT 100000
SET enable_indexscan = on;
SET
SET enable_bitmapscan = on;
SET
SELECT
    (SELECT COUNT(*) FROM multi_leaf_idx_ids) AS idx_rows,
    (SELECT COUNT(*) FROM multi_leaf_seq_ids) AS seq_rows,
    (SELECT COUNT(*) FROM multi_leaf_idx_ids) =
        (SELECT COUNT(*) FROM multi_leaf_seq_ids)
    AS counts_agree,
    NOT EXISTS (
        SELECT 1 FROM multi_leaf_idx_ids EXCEPT
        SELECT 1 FROM multi_leaf_seq_ids
    )
    AND NOT EXISTS (
        SELECT 1 FROM multi_leaf_seq_ids EXCEPT
        SELECT 1 FROM multi_leaf_idx_ids
    ) AS row_sets_agree;
 idx_rows | seq_rows | counts_agree | row_sets_agree 
----------+----------+--------------+----------------
   100000 |   100000 | t            | t
(1 row)

DROP TABLE multi_leaf_idx_ids;
DROP TABLE
DROP TABLE multi_leaf_seq_ids;
DROP TABLE
-- REINDEX test: drop and rebuild the index to verify multi-leaf rebuild.
-- Suppress the version-varying rebuild NOTICEs too.
SET client_min_messages = warning;
SET
REINDEX INDEX multi_leaf_idx;
REINDEX
RESET client_min_messages;
RESET
-- Clean up the plan-shape probe helper.
DROP FUNCTION ml_uses_index(text);
DROP FUNCTION
-- Verify rebuilt index still works correctly.
SET enable_seqscan = off;
SET
SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0);
 count  
--------
 100000
(1 row)

SET enable_seqscan = on;
SET
-- Cleanup.
DROP INDEX multi_leaf_idx;
DROP INDEX
DROP TABLE multi_leaf_test;
DROP TABLE