Re: WIP: Avoid creation of the free space map for small tables

On 10/31/18, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> It seems important to me that before anybody thinks
> about committing this, we construct some kind of destruction case
> where repeated scans of the whole table are triggered as frequently as
> possible, and then run that test with varying thresholds. I might be
> totally wrong, but I bet with a value as large as 32 you will be able
> to find cases where it regresses in a big way.

Here's an attempt at a destruction case: Lobotomize the heap insert
logic such that it never checks the cached target block and has to
call the free space logic for every single insertion, like this:

index ff13c03083..5d5b36af29 100644
--- a/src/backend/access/heap/hio.c
+++ b/src/backend/access/heap/hio.c
@@ -377,7 +377,7 @@ RelationGetBufferForTuple(Relation relation, Size len,
else if (bistate && bistate->current_buf != InvalidBuffer)
targetBlock = BufferGetBlockNumber(bistate->current_buf);
else
- targetBlock = RelationGetTargetBlock(relation);
+ targetBlock = InvalidBlockNumber;

if (targetBlock == InvalidBlockNumber && use_fsm)
{

(with the threshold patch I had to do additional work)
With the small tuples used in the attached v2 test, this means the
free space logic is called ~225 times per block. The test tables are
pre-filled with one tuple and vacuumed so that the FSMs are already
created when testing the master branch. The patch branch is compiled
with a threshold of 8, but testing inserts of 4 pages will effectively
simulate a threshold of 4, etc. As before, trimmed average of 10 runs,
loading to 100 tables each:

# blocks master patch
2 25.1ms 30.3ms
4 40.7ms 48.1ms
6 56.6ms 64.7ms
8 73.1ms 82.0ms

Without this artificial penalty, the 8 block case was about 50ms for
both branches. So if I calculated right, of that 50 ms, master is
spending ~0.10ms looking for free space, and the patch is spending
about ~0.15ms. So, from that perspective, the difference is trivial.
Of course, this is a single client, so not entirely realistic. I think
that shared buffer considerations are most important for deciding the
threshold.

> We also need to think about what happens on the standby, where the FSM
> is updated in a fairly different way.

Were you referring to performance or just functionality? Because the
threshold works on the standby, but I don't know about the performance
there.

-John Naylor