buffer_usage_objects.sql

drop table big_tab;
drop table small_tab;

show shared_buffers;

create table big_tab (val int, val2 int, str varchar);

insert into big_tab select x, x, lpad('string', 100, x::text)
	from generate_series(1, 1000000) x;

create table small_tab as
		select * from big_tab limit 500000;

analyze big_tab;
analyze small_tab;

select relname, relpages from pg_class where relname in ('big_tab', 'small_tab');

select count(*) from pg_buffercache;

create function scan_big_tab_n_times(num_times int) returns void language plpgsql as 
$$
declare
	i int;
begin
	for i in 1 .. num_times loop
		perform count(*) from big_tab;
	end loop;
end;
$$;

create function scan_small_tab_n_times(num_times int) returns void language plpgsql as 
$$
declare
	i int;
begin
	for i in 1 .. num_times loop
		perform count(*) from small_tab;
	end loop;
end;
$$;