vacuum-test-lookup-int.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 int);

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

vacuum 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();

-- to match 38 million deletes in UUID test:
-- 22.6 million
delete from test where ctid <  '(100000,0)'::tid;
-- the rest
--with d as (select * from test order by ctid desc limit 38000000 - 22600000) 
--delete from test where exists 
--(select 1 from d where test.x = d.x);
delete from test where ctid > '(4356637,38)'::tid;

\timing
vacuum (verbose, truncate off) test;