SET search_path TO public;
-CREATE SCHEMA s1;
+CREATE EXTENSION pg_hint_plan;
+CREATE SCHEMA s0;
CREATE TABLE t1 (id int PRIMARY KEY, val int);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE t2 (id int PRIMARY KEY, val int);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE t3 (id int PRIMARY KEY, val int);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
CREATE TABLE t4 (id int PRIMARY KEY, val int);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4"
+CREATE TABLE t5 (id int PRIMARY KEY, val int);
CREATE TABLE p1 (id int PRIMARY KEY, val int);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_pkey" for table "p1"
CREATE TABLE p1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c1_pkey" for table "p1_c1"
CREATE TABLE p1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c2_pkey" for table "p1_c2"
CREATE TABLE p1_c3 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c3_pkey" for table "p1_c3"
CREATE TABLE p1_c4 (LIKE p1 INCLUDING ALL, CHECK (id > 300)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c4_pkey" for table "p1_c4"
CREATE TABLE p1_c1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p1_c1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c1_c1_pkey" for table "p1_c1_c1"
CREATE TABLE p1_c1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p1_c1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c1_c2_pkey" for table "p1_c1_c2"
CREATE TABLE p1_c3_c1 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p1_c3);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c3_c1_pkey" for table "p1_c3_c1"
CREATE TABLE p1_c3_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p1_c3);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p1_c3_c2_pkey" for table "p1_c3_c2"
-CREATE TABLE s1.t1 (id int PRIMARY KEY, val int);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
+CREATE TABLE p2 (id int PRIMARY KEY, val text);
+CREATE INDEX p2_id_val_idx ON p2 (id, val);
+CREATE UNIQUE INDEX p2_val_idx ON p2 (val);
+CREATE INDEX p2_ununi_id_val_idx ON p2 (val);
+CREATE INDEX p2_val_idx_1 ON p2 USING hash (val);
+CREATE INDEX p2_val_id_idx ON p2 (val, id);
+CREATE INDEX p2_val_idx2 ON p2 (val COLLATE "ja_JP");
+CREATE INDEX p2_val_idx3 ON p2 (val varchar_ops);
+CREATE INDEX p2_val_idx4 ON p2 (val DESC NULLS LAST);
+CREATE INDEX p2_val_idx5 ON p2 (val NULLS FIRST);
+CREATE INDEX p2_expr ON p2 ((val < '120'));
+CREATE INDEX p2_expr2 ON p2 ((id * 2 < 120));
+CREATE INDEX p2_val_idx6 ON p2 (val) WHERE val >= '50' AND val < '51';
+CREATE INDEX p2_val_idx7 ON p2 (val) WHERE id < 120;
+CREATE TABLE p2_c1 (LIKE p2 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p2);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p2);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c3 (LIKE p2 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p2);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c4 (LIKE p2 INCLUDING ALL, CHECK (id > 300)) INHERITS(p2);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c1_c1 (LIKE p2 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p2_c1);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c1_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p2_c1);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c3_c1 (LIKE p2 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p2_c3);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE p2_c3_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p2_c3);
+NOTICE: merging column "id" with inherited definition
+NOTICE: merging column "val" with inherited definition
+CREATE TABLE s0.t1 (id int PRIMARY KEY, val int);
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
INSERT INTO t4 SELECT i, i FROM (SELECT generate_series(1, 10) i) t;
+INSERT INTO t5 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO p1_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
INSERT INTO p1_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
INSERT INTO p1_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
INSERT INTO p1_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
INSERT INTO p1_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
INSERT INTO p1_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
+INSERT INTO p2_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
+INSERT INTO p2_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
+INSERT INTO p2_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
+INSERT INTO p2_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
+INSERT INTO p2_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
+INSERT INTO p2_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
CREATE INDEX t1_val ON t1 (val);
CREATE INDEX t2_val ON t2 (val);
+CREATE INDEX t5_id1 ON t5 (id);
+CREATE INDEX t5_id2 ON t5 (id);
+CREATE INDEX t5_id3 ON t5 (id);
+CREATE INDEX t5_val ON t5 (val);
+DROP INDEX p2_c4_val_id_idx;
+CREATE INDEX p2_id2_val ON p2 (id, id, val);
+CREATE INDEX p2_c1_id2_val ON p2_c1 (id, id, val);
+CREATE INDEX p2_c2_id2_val ON p2_c2 (id, id, val);
+CREATE INDEX p2_val2_id ON p2 (val, id, val);
+CREATE INDEX t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ON t5 (id);
+CREATE INDEX p1_val1 ON p1 (val);
+CREATE INDEX p1_val2 ON p1 (val);
+CREATE INDEX p1_val3 ON p1 (val);
+CREATE INDEX p1_c1_val1 ON p1_c1 (val);
+CREATE INDEX p1_c1_val2 ON p1_c1 (val);
+CREATE INDEX p1_c1_val3 ON p1_c1 (val);
+CREATE INDEX p1_c1_c1_val1 ON p1_c1_c1 (val);
+CREATE INDEX p1_c1_c1_val2 ON p1_c1_c1 (val);
+CREATE INDEX p1_c1_c1_val3 ON p1_c1_c1 (val);
+CREATE INDEX p1_c1_c2_val1 ON p1_c1_c2 (val);
+CREATE INDEX p1_c1_c2_val2 ON p1_c1_c2 (val);
+CREATE INDEX p1_c1_c2_val3 ON p1_c1_c2 (val);
+CREATE INDEX p1_c2_val1 ON p1_c2 (val);
+CREATE INDEX p1_c2_val2 ON p1_c2 (val);
+CREATE INDEX p1_c2_val3 ON p1_c2 (val);
+CREATE INDEX p1_c3_val1 ON p1_c3 (val);
+CREATE INDEX p1_c3_val2 ON p1_c3 (val);
+CREATE INDEX p1_c3_val3 ON p1_c3 (val);
+CREATE INDEX p1_c3_c1_val1 ON p1_c3_c1 (val);
+CREATE INDEX p1_c3_c1_val2 ON p1_c3_c1 (val);
+CREATE INDEX p1_c3_c1_val3 ON p1_c3_c1 (val);
+CREATE INDEX p1_c3_c2_val1 ON p1_c3_c2 (val);
+CREATE INDEX p1_c3_c2_val2 ON p1_c3_c2 (val);
+CREATE INDEX p1_c3_c2_val3 ON p1_c3_c2 (val);
+CREATE INDEX p1_c4_val1 ON p1_c4 (val);
+CREATE INDEX p1_c4_val2 ON p1_c4 (val);
+CREATE INDEX p1_c4_val3 ON p1_c4 (val);
ANALYZE t1;
ANALYZE t2;
ANALYZE t3;
ANALYZE t4;
+ANALYZE t5;
ANALYZE p1;
ANALYZE p1_c1;
ANALYZE p1_c2;
+ANALYZE p2;
CREATE VIEW v1 AS SELECT id, val FROM t1;
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;
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;
* The following GUC parameters need the setting of the default value to
* succeed in regression test.
*/
+/* Fix auto-tunable parameters */
+ALTER SYSTEM SET effective_cache_size TO 16384;
+SELECT pg_reload_conf();
+ pg_reload_conf
+----------------
+ t
+(1 row)
+
+SET effective_cache_size TO 16384;
CREATE VIEW settings AS
SELECT name, setting, category
FROM pg_settings
OR name = 'client_min_messages'
ORDER BY category, name;
SELECT * FROM settings;
- name | setting | category
----------------------------+-----------+---------------------------------------------
- geqo | on | Query Tuning / Genetic Query Optimizer
- geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
- geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
- geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer
- geqo_seed | 0 | Query Tuning / Genetic Query Optimizer
- geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer
- geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer
- constraint_exclusion | partition | Query Tuning / Other Planner Options
- cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options
- default_statistics_target | 100 | Query Tuning / Other Planner Options
- from_collapse_limit | 8 | Query Tuning / Other Planner Options
- join_collapse_limit | 8 | Query Tuning / Other Planner Options
- cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants
- cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants
- cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants
- effective_cache_size | 16384 | Query Tuning / Planner Cost Constants
- random_page_cost | 4 | Query Tuning / Planner Cost Constants
- seq_page_cost | 1 | Query Tuning / Planner Cost Constants
- enable_bitmapscan | on | Query Tuning / Planner Method Configuration
- enable_hashagg | on | Query Tuning / Planner Method Configuration
- enable_hashjoin | on | Query Tuning / Planner Method Configuration
- enable_indexscan | on | Query Tuning / Planner Method Configuration
- enable_material | on | Query Tuning / Planner Method Configuration
- enable_mergejoin | on | Query Tuning / Planner Method Configuration
- enable_nestloop | on | Query Tuning / Planner Method Configuration
- enable_seqscan | on | Query Tuning / Planner Method Configuration
- enable_sort | on | Query Tuning / Planner Method Configuration
- enable_tidscan | on | Query Tuning / Planner Method Configuration
- client_min_messages | notice | Reporting and Logging / When to Log
-(29 rows)
+ name | setting | category
+--------------------------------+-----------+-------------------------------------------------
+ client_min_messages | notice | Client Connection Defaults / Statement Behavior
+ geqo | on | Query Tuning / Genetic Query Optimizer
+ geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
+ geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
+ geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer
+ geqo_seed | 0 | Query Tuning / Genetic Query Optimizer
+ geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer
+ geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer
+ constraint_exclusion | partition | Query Tuning / Other Planner Options
+ cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options
+ default_statistics_target | 100 | Query Tuning / Other Planner Options
+ from_collapse_limit | 8 | Query Tuning / Other Planner Options
+ jit | on | Query Tuning / Other Planner Options
+ join_collapse_limit | 8 | Query Tuning / Other Planner Options
+ plan_cache_mode | auto | Query Tuning / Other Planner Options
+ cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants
+ cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants
+ cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants
+ effective_cache_size | 16384 | Query Tuning / Planner Cost Constants
+ jit_above_cost | 100000 | Query Tuning / Planner Cost Constants
+ jit_inline_above_cost | 500000 | Query Tuning / Planner Cost Constants
+ jit_optimize_above_cost | 500000 | Query Tuning / Planner Cost Constants
+ min_parallel_index_scan_size | 64 | Query Tuning / Planner Cost Constants
+ min_parallel_table_scan_size | 1024 | Query Tuning / Planner Cost Constants
+ parallel_setup_cost | 1000 | Query Tuning / Planner Cost Constants
+ parallel_tuple_cost | 0.1 | Query Tuning / Planner Cost Constants
+ random_page_cost | 4 | Query Tuning / Planner Cost Constants
+ seq_page_cost | 1 | Query Tuning / Planner Cost Constants
+ enable_async_append | on | Query Tuning / Planner Method Configuration
+ enable_bitmapscan | on | Query Tuning / Planner Method Configuration
+ enable_gathermerge | on | Query Tuning / Planner Method Configuration
+ enable_hashagg | on | Query Tuning / Planner Method Configuration
+ enable_hashjoin | on | Query Tuning / Planner Method Configuration
+ enable_incremental_sort | on | Query Tuning / Planner Method Configuration
+ enable_indexonlyscan | on | Query Tuning / Planner Method Configuration
+ enable_indexscan | on | Query Tuning / Planner Method Configuration
+ enable_material | on | Query Tuning / Planner Method Configuration
+ enable_memoize | on | Query Tuning / Planner Method Configuration
+ enable_mergejoin | on | Query Tuning / Planner Method Configuration
+ enable_nestloop | on | Query Tuning / Planner Method Configuration
+ enable_parallel_append | on | Query Tuning / Planner Method Configuration
+ enable_parallel_hash | on | Query Tuning / Planner Method Configuration
+ enable_partition_pruning | on | Query Tuning / Planner Method Configuration
+ enable_partitionwise_aggregate | off | Query Tuning / Planner Method Configuration
+ enable_partitionwise_join | off | Query Tuning / Planner Method Configuration
+ enable_seqscan | on | Query Tuning / Planner Method Configuration
+ enable_sort | on | Query Tuning / Planner Method Configuration
+ enable_tidscan | on | Query Tuning / Planner Method Configuration
+(48 rows)
+ANALYZE;
OSDN Git Service
