Also, truncate was faster on direct toast dtoast=# truncate table largedirecttoast; TRUNCATE TABLE Time: 891.392 ms dtoast=# truncate table largeplaintoast; TRUNCATE TABLE Time: 1193.535 ms (00:01.194)
On Mon, Sep 7, 2026 at 10:54 AM Hannu Krosing <[email protected]> wrote: > > The slowness of 1GB datum test in previous email is likely just > because of more levels in toast inex, as it should fit well into > shared buffers > > table_name │ table_size │ toast_table_size │ > toast_index_size │ > ──────────────────────────┼──────────────┼──────────────────┼──────────────────┼ > public.largeplaintoast │ 11527708672 │ 11399487488 │ > 125042688 │ > public.largedirecttoast │ 11495727104 │ 11492515840 │ > 8192 │ > > Time: 8.935 ms > dtoast=# show shared_buffers; > shared_buffers > ──────────────── > 8GB > (1 row) > > On Mon, Sep 7, 2026 at 10:47 AM Hannu Krosing <[email protected]> wrote: > > > > On Mon, Sep 7, 2026 at 9:45 AM Nikita Malakhov <[email protected]> wrote: > > > > > > Hi Hannu! > > > > > > Great you're continuing this work! I've started to review your patch set, > > > and have question about tests and performance: have you tested it against > > > large toasted values? When experimenting with direct toast before my > > > prototype > > > was much faster on small values but starting with relatively large (have > > > to recover > > > previous test results so cannot say exact size), about tens of Mbs, > > > performance > > > starts to degrade and on very large values it is much slower compared to > > > the original > > > mechanics. > > > > Hi, here is a guick tests inserting 10 rows of 1MB, 10MB and 100MB text > > datums > > > > Direct toast is consistently faster by 7 to 15% on writes > > > > datum size | plain | direct | speed-up > > ------------------------------------------- > > 1 MB | 58 | 54 | 7.4% > > 10 MB | 568 | 495 | 14.7% > > 100 MB | 6821 | 6139 | 11.1% > > 1000 MB | 64363 | 58763 | 9.5% > > > > On the read side they are the same when the toast index fits in > > memory, varying less than a percent between runs > > > > select id, length(data) from largedirecttoast where id between 21 and > > 30; -> 19 ms > > select id, length(data) from largedirecttoast where id between 21 and > > 30; -> 183 ms > > select id, length(data) from largedirecttoast where id between 21 and > > 30; -> 1920 ms > > > > For 1GB rows the above select was 2x slower on plain, 47 s vs 23 sec on > > direct > > > > > > Here is what I tested: > > > > dtoast=# create table largedirecttoast(id serial primary key, data > > text storage external) with (toast_flavour=direct); > > CREATE TABLE > > Time: 4.351 ms > > dtoast=# create table largeplaintoast(id serial primary key, data text > > storage external) with (toast_flavour=plain); > > CREATE TABLE > > Time: 4.269 ms > > dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m) > > insert into largeplaintoast(data) select large.datum1m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 58.330 ms > > dtoast=# with large as (SELECT repeat('abcdefghij', 100000) as datum1m) > > insert into largedirecttoast(data) select large.datum1m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 54.280 ms > > dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m) > > insert into largeplaintoast(data) select large.datum10m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 567.808 ms > > dtoast=# with large as (SELECT repeat('abcdefghij', 1000000) as datum10m) > > insert into largedirecttoast(data) select large.datum10m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 494.823 ms > > dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m) > > insert into largeplaintoast(data) select large.datum100m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 6820.868 ms (00:06.821) > > dtoast=# with large as (SELECT repeat('abcdefghij', 10000000) as datum100m) > > insert into largedirecttoast(data) select large.datum100m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 6138.979 ms (00:06.139) > > dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as > > datum1000m) > > insert into largedirecttoast(data) select large.datum1000m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 58763.165 ms (00:58.763) > > dtoast=# with large as (SELECT repeat('abcdefghij', 100000000) as > > datum1000m) > > insert into largeplaintoast(data) select large.datum1000m from large, > > generate_series(1,10); > > INSERT 0 10 > > Time: 64363.909 ms (01:04.364) > > .... > > dtoast=# select id, length(data) from largedirecttoast where id > > between 31 and 40; > > id │ length > > ────┼──────────── > > 31 │ 1000000000 > > 32 │ 1000000000 > > 33 │ 1000000000 > > 34 │ 1000000000 > > 35 │ 1000000000 > > 36 │ 1000000000 > > 37 │ 1000000000 > > 38 │ 1000000000 > > 39 │ 1000000000 > > 40 │ 1000000000 > > (10 rows) > > > > Time: 22981.708 ms (00:22.982) > > dtoast=# select id, length(data) from largeplaintoast where id between > > 31 and 40; > > id │ length > > ────┼──────────── > > 31 │ 1000000000 > > 32 │ 1000000000 > > 33 │ 1000000000 > > 34 │ 1000000000 > > 35 │ 1000000000 > > 36 │ 1000000000 > > 37 │ 1000000000 > > 38 │ 1000000000 > > 39 │ 1000000000 > > 40 │ 1000000000 > > (10 rows) > > > > Time: 47346.034 ms (00:47.346)
