Re: Performance degradation in TPC-H Q18

On 2017-03-01 09:13:15 +0530, Kuntal Ghosh wrote:
> On Wed, Mar 1, 2017 at 8:48 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> >> 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.

> I've tested with different fill factors and the query got completed
> with fill factor 0.6.

That's without the patch in
http://archives.postgresql.org/message-id/20161123083351.5vramz52nmdokhzz%40alap3.anarazel.de
? With that patch it should complete without that change, right?

> With linear probing, the performance degrades more quickly at high
> fill factors because of primary clustering, a tendency for one
> collision to cause more nearby collisions. So, increasing the fill
> factor further doesn't seem to be a good idea.

Well, the idea is increasing it, but *also* detecting clustering and
adaptively resizing earlier.

> So, I was looking for other alternatives and I've found one called
> RobinHood hashing.

simplehash.h implements robin hood hashing.

- Andres