-
Notifications
You must be signed in to change notification settings - Fork 71
Expand file tree
/
Copy pathpathman_only.sql
More file actions
97 lines (80 loc) · 2.88 KB
/
Copy pathpathman_only.sql
File metadata and controls
97 lines (80 loc) · 2.88 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
/*
* ---------------------------------------------
* 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;
/* 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);
VACUUM ANALYZE;
/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM ONLY test_only.from_only_test
UNION SELECT * FROM test_only.from_only_test;
/* should be OK */
EXPLAIN (COSTS OFF)
SELECT * FROM test_only.from_only_test
UNION SELECT * FROM ONLY test_only.from_only_test;
/* 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;
/* 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;
/* 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);
/* 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);
/* 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);
/* 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);
DROP TABLE test_only.from_only_test CASCADE;
DROP SCHEMA test_only;
DROP EXTENSION pg_pathman;