1 SET search_path TO public;
3 CREATE TABLE t1 (id int PRIMARY KEY, val int);
4 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
5 CREATE TABLE t2 (id int PRIMARY KEY, val int);
6 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
7 CREATE TABLE t3 (id int PRIMARY KEY, val int);
8 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
9 CREATE TABLE t4 (id int PRIMARY KEY, val int);
10 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4"
11 CREATE TABLE t5 (id int PRIMARY KEY, val int);
12 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t5_pkey" for table "t5"
13 CREATE TABLE p1 (id int PRIMARY KEY, val int);
14 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_pkey" for table "p1"
15 CREATE TABLE p1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p1);
16 NOTICE: merging column "id" with inherited definition
17 NOTICE: merging column "val" with inherited definition
18 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c1_pkey" for table "p1_c1"
19 CREATE TABLE p1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p1);
20 NOTICE: merging column "id" with inherited definition
21 NOTICE: merging column "val" with inherited definition
22 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c2_pkey" for table "p1_c2"
23 CREATE TABLE p1_c3 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p1);
24 NOTICE: merging column "id" with inherited definition
25 NOTICE: merging column "val" with inherited definition
26 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c3_pkey" for table "p1_c3"
27 CREATE TABLE p1_c4 (LIKE p1 INCLUDING ALL, CHECK (id > 300)) INHERITS(p1);
28 NOTICE: merging column "id" with inherited definition
29 NOTICE: merging column "val" with inherited definition
30 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c4_pkey" for table "p1_c4"
31 CREATE TABLE p1_c1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p1_c1);
32 NOTICE: merging column "id" with inherited definition
33 NOTICE: merging column "val" with inherited definition
34 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c1_c1_pkey" for table "p1_c1_c1"
35 CREATE TABLE p1_c1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p1_c1);
36 NOTICE: merging column "id" with inherited definition
37 NOTICE: merging column "val" with inherited definition
38 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c1_c2_pkey" for table "p1_c1_c2"
39 CREATE TABLE p1_c3_c1 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p1_c3);
40 NOTICE: merging column "id" with inherited definition
41 NOTICE: merging column "val" with inherited definition
42 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c3_c1_pkey" for table "p1_c3_c1"
43 CREATE TABLE p1_c3_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p1_c3);
44 NOTICE: merging column "id" with inherited definition
45 NOTICE: merging column "val" with inherited definition
46 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c3_c2_pkey" for table "p1_c3_c2"
47 CREATE TABLE s0.t1 (id int PRIMARY KEY, val int);
48 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
49 INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
50 INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
51 INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
52 INSERT INTO t4 SELECT i, i FROM (SELECT generate_series(1, 10) i) t;
53 INSERT INTO t5 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
54 INSERT INTO p1_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
55 INSERT INTO p1_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
56 INSERT INTO p1_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
57 INSERT INTO p1_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
58 INSERT INTO p1_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
59 INSERT INTO p1_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
60 CREATE INDEX t1_val ON t1 (val);
61 CREATE INDEX t2_val ON t2 (val);
62 CREATE INDEX t5_id1 ON t5 (id);
63 CREATE INDEX t5_id2 ON t5 (id);
64 CREATE INDEX t5_id3 ON t5 (id);
65 CREATE INDEX t5_val ON t5 (val);
74 CREATE VIEW v1 AS SELECT id, val FROM t1;
75 CREATE VIEW v2 AS SELECT t1.id t1_id, t1.val t1_val, t2.id t2_id, t2.val t2_val FROM t1, t2 WHERE t1.id = t2.id;
76 CREATE VIEW v3 AS SELECT t_1.id t1_id, t_1.val t1_val, t_2.id t2_id, t_2.val t2_val FROM t1 t_1, t2 t_2 WHERE t_1.id = t_2.id;
77 CREATE VIEW v4 AS SELECT v_2.t1_id, t_3.id FROM v2 v_2, t3 t_3 WHERE v_2.t1_id = t_3.id;
79 * The following GUC parameters need the setting of the default value to
80 * succeed in regression test.
82 CREATE VIEW settings AS
83 SELECT name, setting, category
85 WHERE category LIKE 'Query Tuning%'
86 OR name = 'client_min_messages'
87 ORDER BY category, name;
88 SELECT * FROM settings;
89 name | setting | category
90 ---------------------------+-----------+---------------------------------------------
91 geqo | on | Query Tuning / Genetic Query Optimizer
92 geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
93 geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
94 geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer
95 geqo_seed | 0 | Query Tuning / Genetic Query Optimizer
96 geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer
97 geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer
98 constraint_exclusion | partition | Query Tuning / Other Planner Options
99 cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options
100 default_statistics_target | 100 | Query Tuning / Other Planner Options
101 from_collapse_limit | 8 | Query Tuning / Other Planner Options
102 join_collapse_limit | 8 | Query Tuning / Other Planner Options
103 cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants
104 cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants
105 cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants
106 effective_cache_size | 16384 | Query Tuning / Planner Cost Constants
107 random_page_cost | 4 | Query Tuning / Planner Cost Constants
108 seq_page_cost | 1 | Query Tuning / Planner Cost Constants
109 enable_bitmapscan | on | Query Tuning / Planner Method Configuration
110 enable_hashagg | on | Query Tuning / Planner Method Configuration
111 enable_hashjoin | on | Query Tuning / Planner Method Configuration
112 enable_indexonlyscan | on | Query Tuning / Planner Method Configuration
113 enable_indexscan | on | Query Tuning / Planner Method Configuration
114 enable_material | on | Query Tuning / Planner Method Configuration
115 enable_mergejoin | on | Query Tuning / Planner Method Configuration
116 enable_nestloop | on | Query Tuning / Planner Method Configuration
117 enable_seqscan | on | Query Tuning / Planner Method Configuration
118 enable_sort | on | Query Tuning / Planner Method Configuration
119 enable_tidscan | on | Query Tuning / Planner Method Configuration
120 client_min_messages | notice | Reporting and Logging / When to Log