Re: Performance degradation in TPC-H Q18

On 2017-03-06 16:58:23 -0500, Robert Haas wrote:
> On Mon, Mar 6, 2017 at 3:32 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> >> I think DEBUG1 is far too high for something that could occur with
> >> some frequency on a busy system; I'm fairly strongly of the opinion
> >> that you ought to downgrade that by a couple of levels, say to DEBUG3
> >> or so.
> >
> > I actually planned to remove it entirely, before committing. It was more
> > left in for testers to be able to see when the code triggers.
>
> Oh, OK. That'd be fine too.

And pushed that way.

> > FWIW, I played with some better mixing, and it helps a bit with
> > accurately sized hashtables and multiple columns.
> >
> > What's however more interesting is that a better mixed IV and/or better
> > iteration now *slightly* *hurts* performance with grossly misestimated
> > sizes, because resizing has to copy more rows... Not what I predicted.
>
> I don't quite follow this.

The whole performance issue trigger this thread only exists when the
hashtable sizes are mis-estimated, right? Turns out that after applying
the just-committed changes, that "fixing" the bad-mixing and/or doing
iteration that's not entirely in hash-order, slighty degrades
performance. The difference is that without either of those additional
changes, we resize to the "right" size very early, when the hashtable is
barely filled (i.e. only few entries need to be moved), because the
imbalance is observed at tsart. With the changes however the resizing
happens when the table is pretty full (i.e. a lot of entries need to be
moved). So the early imbalance ends up actually not hurting
performance...

- Andres