Re: Parallel Append subplan order instability on aye-aye

I wrote:
> Yeah.  I think we've had quite enough of the stats-transmission-related
> failures, and they're no longer proving anything about the original
> problem.  So I will go do what I proposed in mid-July and revert the
> stats queries, while keeping the reltuples/relpages check.  (I'd kind
> of like to get more confirmation that the plan shape change is associated
> with those fields reading as zeroes, before we decide what to do about the
> underlying instability.)

Christoph Berg's recent complaint reminded me to scan the buildfarm
database again for info related to this issue, and I found this:

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=moonjelly&dt=2019-07-02%2017%3A17%3A02

in which the failure diffs are

diff -U3 /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/select_parallel.out
/home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/select_parallel.out
--- /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/select_parallel.out
2019-05-2119:17:03.472207619 +0200 
+++
/home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/select_parallel.out
  2019-07-02 19:21:53.643095637 +0200 
@@ -98,7 +98,7 @@
  a_star  |        1 |         3
  b_star  |        1 |         4
  c_star  |        1 |         4
- d_star  |        1 |        16
+ d_star  |        0 |         0
  e_star  |        1 |         7
  f_star  |        1 |        16
 (6 rows)
@@ -130,7 +130,7 @@
 -----------------------------------------------------
  Finalize Aggregate
    ->  Gather
-         Workers Planned: 1
+         Workers Planned: 3
          ->  Partial Aggregate
                ->  Append
                      ->  Parallel Seq Scan on a_star
diff -U3 /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/stats.out
/home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/stats.out
--- /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/test/regress/expected/stats.out    2019-05-21
19:17:03.472207619+0200 
+++ /home/fabien/pg/build-farm-10/buildroot/HEAD/pgsql.build/src/bin/pg_upgrade/tmp_check/regress/results/stats.out
2019-07-0219:21:57.891105601 +0200 
@@ -208,7 +208,7 @@
  a_star  |        1 |         3
  b_star  |        1 |         4
  c_star  |        1 |         4
- d_star  |        1 |        16
+ d_star  |        0 |         0
  e_star  |        1 |         7
  f_star  |        1 |        16
 (6 rows)

While this fails to show the plan ordering difference we were looking for,
it does show that relpages/reltuples can sometimes read as zeroes for one
of these tables.  (It also indicates that at least some of the
worker-count instability we've seen might trace to this same issue.)

That's the only related failure I could find in the last three months,
which makes me think that we've changed the regression tests enough that
the chance timing needed to cause this is (once again) very improbable.
So I'm prepared to give up waiting for more buildfarm evidence.

I propose to finish reverting f03a9ca43 in HEAD, and instead install
the attached in HEAD and v12.  This follows the upthread suggestions
from Thomas and myself to use ANALYZE to ensure that these tables
have the expected relpages/reltuples entries.

In principle, we might need this further back than v12, but without having
seen a test failure in the wild I'm not tempted to back-patch further.

            regards, tom lane

diff --git a/src/test/regress/expected/create_misc.out b/src/test/regress/expected/create_misc.out
index 8366841..cee35ed 100644
--- a/src/test/regress/expected/create_misc.out
+++ b/src/test/regress/expected/create_misc.out
@@ -130,6 +130,13 @@ INSERT INTO f_star (class, e) VALUES ('f', '-12'::int2);
 INSERT INTO f_star (class, f)
    VALUES ('f', '(11111111,33333333),(22222222,44444444)'::polygon);
 INSERT INTO f_star (class) VALUES ('f');
+-- Analyze the X_star tables for better plan stability in later tests
+ANALYZE a_star;
+ANALYZE b_star;
+ANALYZE c_star;
+ANALYZE d_star;
+ANALYZE e_star;
+ANALYZE f_star;
 --
 -- for internal portal (cursor) tests
 --
diff --git a/src/test/regress/sql/create_misc.sql b/src/test/regress/sql/create_misc.sql
index d4a63b7..d0b04a8 100644
--- a/src/test/regress/sql/create_misc.sql
+++ b/src/test/regress/sql/create_misc.sql
@@ -192,6 +192,14 @@ INSERT INTO f_star (class, f)

 INSERT INTO f_star (class) VALUES ('f');

+-- Analyze the X_star tables for better plan stability in later tests
+ANALYZE a_star;
+ANALYZE b_star;
+ANALYZE c_star;
+ANALYZE d_star;
+ANALYZE e_star;
+ANALYZE f_star;
+

 --
 -- for internal portal (cursor) tests