Horton

Re: Making all nbtree entries unique by having heap TIDs participate in comparisons

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Alexander Korotkov <a.korotkov@postgrespro.ru>, Thomas Munro <thomas.munro@enterprisedb.com>, Claudio Freire <klaussfreire@gmail.com>, Anastasia Lubennikova <a.lubennikova@postgrespro.ru>, "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: 2019-03-12T07:20:21Z
Lists: pgsql-hackers 
On Mon, Mar 11, 2019 at 11:30 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> Yeah, that's fine. I'm curious, though, could you bloat the indexes back
> to the old size by setting the fillfactor?

I think that that might work, though it's hard to say for sure offhand.

The "split after new item" optimization is supposed to be a variation
of rightmost splits, of course. We apply fillfactor in the same way
much of the time. You would still literally split immediately after
the new item some of the time, though, which makes it unclear how much
bloat there would be without testing it.

Some indexes mostly apply fillfactor in non-rightmost pages, while
other indexes mostly split at the exact point past the new item,
depending on details like the size of the groupings.

I am currently doing a multi-day 6,000 warehouse benchmark, since I
want to be sure that the bloat resistance will hold up over days. I
think that it will, because there aren't that many updates, and
they're almost all HOT-safe. I'll put the idea of a 50/50 fillfactor
benchmark with the high-contention/regressed workload on my TODO list,
though.

-- 
Peter Geoghegan

Commits

  1. Add "split after new tuple" nbtree optimization.

  2. Add nbtree high key "continuescan" optimization.

  3. Allow amcheck to re-find tuples using new search.

  4. Consider secondary factors during nbtree splits.

  5. Make heap TID a tiebreaker nbtree index column.

  6. Refactor nbtree insertion scankeys.

  7. Redesign the partition dependency mechanism.

  8. Avoid unnecessary palloc overhead in _bt_first(). The temporary