Re: Performance degradation in TPC-H Q18

On 2017-03-01 08:42:35 +0530, Robert Haas wrote:
> On Tue, Feb 28, 2017 at 11:16 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> >> To me, it seems like a big problem that we have large, unfixed
> >> performance regressions with simplehash four months after it went in.
> >
> > Yea, I agree. I'm fairly sure that the patch I posted in that thread
> > actually fixes the issue (and would also have made already applied hash
> > patch of yours a small-ish optimization). I held back back because I
> > disliked the idea of magic constants, and I couldn't figure out a way to
> > properly "derive" them - but I'm inclined to simply live with the magic constsnts.
>
> I think it's better to push in a proposed fix and see how it holds up
> than to leave the tree in an unfixed state for long periods of time.

Yea, that was a mistake. I kind of was hoping for an epiphany that
has not yet come.

> BTW, another option to consider is lowering the target fillfactor.
> IIRC, Kuntal mentioned to me that cranking it down seemed to fix the
> issue. Obviously, it's better to detect when we need a lower
> fillfactor than to always use a lower one, but obviously the tighter
> you pack the hash table, the more likely it is that you're going to
> have these kinds of problems.

Yea, that'd be one approach, but I feel better dynamically increasing
the fillfactor like in the patch. Even with a lower fillfactor you could
see issues, and as you say a higher fillfactor is nice [TM]; after the
patch I played with *increasing* the fillfactor, without being able to
measure a downside.

Greetings,

Andres Freund