On Tue, Jul 23, 2019 at 6:22 PM Peter Geoghegan <p...@bowt.ie> wrote: > Attached is a revised version of your v2 that fixes this issue -- I'll > call this v3.
Remember that index that I said was 5.5x smaller with the patch applied, following retail insertions (a single big INSERT ... SELECT ...)? Well, it's 6.5x faster with this small additional patch applied on top of the v3 I posted yesterday. Many of the indexes in my test suite are about ~20% smaller __in addition to__ very big size reductions. Some are even ~30% smaller than they were with v3 of the patch. For example, the fair use implementation of TPC-H that my test data comes from has an index on the "orders" o_orderdate column, named idx_orders_orderdate, which is made ~30% smaller by the addition of this simple patch (once again, this is following a single big INSERT ... SELECT ...). This change makes idx_orders_orderdate ~3.3x smaller than it is with master/Postgres 12, in case you were wondering. This new patch teaches nbtsplitloc.c to subtract posting list overhead when sizing the new high key for the left half of a candidate split point, since we know for sure that _bt_truncate() will at least manage to truncate away that much from the new high key, even in the worst case. Since posting lists are often very large, this can make a big difference. This is actually just a bugfix, not a new idea -- I merely made nbtsplitloc.c understand how truncation works with posting lists. There seems to be a kind of "synergy" between the nbtsplitloc.c handling of pages that have lots of duplicates and posting list compression. It seems as if the former mechanism "sets up the bowling pins", while the latter mechanism "knocks them down", which is really cool. We should try to gain a better understanding of how that works, because it's possible that it could be even more effective in some cases. -- Peter Geoghegan
0003-Account-for-posting-list-overhead-during-splits.patch
Description: Binary data