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

On Fri, Jan 4, 2019 at 8:23 AM Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com> wrote:
> Thanks,
>
> On Sun, Dec 30, 2018 at 3:49 AM John Naylor <jcnaylor(at)gmail(dot)com> wrote:
> > On 12/29/18, Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com> wrote:
> > > Results are execution time(unit ms) taken by copy statement when number of
> > > records equal to exact number which fit HEAP_FSM_CREATION_THRESHOLD = 4
> > > pages. For fill factor 20 it is till tid (3, 43) and for scale factor 70
> > > till tid (3, 157). Result is taken as a median of 10 runs.
> >
> > > So 2-3% consistent regression, And on every run I can see for patch v11
> > > execution time is slightly more than base.
> >
> > Thanks for testing!
> >
> > > I also tried to insert more
> > > records till 8 pages and same regression is observed! So I guess even
> > > HEAP_FSM_CREATION_THRESHOLD = 4 is not perfect!
> >
> > That's curious, because once the table exceeds the threshold, it would
> > be allowed to update the FSM, and in the process write 3 pages that it
> > didn't have to in the 4 page test. The master branch has the FSM
> > already, so I would expect the 8 page case to regress more.
>
> I tested with configuration HEAP_FSM_CREATION_THRESHOLD = 4 and just
> tried to insert till 8 blocks to see if regression is carried on with
> further inserts.
>
> > What I can do later is provide a supplementary patch to go on top of
> > mine that only checks the last block. If that improves performance,
> > I'll alter my patch to only check every other page.
>
> Running callgrind for same test shows below stats
> Before patch
> ==========
> Number of calls function_name
> 2000 heap_multi_insert
> 2000 RelationGetBufferForTuple
> 3500 ReadBufferBI
>
> After Patch
> =========
> Number of calls function_name
> 2000 heap_multi_insert
> 2000 RelationGetBufferForTuple
> 5000 ReadBufferBI
>
> I guess Increase in ReadBufferBI() calls might be the reason which is
> causing regression. Sorry I have not investigated it.
>

I think the reason is that we are checking each block when blocks are
less than HEAP_FSM_CREATION_THRESHOLD. Even though all the blocks are
in memory, there is some cost to check them all. OTOH, without the
patch, even if it accesses FSM, it won't have to make so many
in-memory reads for blocks.

BTW, have you check for scale_factor 80 or 100 as suggested last time?

> I will check
> same with your next patch!
>

Yeah, that makes sense, John, can you provide a patch on top of the
current patch where we check either the last block or every other
block.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com