Re: HOT patch - version 15

Bruce Momjian wrote:
> (Can someone time the access time for following a chain that fills an
> entire page (the worst case) vs. having a single tuple on the page?)

Here is some results, on my laptop. The test case is a table with a
single integer column, and a single row in the table. The row is updated
250 times to make a 251 tuples long chain. Page can hold 255 tuples, so
this is pretty much the worst case. After that, the row is fetched
1000000 times, in a PL/pgSQL function. I ran the tests with
enable_seqscan on and off, see "seqscan" and "idxscan" rows below.
Numbers are the times spent doing the fetches, in seconds. I repeated
each test a few times to make sure that the results are repeatable, they
seem to be repeatable to ~0.1s precision. The test script used is
attached. Autovacuum was disabled, and shared_buffers=320MB, otherwise
all settings were left to defaults.

HEAD HOT HOT-opt HOT-pruned
seqscan 19.9 21.1 20.1 11.5
idxscan 27.8 31.4 30.4 13.7

Explanations of the columns:

HEAD: CVS HEAD
HOT-pruned: CVS HEAD + HOT patch v15
HOT: CVS HEAD + HOT patch v15, but with heap_page_prune_defrag short
circuited to do nothing
HOT-opt: CVS HEAD + HOT patch v15, but with static XidInMVCCSnapshot
like in CVS HEAD

I didn't expect a difference in seqscan performance between HEAD and
HOT. I oprofiled it, and figured out that it's because HOT patch removed
the static-qualifier XidInMVCCSnapshot, because it's needed in
plancat.c. I changed it back to static, dummying out the call in
plancat.c, and the results are now closer to each other (HOT-opt column).

Comparing the idxscan columns, it looks like following the chain *is*
more expensive than having to go through killed index pointers. Pruning
clearly does help.

Given that this test is pretty much the worst case scenario, I'm ok with
not pruning for the purpose of keeping chains short.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com