Re: "Write amplification" is made worse by "getting tired" while inserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)

On 2 August 2018 at 21:32, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

If I want to sort on heap TID as a tie-breaker, I cannot cut any
> corners. That is, it has to be just another column, at least as far as
> the implementation is concerned (heap TID will have a different
> representation in internal pages and leaf high keys, but nonetheless
> it's essentially just another column in the keyspace). This means that
> if I don't have suffix truncation, I'll regress performance in many
> important cases that have no compensating benefit (e.g. pgbench).
> There is no point in trying to assess that.
>

If you include heap TID as a column the suffix will be unique and cannot
benefit from suffix truncation.

It would be better to avoid including the heap TID as a column, since it is
already there. Or the other way around, don't include it as payload if it
is has to be a column.

Alternatively, include only the heap block number. That would make it
non-unique, but never more than 240 duplicates. So it would allow suffix
truncation, and yet still avoid the multi-page split effect.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services