Re: Avoiding smgrimmedsync() during nbtree index builds

On Mon, May 3, 2021 at 5:24 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> So, I've written a patch which avoids doing the immediate fsync for
> index builds either by using shared buffers or by queueing sync requests
> for the checkpointer. If a checkpoint starts during the index build and
> the backend is not using shared buffers for the index build, it will
> need to do the fsync.

I've attached a rebased version of the patch (old patch doesn't apply).

With the patch applied (compiled at O2), creating twenty empty tables in
a transaction with a text column and an index on another column (like in
the attached SQL [make a test_idx schema first]) results in a fairly
consistent 15-30% speedup on my laptop (timings still in tens of ms -
avg 50 ms to avg 65 ms so run variation affects the % a lot).
Reducing the number of fsync calls from 40 to 1 was what likely causes
this difference.

- Melanie