Horton

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

John Naylor <jcnaylor@gmail.com>

View thread View on postgr.es
From: John Naylor <jcnaylor@gmail.com>
To: Mithun Cy <mithun.cy@enterprisedb.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2018-12-29T22:19:58Z
Lists: pgsql-hackers 
On 12/29/18, Mithun Cy <mithun.cy@enterprisedb.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.

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.

-John Naylor

Commits

  1. Improve code comments in b0eaa4c51b.

  2. During pg_upgrade, conditionally skip transfer of FSMs.

  3. Add more tests for FSM.

  4. Doc: Update the documentation for FSM behavior for small tables.

  5. Make FSM test portable.

  6. Avoid creation of the free space map for small heap relations, take 2.

  7. Move page initialization from RelationAddExtraBlocks() to use, take 2.

  8. Avoid creation of the free space map for small heap relations.

  9. In bootstrap mode, don't allow the creation of files if they don't already