vacuum-test-lookup-uuid.sql

-- try to stress tid lookup:
-- 1. vacuum freeze first to reduce heap scan time
-- 2. delete some records at the beginning and end of heap to defeat binary search's pre-check

--alter system set shared_buffers ='4GB';
alter system set max_wal_size ='10GB';
alter system set checkpoint_timeout ='24h';
alter system set autovacuum = off;
alter system set maintenance_work_mem = '1GB';
select pg_reload_conf();

show shared_buffers;

drop table if exists test;
create unlogged table if not exists test (x uuid);

--insert into test (x) select gen_random_uuid() from generate_series(1,1000);--*1000*1000);
insert into test (x) select gen_random_uuid() from generate_series(1,1000*1000*1000);
select pg_size_pretty(pg_table_size('test'));

vacuum freeze test;

create index on test (x);
select pg_size_pretty(pg_total_relation_size('test'));

select max(ctid) from test;

--select * from pg_backend_pid();

delete from test where ctid <  '(100000,0)'::tid;
delete from test where ctid > '(5300000,0)'::tid;

\timing
vacuum (verbose, truncate off) test;