pathman_only_1.out

/*
 * ---------------------------------------------
 *  NOTE: This test behaves differenly on PgPro
 * ---------------------------------------------
 *
 * --------------------
 *  pathman_only_1.sql
 * --------------------
 * Since 608b167f9f in PostgreSQL 12, CTEs which are scanned once are no longer
 * an optimization fence, which changes practically all plans here. There is
 * an option to forcibly make them MATERIALIZED, but we also need to run tests
 * on older versions, so create pathman_only_1.out instead.
 *
 * --------------------
 *  pathman_only_2.sql
 * --------------------
 * Since 55a1954da16 and 6ef77cf46e8 in PostgreSQL 13, output of EXPLAIN was
 * changed, now it includes aliases for inherited tables.
 *
 * --------------------
 *  pathman_only_3.sql
 * --------------------
 * Since a5fc46414de in PostgreSQL 16, the order of the operands was changed,
 * which affected the output of the "Prune by" in EXPLAIN.
 *
 * --------------------
 *  pathman_only_4.sql
 * --------------------
 * Since fd0398fcb09 in PostgreSQL 17, output of EXPLAIN was
 * changed, now it displays SubPlan nodes and output parameters.
 */
\set VERBOSITY terse
SET search_path = 'public';
CREATE EXTENSION pg_pathman;
CREATE SCHEMA test_only;
-- Prevent Ent-specific changes in query plans. Equivalent to
-- "SET enable_extra_transformations = off" but output is
-- edition-independent.
SELECT count(*) >= 0 AS success
FROM (
	SELECT set_config(name, 'off', false) FROM pg_settings
	WHERE name = 'enable_extra_transformations'
) tmp;
 success 
---------
 t
(1 row)

/* Test special case: ONLY statement with not-ONLY for partitioned table */
CREATE TABLE test_only.from_only_test(val INT NOT NULL);
INSERT INTO test_only.from_only_test SELECT generate_series(1, 20);
SELECT create_range_partitions('test_only.from_only_test', 'val', 1, 2);
 create_range_partitions 
-------------------------
                      10
(1 row)

VACUUM ANALYZE;
/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM ONLY test_only.from_only_test
UNION SELECT * FROM test_only.from_only_test;
                   QUERY PLAN                    
-------------------------------------------------
 HashAggregate
   Group Key: from_only_test.val
   ->  Append
         ->  Seq Scan on from_only_test
         ->  Append
               ->  Seq Scan on from_only_test_1
               ->  Seq Scan on from_only_test_2
               ->  Seq Scan on from_only_test_3
               ->  Seq Scan on from_only_test_4
               ->  Seq Scan on from_only_test_5
               ->  Seq Scan on from_only_test_6
               ->  Seq Scan on from_only_test_7
               ->  Seq Scan on from_only_test_8
               ->  Seq Scan on from_only_test_9
               ->  Seq Scan on from_only_test_10
(15 rows)

/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM test_only.from_only_test
UNION SELECT * FROM ONLY test_only.from_only_test;
                   QUERY PLAN                    
-------------------------------------------------
 HashAggregate
   Group Key: from_only_test_1.val
   ->  Append
         ->  Append
               ->  Seq Scan on from_only_test_1
               ->  Seq Scan on from_only_test_2
               ->  Seq Scan on from_only_test_3
               ->  Seq Scan on from_only_test_4
               ->  Seq Scan on from_only_test_5
               ->  Seq Scan on from_only_test_6
               ->  Seq Scan on from_only_test_7
               ->  Seq Scan on from_only_test_8
               ->  Seq Scan on from_only_test_9
               ->  Seq Scan on from_only_test_10
         ->  Seq Scan on from_only_test
(15 rows)

/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM test_only.from_only_test
UNION SELECT * FROM test_only.from_only_test
UNION SELECT * FROM ONLY test_only.from_only_test;
                             QUERY PLAN                              
---------------------------------------------------------------------
 HashAggregate
   Group Key: from_only_test_1.val
   ->  Append
         ->  Append
               ->  Seq Scan on from_only_test_1
               ->  Seq Scan on from_only_test_2
               ->  Seq Scan on from_only_test_3
               ->  Seq Scan on from_only_test_4
               ->  Seq Scan on from_only_test_5
               ->  Seq Scan on from_only_test_6
               ->  Seq Scan on from_only_test_7
               ->  Seq Scan on from_only_test_8
               ->  Seq Scan on from_only_test_9
               ->  Seq Scan on from_only_test_10
         ->  Append
               ->  Seq Scan on from_only_test_1 from_only_test_1_1
               ->  Seq Scan on from_only_test_2 from_only_test_2_1
               ->  Seq Scan on from_only_test_3 from_only_test_3_1
               ->  Seq Scan on from_only_test_4 from_only_test_4_1
               ->  Seq Scan on from_only_test_5 from_only_test_5_1
               ->  Seq Scan on from_only_test_6 from_only_test_6_1
               ->  Seq Scan on from_only_test_7 from_only_test_7_1
               ->  Seq Scan on from_only_test_8 from_only_test_8_1
               ->  Seq Scan on from_only_test_9 from_only_test_9_1
               ->  Seq Scan on from_only_test_10 from_only_test_10_1
         ->  Seq Scan on from_only_test
