Re: master check fails on Windows Server 2008 - Mailing list pgsql-hackers

Marina Polyakova <m.polyakova@postgrespro.ru> writes:
> Hello, hackers! I got a permanent failure of master (commit 
> 2a41507dab0f293ff241fe8ae326065998668af8) check on Windows Server 2008. 
> Regression output and diffs as well as config.pl are attached.

Weird.  AFAICS the cost estimates for those two plans should be quite
different, so this isn't just a matter of the estimates maybe being
a bit platform-dependent.  (And that test has been there nearly a
year without causing reported problems.)

To dig into it a bit more, I tweaked the test case to show the costs
for both plans, and got an output diff as attached.  Could you try
the same experiment on your Windows box?  In order to force the choice
in the other direction, you'd need to temporarily disable enable_sort,
not enable_hashagg as I did here, but the principle is the same.

            regards, tom lane

diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 46acaad..1082660 100644
*** a/src/test/regress/sql/stats_ext.sql
--- b/src/test/regress/sql/stats_ext.sql
*************** EXPLAIN (COSTS off)
*** 177,184 ****
  EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;

! EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;

  EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
--- 177,188 ----
  EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY a, b, c, d;

! EXPLAIN --(COSTS off)
!  SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
! set enable_hashagg = 0;
! EXPLAIN --(COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
+ reset enable_hashagg;

  EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
*** /home/postgres/pgsql/src/test/regress/expected/stats_ext.out    Mon Feb 12 14:53:46 2018
--- /home/postgres/pgsql/src/test/regress/results/stats_ext.out    Fri Feb 16 11:23:11 2018
***************
*** 309,323 ****
           ->  Seq Scan on ndistinct
  (5 rows)

! EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
!          QUERY PLAN
! -----------------------------
!  HashAggregate
     Group Key: b, c, d
!    ->  Seq Scan on ndistinct
  (3 rows)

  EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
           QUERY PLAN
--- 309,336 ----
           ->  Seq Scan on ndistinct
  (5 rows)

! EXPLAIN --(COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
!                               QUERY PLAN
! ----------------------------------------------------------------------
!  HashAggregate  (cost=291.00..307.32 rows=1632 width=20)
     Group Key: b, c, d
!    ->  Seq Scan on ndistinct  (cost=0.00..191.00 rows=10000 width=12)
  (3 rows)

+ set enable_hashagg = 0;
+ EXPLAIN --(COSTS off)
+  SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
+                                  QUERY PLAN
+ ----------------------------------------------------------------------------
+  GroupAggregate  (cost=1026.89..1168.21 rows=1632 width=20)
+    Group Key: b, c, d
+    ->  Sort  (cost=1026.89..1051.89 rows=10000 width=12)
+          Sort Key: b, c, d
+          ->  Seq Scan on ndistinct  (cost=0.00..191.00 rows=10000 width=12)
+ (5 rows)
+
+ reset enable_hashagg;
  EXPLAIN (COSTS off)
   SELECT COUNT(*) FROM ndistinct GROUP BY a, d;
           QUERY PLAN

======================================================================