Re: Enabling B-Tree deduplication by default

On Wed, Jan 29, 2020 at 6:56 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> This (and the rest of the explanation) don't really address my
> concern. I understand that deduplicating in lieu of splitting a page
> in a unique index is highly likely to be a win. What I don't
> understand is why it shouldn't just be a win, period. Not splitting a
> page seems like it has a big upside regardless of whether the index is
> unique -- and in fact, the upside could be a lot bigger for a
> non-unique index. If the coarse-grained LP_DEAD thing is the problem,
> then I can grasp that issue, but you don't seem very worried about
> that.

You're right that I'm not worried about the coarse-grained LP_DEAD
thing here. What I'm concerned about is cases where we attempt
deduplication, but it doesn't work out because there are no duplicates
-- that means we waste some cycles. Or cases where we manage to delay
a split, but only for a very short period of time -- in theory it
would be preferable to just accept the page split up front. However,
in practice we can't make these distinctions, since it would hinge
upon predicting the future, and we don't have a good heuristic. The
fact that a deduplication pass barely manages to prevent an immediate
page split isn't a useful proxy for how likely it is that the page
will split in any timeframe. We might have prevented it from happening
for another 2 milliseconds, or we might have prevented it forever.
It's totally workload dependent.

The good news is that these extra cycles aren't very noticeable even
with a workload where deduplication doesn't help at all (e.g. with
several indexes an append-only table, and few or no duplicates). The
cycles are generally a fixed cost. Furthermore, it seems to be
possible to virtually avoid the problem in the case of unique indexes
by applying the incoming-item-is-duplicate heuristic. Maybe I am
worrying over nothing.

> Generally, I think it's a bad idea to give the user an "emergency off
> switch" and then sometimes ignore it. If the feature seems to be
> generally beneficial, but you're worried that there might be
> regressions in obscure cases, then turn it on by default, and give the
> user the ability to forcibly turn it off. But don't give the the
> opportunity to forcibly turn it off sometimes. Nobody's going to run
> around setting a reloption just for fun -- they're going to do it
> because they hit a problem.

Actually, we do. There is both a reloption and a GUC. The GUC only
works with non-unique indexes, where the extra cost I describe might
be an issue (it can at least be demonstrated in a benchmark).The
reloption works with both unique and non-unique indexes. It will be
useful for turning off deduplication selectively in non-unique
indexes. In the case of unique indexes, it can be thought of as a
debugging thing (though we really don't want users to think about
deduplication in unique indexes at all).

I'm really having a hard time imagining or demonstrating any downside
with unique indexes, given the heuristic, so ISTM that turning off
deduplication really is just a debugging thing there.

My general assumption is that 99%+ of users will want to use
deduplication everywhere. I am concerned about the remaining ~1% of
users who might have a workload that is slightly regressed by
deduplication. Even this small minority of users will still want to
use deduplication in unique indexes. Plus we don't really want to talk
about deduplication in unique indexes to users, since it'll probably
confuse them. That's another reason to treat each case differently.

Again, maybe I'm making an excessively thin distinction. I really want
to be able to enable the feature everywhere, while also not getting
even one complaint about it. Perhaps that's just not a realistic or
useful goal.

--
Peter Geoghegan