Marina Polyakova <m.polyakova@postgrespro.ru> writes:
> On 16-02-2018 19:31, Tom Lane wrote:
>> 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.)
> 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):
Ah-hah. I can reproduce the described failure if I configure with
--disable-float8-byval on an otherwise 64-bit machine. It appears that
the minimum work_mem setting that will allow this query to use a hashagg
plan on such a configuration is about 155kB (which squares with the
results you show). On the other hand, in a normal 64-bit configuration,
work_mem settings of 160kB or more cause other failures (due to other
plans switching to hashagg), and on a 32-bit machine I see such failures
with work_mem of 150kB or more. So there's basically no setting of
work_mem that would make all these tests pass everywhere.
I see several things we could do about this:
1. Nothing; just say "sorry, we don't promise that the regression tests
pass with no plan differences on nonstandard configurations". Given that
--disable-float8-byval has hardly any real-world use, there is not a lot
of downside to that.
2. Decide that --disable-float8-byval, and for that matter
--disable-float4-byval, have no real-world use at all and take them out.
There was some point in those options back when we cared about binary
compatibility with version-zero C functions, but now I'm not sure why
anyone would use them.
3. Drop that one test case from stats_ext.sql; I'm not sure how much
additional test value it's actually bringing.
4. Try to tweak the stats_ext.sql test conditions in some more refined
way to get the test to pass everywhere. This'd be a lot of work with
no guarantee of success, so I'm not too excited about it.
5. Something else?
regards, tom lane