(26 rows)

/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM ONLY test_only.from_only_test
UNION SELECT * FROM test_only.from_only_test
UNION SELECT * FROM test_only.from_only_test;
                             QUERY PLAN                              
---------------------------------------------------------------------
 HashAggregate
   Group Key: from_only_test.val
   ->  Append
         ->  Seq Scan on from_only_test
         ->  Append
               ->  Seq Scan on from_only_test_1
               ->  Seq Scan on from_only_test_2
               ->  Seq Scan on from_only_test_3
               ->  Seq Scan on from_only_test_4
               ->  Seq Scan on from_only_test_5
               ->  Seq Scan on from_only_test_6
               ->  Seq Scan on from_only_test_7
               ->  Seq Scan on from_only_test_8
               ->  Seq Scan on from_only_test_9
               ->  Seq Scan on from_only_test_10
         ->  Append
               ->  Seq Scan on from_only_test_1 from_only_test_1_1
               ->  Seq Scan on from_only_test_2 from_only_test_2_1
               ->  Seq Scan on from_only_test_3 from_only_test_3_1
               ->  Seq Scan on from_only_test_4 from_only_test_4_1
               ->  Seq Scan on from_only_test_5 from_only_test_5_1
               ->  Seq Scan on from_only_test_6 from_only_test_6_1
               ->  Seq Scan on from_only_test_7 from_only_test_7_1
               ->  Seq Scan on from_only_test_8 from_only_test_8_1
               ->  Seq Scan on from_only_test_9 from_only_test_9_1
               ->  Seq Scan on from_only_test_10 from_only_test_10_1
(26 rows)

/* not ok, ONLY|non-ONLY in one query (this is not the case for PgPro) */
EXPLAIN (COSTS OFF)
SELECT * FROM test_only.from_only_test a
JOIN ONLY test_only.from_only_test b USING(val);
                 QUERY PLAN                  
---------------------------------------------
 Nested Loop
   ->  Seq Scan on from_only_test b
   ->  Custom Scan (RuntimeAppend)
         Prune by: (b.val = a.val)
         ->  Seq Scan on from_only_test_1 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_2 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_3 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_4 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_5 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_6 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_7 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_8 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_9 a
               Filter: (b.val = val)
         ->  Seq Scan on from_only_test_10 a
               Filter: (b.val = val)
(24 rows)

/* should be OK */
EXPLAIN (COSTS OFF)
WITH q1 AS (SELECT * FROM test_only.from_only_test),
	 q2 AS (SELECT * FROM ONLY test_only.from_only_test)
SELECT * FROM q1 JOIN q2 USING(val);
                          QUERY PLAN                           
---------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on from_only_test from_only_test_1
   ->  Custom Scan (RuntimeAppend)
         Prune by: (from_only_test_1.val = from_only_test.val)
         ->  Seq Scan on from_only_test_1 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_2 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_3 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_4 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_5 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_6 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_7 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_8 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_9 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_10 from_only_test
               Filter: (from_only_test_1.val = val)
(24 rows)

/* should be OK */
EXPLAIN (COSTS OFF)
WITH q1 AS (SELECT * FROM ONLY test_only.from_only_test)
SELECT * FROM test_only.from_only_test JOIN q1 USING(val);
                          QUERY PLAN                           
---------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on from_only_test from_only_test_1
   ->  Custom Scan (RuntimeAppend)
         Prune by: (from_only_test_1.val = from_only_test.val)
         ->  Seq Scan on from_only_test_1 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_2 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_3 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_4 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_5 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_6 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_7 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_8 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_9 from_only_test
               Filter: (from_only_test_1.val = val)
         ->  Seq Scan on from_only_test_10 from_only_test
               Filter: (from_only_test_1.val = val)
(24 rows)

/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM test_only.from_only_test
WHERE val = (SELECT val FROM ONLY test_only.from_only_test
			 ORDER BY val ASC
			 LIMIT 1);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Custom Scan (RuntimeAppend)
   Prune by: (from_only_test.val = $0)
   InitPlan 1 (returns $0)
     ->  Limit
           ->  Sort
                 Sort Key: from_only_test_1.val
                 ->  Seq Scan on from_only_test from_only_test_1
   ->  Seq Scan on from_only_test_1 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_2 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_3 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_4 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_5 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_6 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_7 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_8 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_9 from_only_test
         Filter: (val = $0)
   ->  Seq Scan on from_only_test_10 from_only_test
         Filter: (val = $0)
(27 rows)

DROP TABLE test_only.from_only_test CASCADE;
NOTICE:  drop cascades to 11 other objects
DROP SCHEMA test_only;
DROP EXTENSION pg_pathman;
-- RESET enable_extra_transformations
SELECT count(*) >= 0 AS success
FROM (
	SELECT set_config(name, NULL, false) FROM pg_settings
	WHERE name = 'enable_extra_transformations'
) tmp;
 success 
---------
 t
(1 row)