Re: master check fails on Windows Server 2008

On 16-02-2018 19:31, Tom Lane wrote:
> Marina Polyakova <m(dot)polyakova(at)postgrespro(dot)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.

Thank you very much! Your test showed that hash aggregation was not even
added to the possible paths (see windows_regression.diffs attached).
Exploring this, I found that not allowing float8 to pass by value in
config.pl was crucial for the size of the hash table used in this query
(see diff.patch attached):

From postmaster.log on Windows:

2018-02-17 20:50:48.596 MSK [1592] pg_regress/stats_ext STATEMENT:
EXPLAIN
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
2018-02-17 20:50:48.596 MSK [1592] pg_regress/stats_ext LOG: rewritten
parse tree:
...
2018-02-17 20:50:48.596 MSK [1592] pg_regress/stats_ext STATEMENT:
EXPLAIN
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
# 20 = INT8OID => pg_type.typbyval = FLOAT8PASSBYVAL:
get_agg_clause_costs_walker aggtranstype 20 get_typbyval(aggtranstype) 0
get_agg_clause_costs_walker avgwidth 8 sizeof(void *) 8
costs->transitionSpace 24
# add AGG_SORTED path:
add_paths_to_grouping_rel 1 create_agg_path (aggstrategy 1)
estimate_hashagg_tablesize 1 hashentrysize 32
# add transitionSpace = 24:
estimate_hashagg_tablesize 2 hashentrysize 56
estimate_hashagg_tablesize 3 hashentrysize 96
estimate_hashagg_tablesize dNumGroups 1632.000000
# 156672 = 96 * 1632 > 131072:
add_paths_to_grouping_rel hashaggtablesize 156672 work_mem 128 work_mem
* 1024L 131072 grouped_rel->pathlist == NIL 0
2018-02-17 20:50:48.596 MSK [1592] pg_regress/stats_ext LOG: plan:
...

From postmaster.log on my computer (allow float8 to pass by value):

2018-02-17 20:45:57.651 MSK [3012] pg_regress/stats_ext STATEMENT:
EXPLAIN
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
2018-02-17 20:45:57.651 MSK [3012] pg_regress/stats_ext LOG: rewritten
parse tree:
...
2018-02-17 20:45:57.651 MSK [3012] pg_regress/stats_ext STATEMENT:
EXPLAIN
SELECT COUNT(*) FROM ndistinct GROUP BY b, c, d;
# 20 = INT8OID => pg_type.typbyval = FLOAT8PASSBYVAL:
get_agg_clause_costs_walker aggtranstype 20 get_typbyval(aggtranstype) 1
# add AGG_SORTED path:
add_paths_to_grouping_rel 1 create_agg_path (aggstrategy 1)
estimate_hashagg_tablesize 1 hashentrysize 32
# add transitionSpace = 0:
estimate_hashagg_tablesize 2 hashentrysize 32
estimate_hashagg_tablesize 3 hashentrysize 72
estimate_hashagg_tablesize dNumGroups 1632.000000
# 117504 = 72 * 1632 < 131072:
add_paths_to_grouping_rel hashaggtablesize 117504 work_mem 128 work_mem
* 1024L 131072 grouped_rel->pathlist == NIL 0
# add AGG_HASHED path:
add_paths_to_grouping_rel 2 create_agg_path (aggstrategy 2)
2018-02-17 20:45:57.651 MSK [3012] pg_regress/stats_ext LOG: plan:
...

--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company