Re: Performance degradation in TPC-H Q18

On Wed, Feb 22, 2017 at 11:23 AM, Kuntal Ghosh
<kuntalghosh(dot)2007(at)gmail(dot)com> wrote:
> While conducting the experiments for parallelism, Rafia came across a
> hang in Q18 when plan uses partial and finalize hash aggregate. This
> could be seen on both scale factors - 20 and 300, on setting work_mem
> high enough so that the query uses hash aggregate. It seems that
> commit b81b5a96f424531b97cdd1dba97d9d1b9c9d372e does not solve the
> issue completely.

Andres, any thoughts? Isn't the same issue that we were discussing
https://www.postgresql.org/message-id/CA+TgmoYNO8qouPVO=1Q2aXzuxe942d_T5bcvZd9iKOC9tb3uLg@mail.gmail.com
over a month ago?

To me, it seems like a big problem that we have large, unfixed
performance regressions with simplehash four months after it went in.
I hate to suggest ripping the whole thing out, and it seems like
overkill, but it's pretty clear to me that the current state of things
is unacceptable, and that we're going to have a lot of unhappy users
if we ship it the way that it is right now. I want to point out that
the kinds of problems we're hitting here are exactly what I told you I
was worried about before it went in - that the average-case
performance would be better but that there would be
all-too-easy-to-hit cases where things got much worse because the
whole thing degenerated into a linear search. Not only did that
happen, but there seem to be multiple ways of producing it without
half trying, of which b81b5a96f424531b97cdd1dba97d9d1b9c9d372e fixed
only one. Something that's 5-10% faster in common cases but 2x or 10x
slower when things go wrong is not an improvement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company