From 9c9a1f2ebb00f96f1164f01006ab9861b36f1ab1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 11 Dec 2013 15:08:38 -0500 Subject: [PATCH] Tweak placement of explicit ANALYZE commands in the regression tests. Make the COPY test, which loads most of the large static tables used in the tests, also explicitly ANALYZE those tables. This allows us to get rid of various ad-hoc, and rather redundant, ANALYZE commands that had gotten stuck into various test scripts over time to ensure we got consistent plan choices. (We could have done a database-wide ANALYZE, but that would cause stats to get attached to the small static tables too, which results in plan changes compared to the historical behavior. I'm not sure that's a good idea, so not going that far for now.) Back-patch to 9.0, since 9.0 and 9.1 are currently sometimes failing regression tests for lack of an "ANALYZE tenk1" in the subselect test. There's no need for this in 8.4 since we didn't print any plans back then. --- src/test/regress/expected/aggregates.out | 1 - src/test/regress/expected/alter_table.out | 1 - src/test/regress/expected/arrays.out | 1 - src/test/regress/expected/create_index.out | 2 +- src/test/regress/expected/join.out | 1 - src/test/regress/input/copy.source | 25 ++++++++++++++++++++++ src/test/regress/output/copy.source | 23 ++++++++++++++++++++ src/test/regress/sql/aggregates.sql | 1 - src/test/regress/sql/alter_table.sql | 1 - src/test/regress/sql/arrays.sql | 2 -- src/test/regress/sql/create_index.sql | 2 +- src/test/regress/sql/join.sql | 2 -- 12 files changed, 50 insertions(+), 12 deletions(-) diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index b6d79ca387..6f0d20fbae 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -445,7 +445,6 @@ FROM bool_test; -- Test cases that should be optimized into indexscans instead of -- the generic aggregate implementation. -- -analyze tenk1; -- ensure we get consistent plans here -- Basic cases explain (costs off) select min(unique1) from tenk1; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 68d30acc1e..896b5b25d0 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -140,7 +140,6 @@ ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1; CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1; ALTER TABLE tmp_view RENAME TO tmp_view_new; -- hack to ensure we get an indexscan here -ANALYZE tenk1; set enable_seqscan to off; set enable_bitmapscan to off; -- 5 values, sorted diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 9865b69b8b..6e5534995d 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -421,7 +421,6 @@ SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; {0,1,2,3} (1 row) -ANALYZE array_op_test; SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno; seqno | i | t -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 194c6723c4..da3bdfbe07 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2699,7 +2699,7 @@ SELECT count(*) FROM dupindexcols -- -- Check ordering of =ANY indexqual results (bug in 9.2.0) -- -vacuum analyze tenk1; -- ensure we get consistent plans here +vacuum tenk1; -- ensure we get consistent plans here explain (costs off) SELECT unique1 FROM tenk1 WHERE unique1 IN (1,42,7) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 8ea15e8627..e292ef05f6 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2129,7 +2129,6 @@ on (x1 = xx1) where (xx2 is not null); -- regression test: check for bug with propagation of implied equality -- to outside an IN -- -analyze tenk1; -- ensure we get consistent plans here select count(*) from tenk1 a where unique1 in (select unique1 from tenk1 b join tenk1 c using (unique1) where b.unique2 = 42); diff --git a/src/test/regress/input/copy.source b/src/test/regress/input/copy.source index ab3f5083e8..80f911fea8 100644 --- a/src/test/regress/input/copy.source +++ b/src/test/regress/input/copy.source @@ -60,6 +60,31 @@ COPY array_op_test FROM '@abs_srcdir@/data/array.data'; COPY array_index_op_test FROM '@abs_srcdir@/data/array.data'; +-- analyze all the data we just loaded, to ensure plan consistency +-- in later tests + +ANALYZE aggtest; +ANALYZE onek; +ANALYZE tenk1; +ANALYZE slow_emp4000; +ANALYZE person; +ANALYZE emp; +ANALYZE student; +ANALYZE stud_emp; +ANALYZE road; +ANALYZE real_city; +ANALYZE hash_i4_heap; +ANALYZE hash_name_heap; +ANALYZE hash_txt_heap; +ANALYZE hash_f8_heap; +ANALYZE test_tsvector; +ANALYZE bt_i4_heap; +ANALYZE bt_name_heap; +ANALYZE bt_txt_heap; +ANALYZE bt_f8_heap; +ANALYZE array_op_test; +ANALYZE array_index_op_test; + --- test copying in CSV mode with various styles --- of embedded line ending characters diff --git a/src/test/regress/output/copy.source b/src/test/regress/output/copy.source index febca712bb..e8951c5b73 100644 --- a/src/test/regress/output/copy.source +++ b/src/test/regress/output/copy.source @@ -34,6 +34,29 @@ COPY bt_txt_heap FROM '@abs_srcdir@/data/desc.data'; COPY bt_f8_heap FROM '@abs_srcdir@/data/hash.data'; COPY array_op_test FROM '@abs_srcdir@/data/array.data'; COPY array_index_op_test FROM '@abs_srcdir@/data/array.data'; +-- analyze all the data we just loaded, to ensure plan consistency +-- in later tests +ANALYZE aggtest; +ANALYZE onek; +ANALYZE tenk1; +ANALYZE slow_emp4000; +ANALYZE person; +ANALYZE emp; +ANALYZE student; +ANALYZE stud_emp; +ANALYZE road; +ANALYZE real_city; +ANALYZE hash_i4_heap; +ANALYZE hash_name_heap; +ANALYZE hash_txt_heap; +ANALYZE hash_f8_heap; +ANALYZE test_tsvector; +ANALYZE bt_i4_heap; +ANALYZE bt_name_heap; +ANALYZE bt_txt_heap; +ANALYZE bt_f8_heap; +ANALYZE array_op_test; +ANALYZE array_index_op_test; --- test copying in CSV mode with various styles --- of embedded line ending characters create temp table copytest ( diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index e33b36bad1..bfda284bf4 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -208,7 +208,6 @@ FROM bool_test; -- Test cases that should be optimized into indexscans instead of -- the generic aggregate implementation. -- -analyze tenk1; -- ensure we get consistent plans here -- Basic cases explain (costs off) diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 404fa95e3f..84529c0530 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -178,7 +178,6 @@ CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1; ALTER TABLE tmp_view RENAME TO tmp_view_new; -- hack to ensure we get an indexscan here -ANALYZE tenk1; set enable_seqscan to off; set enable_bitmapscan to off; -- 5 values, sorted diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index 294b44ee08..9ea53b1544 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -196,8 +196,6 @@ SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}"; SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}"; SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; -ANALYZE array_op_test; - SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno; SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno; SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index c6ca01c8b4..6835c884c6 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -896,7 +896,7 @@ SELECT count(*) FROM dupindexcols -- Check ordering of =ANY indexqual results (bug in 9.2.0) -- -vacuum analyze tenk1; -- ensure we get consistent plans here +vacuum tenk1; -- ensure we get consistent plans here explain (costs off) SELECT unique1 FROM tenk1 diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 1868b6d8e2..316a6e93d7 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -330,8 +330,6 @@ on (x1 = xx1) where (xx2 is not null); -- regression test: check for bug with propagation of implied equality -- to outside an IN -- -analyze tenk1; -- ensure we get consistent plans here - select count(*) from tenk1 a where unique1 in (select unique1 from tenk1 b join tenk1 c using (unique1) where b.unique2 = 42); -- 2.40.0