Postgres Pro
Downloads
Home   >   mailing lists

Re: "Write amplification" is made worse by "getting tired" whileinserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters) - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: "Write amplification" is made worse by "getting tired" whileinserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)
Date
Msg-id CANP8+j+KLgY8Gt7HaMbHNhb4GEaN7dMCy4Jg3_unATvwi6HtcA@mail.gmail.com
Whole thread Raw
In response to Re: "Write amplification" is made worse by "getting tired" whileinserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)  (Peter Geoghegan <pg@bowt.ie>)
Responses Re: "Write amplification" is made worse by "getting tired" whileinserting into nbtree secondary indexes (Was: Why B-Tree suffix truncation matters)
List pgsql-hackers
Tree view
On 2 August 2018 at 21:32, Peter Geoghegan <pg@bowt.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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pgsql-hackers by date:

Previous
From: Yugo Nagata
Date:
Subject: Re: pg_verify_checksums failure with hash indexes
Next
From: Peter Eisentraut
Date:
Subject: Re: some more error location support