Re: Optimising compactify_tuples()

On Wed, 16 Sep 2020 at 02:10, Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)com> wrote:
> BTW: this message "redo done at 0/9749FF70 system usage: CPU: user: 13.46 s, system: 0.78 s, elapsed: 14.25 s" is priceless addition :)

Thanks a lot for the detailed benchmark results and profiles. That was
useful. I've pushed both patches now. I did a bit of a sweep of the
comments on the 0001 patch before pushing it.

I also did some further performance tests of something other than
recovery. I can also report a good performance improvement in VACUUM.
Something around the ~25% reduction mark

psql -c "drop table if exists t1;" postgres > /dev/null
psql -c "create table t1 (a int primary key, b int not null) with
(autovacuum_enabled = false, fillfactor = 85);" postgres > /dev/null
psql -c "insert into t1 select x,0 from generate_series(1,10000000)
x;" postgres > /dev/null
psql -c "drop table if exists log_wal;" postgres > /dev/null
psql -c "create table log_wal (lsn pg_lsn not null);" postgres > /dev/null
psql -c "insert into log_wal values(pg_current_wal_lsn());" postgres > /dev/null
pgbench -n -f update.sql -t 60000 -c 200 -j 200 -M prepared postgres
psql -c "select 'Used ' ||
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), lsn)) || ' of
WAL' from log_wal limit 1;" postgres
psql postgres

\timing on
VACUUM t1;

Fillfactor = 85

patched:

Time: 2917.515 ms (00:02.918)
Time: 2944.564 ms (00:02.945)
Time: 3004.136 ms (00:03.004)

master:
Time: 4050.355 ms (00:04.050)
Time: 4104.999 ms (00:04.105)
Time: 4158.285 ms (00:04.158)

Fillfactor = 100

Patched:

Time: 4245.676 ms (00:04.246)
Time: 4251.485 ms (00:04.251)
Time: 4247.802 ms (00:04.248)

Master:
Time: 5459.433 ms (00:05.459)
Time: 5917.356 ms (00:05.917)
Time: 5430.986 ms (00:05.431)

David