X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=expected%2Finit.out;h=d443f33d1da2b1e8365807a1dfa73d51370edfd0;hb=454f72a07c2bfa37816be9b223de17e01bdb23c8;hp=ce751ed593553c594ebb4a420fa3ca096b85cd85;hpb=e16a44e7b6e72aa5c539c5b7ce5356ed9be44a50;p=pghintplan%2Fpg_hint_plan.git diff --git a/expected/init.out b/expected/init.out index ce751ed..d443f33 100644 --- a/expected/init.out +++ b/expected/init.out @@ -1,68 +1,140 @@ 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; @@ -71,6 +143,15 @@ CREATE VIEW v4 AS SELECT v_2.t1_id, t_3.id FROM v2 v_2, t3 t_3 WHERE v_2.t1_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 @@ -78,36 +159,56 @@ SELECT name, setting, category 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;