/* * --------------------------------------------- * NOTE: This test behaves differenly on PgPro * --------------------------------------------- */ \set VERBOSITY terse SET search_path = 'public'; CREATE EXTENSION pg_pathman; CREATE SCHEMA test_only; /* 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 --------------------------------------------- Hash Join Hash Cond: (q1.val = q2.val) CTE q1 -> 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 CTE q2 -> Seq Scan on from_only_test -> CTE Scan on q1 -> Hash -> CTE Scan on q2 (19 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 CTE q1 -> Seq Scan on from_only_test from_only_test_1 -> CTE Scan on q1 -> Custom Scan (RuntimeAppend) Prune by: (q1.val = from_only_test.val) -> Seq Scan on from_only_test_1 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_2 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_3 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_4 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_5 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_6 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_7 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_8 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_9 from_only_test Filter: (q1.val = val) -> Seq Scan on from_only_test_10 from_only_test Filter: (q1.val = val) (26 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 SCHEMA test_only CASCADE; NOTICE: drop cascades to 12 other objects DROP EXTENSION pg_pathman;