SET search_path TO public; CREATE EXTENSION pg_hint_plan; CREATE SCHEMA s0; CREATE TABLE t1 (id int PRIMARY KEY, val int); CREATE TABLE t2 (id int PRIMARY KEY, val int); CREATE TABLE t3 (id int PRIMARY KEY, val int); CREATE TABLE t4 (id int PRIMARY KEY, val int); CREATE TABLE t5 (id int PRIMARY KEY, val int); CREATE TABLE p1 (id int PRIMARY KEY, val int); 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 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 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 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 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 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 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 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 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 "C"); 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; 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; /* * Utility function to retrieve a query ID from a query. * * This wraps the input query within an EXPLAIN (VERBOSE, FORMAT json) and * returns its query ID. */ CREATE FUNCTION get_query_id(text) RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE query text; explain_output text; query_id bigint; BEGIN query = 'EXPLAIN (VERBOSE, FORMAT json) ' || $1; EXECUTE query INTO explain_output; SELECT INTO query_id ((explain_output::jsonb)->0->'Query Identifier')::bigint; return query_id; END; $$; /* * The following GUC parameters need the setting of the default value to * succeed in regression test. */ SELECT current_database() AS datname \gset /* Fix auto-tunable parameters */ ALTER DATABASE :"datname" SET effective_cache_size TO 16384; SET effective_cache_size TO 16384; CREATE VIEW settings AS SELECT name, setting, category FROM pg_settings WHERE category LIKE 'Query Tuning%' OR name = 'client_min_messages' ORDER BY category, name; SELECT * FROM settings; 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 recursive_worktable_factor | 10 | 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_group_by_reordering | 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_presorted_aggregate | 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 (51 rows) -- EXPLAIN filtering -- -- A lot of tests rely on EXPLAIN being executed with costs enabled -- to check the validity of the plans generated with hints. -- -- This function takes in input a query, executes it and applies some -- filtering to ensure a stable output. See the tests calling this -- function to see how it can be used. -- -- If required, this can be extended with new operation modes. CREATE OR REPLACE FUNCTION explain_filter(text) RETURNS SETOF text LANGUAGE plpgsql AS $$ DECLARE ln text; BEGIN FOR ln IN EXECUTE $1 LOOP -- Replace cost values with some 'xxx' ln := regexp_replace(ln, 'cost=10{7}[.0-9]+ ', 'cost={inf}..{inf} '); ln := regexp_replace(ln, 'cost=[.0-9]+ ', 'cost=xxx..xxx '); -- Replace width with some 'xxx' ln := regexp_replace(ln, 'width=[0-9]+([^0-9])', 'width=xxx\1'); -- Filter foreign files ln := regexp_replace(ln, '^( +Foreign File: ).*$', '\1 (snip..)'); return next ln; END LOOP; END; $$; ANALYZE;