Re: Why the buildfarm is all pink

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Dec 10, 2013 at 7:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Anyway, bottom line is that I think we need to institute, and
>> back-patch, some consistent scheme for when to analyze the standard
>> tables during the regression tests, so that we don't have hazards
>> like this for tests that want to check what plan gets selected.
>>
>> Comments?

> Everything you're saying sounds reasonable from here.

I experimented with this and found out that an across-the-board ANALYZE
in the copy test causes a number of existing plan results to change,
because actually the very small tables like int4_tbl have never been
analyzed at all in the past.  Now, that might be just fine, or it might
be that those tests would no longer test the code path they were meant
to test.  I lack the energy right now to examine each one and decide
whether the change is OK.  What I propose instead is that the copy test
only analyze the tables that it itself loaded.  This lets us get rid
of most of the ad-hoc ANALYZEs of pre-existing tables, as in the attached
proposed patch against HEAD.  I ended up leaving in the "vacuum analyze
tenk1" in create_index.sql, because it turns out what that is actually
accomplishing is to fill the visibility map so that an index-only scan
will be chosen.  Maybe we should change all the added ANALYZEs in the
copy test to VACUUM ANALYZEs?  That would be redundant though with the
database-wide VACUUM in sanity_check.sql, which unfortunately runs after
create_index.sql.

I haven't touched matview.sql here; that seems like a distinct issue.

            regards, tom lane

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index e17881c..c05b39c 100644
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
*************** FROM bool_test;
*** 506,512 ****
  -- 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;
--- 506,511 ----
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 232a233..0f0c638 100644
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
*************** ALTER INDEX tmp_onek_unique1 RENAME TO o
*** 140,146 ****
  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
--- 140,145 ----
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index d47861e..23b3902 100644
*** a/src/test/regress/expected/arrays.out
--- b/src/test/regress/expected/arrays.out
*************** SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3
*** 421,427 ****
   {0,1,2,3}
  (1 row)

- ANALYZE array_op_test;
  SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno;
   seqno |                i                |                                                                 t
                                                        

-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
--- 421,426 ----
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index c3598e2..85113a9 100644
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
*************** on (x1 = xx1) where (xx2 is not null);
*** 2129,2135 ****
  -- 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);
--- 2129,2134 ----
diff --git a/src/test/regress/input/copy.source b/src/test/regress/input/copy.source
index ab3f508..80f911f 100644
*** a/src/test/regress/input/copy.source
--- b/src/test/regress/input/copy.source
*************** COPY array_op_test FROM '@abs_srcdir@/da
*** 60,65 ****
--- 60,90 ----

  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 febca71..e8951c5 100644
*** a/src/test/regress/output/copy.source
--- b/src/test/regress/output/copy.source
*************** COPY bt_txt_heap FROM '@abs_srcdir@/data
*** 34,39 ****
--- 34,62 ----
  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 1bbe073..f99a07d 100644
*** a/src/test/regress/sql/aggregates.sql
--- b/src/test/regress/sql/aggregates.sql
*************** FROM bool_test;
*** 220,226 ****
  -- 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)
--- 220,225 ----
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index b555430..87973c1 100644
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
*************** CREATE VIEW tmp_view (unique1) AS SELECT
*** 178,184 ****
  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
--- 178,183 ----
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 5a5a582..e4f9f31 100644
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
*************** SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6]
*** 196,203 ****
  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;
--- 196,201 ----
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 6ae37f5..718175b 100644
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
*************** on (x1 = xx1) where (xx2 is not null);
*** 330,337 ****
  -- 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);
--- 330,335 ----