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

From: John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP: Avoid creation of the free space map for small tables
Date: 2019-01-19 23:49:08
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

On Sat, Jan 19, 2019 at 8:06 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Thu, Jan 17, 2019 at 11:13 PM John Naylor
> Few more comments:
> 1.
> I think we should not allow to create FSM for toast tables as well
> till there size reaches HEAP_FSM_CREATION_THRESHOLD. If you try below
> test, you can see that FSM will be created for the toast table even if
> the size of toast relation is 1 page.
> I have fixed this in the attached patch, kindly verify it once and see
> if you can add the test for same as well.

Works for me. For v16, I've added and tested similar logic to
pg_upgrade and verified that toast tables work the same as normal
tables in recovery. I used a slightly different method to generate the
long random string to avoid creating a function. Also, some cosmetic
adjustments -- I changed the regression test to use 'i' instead of 'g'
to match the use of generate_series in most other tests, and made
capitalization more consistent.

> 2.
> -CREATE TABLE test1 (a int, b int);
> -INSERT INTO test1 VALUES (16777217, 131584);
> +CREATE TABLE test_rel_forks (a
> int);
> +-- Make sure there are enough blocks in the heap for the FSM to be created.
> +INSERT INTO test_rel_forks SELECT g
> from generate_series(1,10000) g;
> -VACUUM test1; -- set up FSM
> +-- set up FSM and VM
> +VACUUM test_rel_forks;
> This test will create 45 pages instead of 1. I know that to create
> FSM, we now need more than 4 pages, but 45 seems to be on the higher
> side. I think we should not unnecessarily populate more data if there
> is no particular need for it, let's restrict the number of pages to 5
> if possible.

Good idea, done here and in the fsm regression test.

> 3.
> -SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
> - fsm_1
> --------
> - 8192
> -(1 row)
> -
> -SELECT octet_length
> (get_raw_page('test1', 'vm', 0)) AS vm_0;
> +SELECT octet_length(get_raw_page('test_rel_forks', 'fsm', 10)) AS fsm_10;
> +ERROR: block number 10 is out of range for relation "test_rel_forks"
> Why have you changed the test definition here? Previously test checks
> the existing FSM page, but now it tries to access out of range page.

The patch is hard to read here, but I still have a test for the
existing FSM page:

-SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
+SELECT octet_length(get_raw_page('test_rel_forks', 'main', 100)) AS main_100;
+ERROR: block number 100 is out of range for relation "test_rel_forks"
+SELECT octet_length(get_raw_page('test_rel_forks', 'fsm', 0)) AS fsm_0;
(1 row)

I have a test for in-range and out-of-range for each relation fork.

John Naylor
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v16-0001-Avoid-creation-of-the-free-space-map-for-small-h.patch text/x-patch 39.5 KB
v16-0002-During-pg_upgrade-conditionally-skip-transfer-of.patch text/x-patch 7.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Donald Dong 2019-01-19 23:59:13 Re: Ryu floating point output patch
Previous Message Alexander Korotkov 2019-01-19 23:45:10 Re: jsonpath