CREATE EXTENSION pg_track_optimizer;
-- Cleanup history
SELECT * FROM pg_track_optimizer_reset();
 pg_track_optimizer_reset 
--------------------------
                        1
(1 row)

SET pg_track_optimizer.mode = 'forced';
/*
 * Test Subplan evaluation inside JOIN clause.
 * The Subplan should appear in the second part of an AND clause
 * with number of loops > 10.
 */
-- Create test tables
CREATE TABLE outer_table (
  id       INTEGER,
  category INTEGER,
  val      INTEGER
);
CREATE TABLE inner_table (
  id  INTEGER,
  val INTEGER
);
CREATE TABLE reference_table (
  category  INTEGER,
  threshold INTEGER
);
-- Insert data to ensure > 10 loops
-- outer_table: 20 rows across 5 categories
INSERT INTO outer_table
SELECT i, (i % 5) + 1, i * 10
FROM generate_series(1, 20) i;
-- inner_table: matching ids
INSERT INTO inner_table
SELECT i, i * 5
FROM generate_series(1, 20) i;
-- reference_table: thresholds for each category
INSERT INTO reference_table VALUES
  (1, 50), (2, 100), (3, 150), (4, 200), (5, 250);
VACUUM ANALYZE outer_table, inner_table, reference_table;
-- Force nested loop to ensure the SubPlan executes multiple times
SET enable_hashjoin = off;
SET enable_mergejoin = off;
-- Just to avoid memory consumption report
SET enable_material = 'off';
/*
 * This query demonstrates:
 * 1. JOIN with two-part AND condition
 * 2. Second part contains a correlated subquery (becomes SubPlan)
 * 3. SubPlan evaluates multiple times for each outer row that passes first
 * condition
 */
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT o.id, o.category, o.val, i.val as inner_val
FROM outer_table o
JOIN inner_table i ON
  o.id = i.id OR
  o.val > (
    SELECT threshold
    FROM reference_table r
    WHERE r.category = o.category
  );
                             QUERY PLAN                             
--------------------------------------------------------------------
 Nested Loop (actual rows=134.00 loops=1)
   Join Filter: ((o.id = i.id) OR (o.val > (SubPlan expr_1)))
   Rows Removed by Join Filter: 266
   ->  Seq Scan on outer_table o (actual rows=20.00 loops=1)
   ->  Seq Scan on inner_table i (actual rows=20.00 loops=20)
   SubPlan expr_1
     ->  Seq Scan on reference_table r (actual rows=1.00 loops=380)
           Filter: (category = o.category)
           Rows Removed by Filter: 4
(9 rows)

-- Verify the SubPlan executed multiple times
-- The plan should show "SubPlan" with loops > 10
SELECT
  (avg_error -> 'mean')::numeric > 0. AS err_non_zero,
  (f_join_filter -> 'mean')::numeric > 0. AS jf_non_zero,
  (f_scan_filter -> 'mean')::numeric > 0. AS sf_non_zero,
  (f_worst_splan -> 'mean')::numeric > 0. AS spf_non_zero,
  evaluated_nodes,
  plan_nodes,
  nexecs
FROM pg_track_optimizer()
WHERE query LIKE '%FROM outer_table%';
 err_non_zero | jf_non_zero | sf_non_zero | spf_non_zero | evaluated_nodes | plan_nodes | nexecs 
--------------+-------------+-------------+--------------+-----------------+------------+--------
 t            | t           | t           | t            |               4 |          4 |      1
(1 row)

/*
 * Don't care for now about parallel query plan for now - upstream doesn't
 * support this feature yet. But this test should fail if they implement it and
 * we would have to fix it too.
 */
RESET enable_hashjoin;
RESET enable_mergejoin;
ALTER TABLE outer_table SET (parallel_workers = 4);
ALTER TABLE inner_table SET (parallel_workers = 4);
ALTER TABLE reference_table SET (parallel_workers = 4);
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 0.0001;
SET parallel_tuple_cost = 0.0000001;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT o.id, o.category, o.val, i.val as inner_val
FROM outer_table o
JOIN inner_table i ON
  o.id = i.id OR
  o.val > (
    SELECT threshold
    FROM reference_table r
    WHERE r.category = o.category
  );
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Nested Loop (actual rows=134.00 loops=1)
   Join Filter: ((o.id = i.id) OR (o.val > (SubPlan expr_1)))
   Rows Removed by Join Filter: 266
   ->  Seq Scan on outer_table o (actual rows=20.00 loops=1)
   ->  Gather (actual rows=20.00 loops=20)
         Workers Planned: 4
         Workers Launched: 4
         ->  Parallel Seq Scan on inner_table i (actual rows=4.00 loops=100)
   SubPlan expr_1
     ->  Seq Scan on reference_table r (actual rows=1.00 loops=380)
           Filter: (category = o.category)
           Rows Removed by Filter: 4
(12 rows)

RESET max_parallel_workers_per_gather;
RESET parallel_setup_cost;
RESET parallel_tuple_cost;
RESET min_parallel_table_scan_size;
RESET min_parallel_index_scan_size;
RESET enable_material;
-- Cleanup
DROP TABLE outer_table, inner_table, reference_table;
DROP EXTENSION pg_track_optimizer;