Re: Reduce pinning in btree indexes

Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> [need to check performance more]

It looks like the remaining performance regression was indeed a
result of code alignment. I found two "paranoia" assignments I had
accidentally failed to put back with the rest of the mark/restore
optimization; after that trivial change the patched version is
ever-so-slightly faster than master on all tests.

Average of 3 `make check-world` runs:
master: 336.288 seconds
patch: 332.237 seconds

Average of 6 `make check` runs:
master: 25.409 seconds
patch: 25.270 seconds

The following were all run 12 times, the worst 2 dropped, the rest
averaged:

Kyotaro-san's 1m mark "worst case" benchmark:
master: 962.581 ms, 6.765 stdev
patch: 947.518 ms, 3.584 stdev

Kyotaro-san's 500k mark, 500k restore "worst case" benchmark:
master: 1366.639 ms, 5.314 stdev
patch: 1363.844 ms, 5.896 stdev

pgbench -i -s 16 pgbench / pgbench -c 16 -j 4 -T 500 pgbench
master: 265.011 tps, 4.952 stdev
patch: 267.164 tps, 9.094 stdev

How do people feel about the idea of me pushing this for 9.5 (after
I clean up all the affected comments and README files)? I know
this first appeared in the last CF, but the footprint is fairly
small and the only user-visible behavior change is that a btree
index scan of a WAL-logged table using an MVCC snapshot no longer
blocks a vacuum indefinitely. (If there are objections I will move
this to the first CF for the next release.)

src/backend/access/nbtree/nbtree.c | 50 +++++-------
src/backend/access/nbtree/nbtsearch.c | 141 +++++++++++++++++++++++++---------
src/backend/access/nbtree/nbtutils.c | 58 ++++++++++----
src/include/access/nbtree.h | 36 ++++++++-
4 files changed, 201 insertions(+), 84 deletions(-)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company