buffer_usage_queries.sql

application/octet-stream

Filename: buffer_usage_queries.sql

Type: application/octet-stream

Part: 1

Message: Re: Priority table or Cache table

select c.relname, count(*)
	from pg_buffercache bc, pg_class c
	where bc.relfilenode = c.relfilenode and c.relname in ('big_tab', 'small_tab')
	group by c.relname;

explain analyze select * from big_tab;

select c.relname, count(*)
	from pg_buffercache bc, pg_class c
	where bc.relfilenode = c.relfilenode and c.relname in ('big_tab', 'small_tab')
	group by c.relname;

explain analyze select * from small_tab;

select c.relname, count(*)
	from pg_buffercache bc, pg_class c
	where bc.relfilenode = c.relfilenode and c.relname in ('big_tab', 'small_tab')
	group by c.relname;

select scan_small_tab_n_times(1000);

select c.relname, count(*)
	from pg_buffercache bc, pg_class c
	where bc.relfilenode = c.relfilenode and c.relname in ('big_tab', 'small_tab')
	group by c.relname;

select scan_big_tab_n_times(1000);

select c.relname, count(*)
	from pg_buffercache bc, pg_class c
	where bc.relfilenode = c.relfilenode and c.relname in ('big_tab', 'small_tab')
	group by c.relname;

select scan_small_tab_n_times(1000);

select c.relname, count(*)
	from pg_buffercache bc, pg_class c
	where bc.relfilenode = c.relfilenode and c.relname in ('big_tab', 'small_tab')
	group by c.relname;