test.sql

application/sql

Download

-- -c shared_buffers=16MB -c max_connections=4,
-- gives MaxProportionalBuffers=44

create extension if not exists pg_buffercache;

create or replace function uncache(name text) returns boolean
begin atomic
  select bool_and(pg_buffercache_evict(bufferid))
    from pg_buffercache
   where relfilenode = pg_relation_filenode(name::regclass);
end;

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;

create table t1 as select generate_series(1, 100000);
create table t2 as select generate_series(1, 100000);
create table t3 as select generate_series(1, 100000);

select uncache('t1');
select uncache('t2');
select uncache('t3');

set io_combine_limit = 32;

do
$$
declare
  c1 cursor for select * from t1;
  c2 cursor for select * from t2;
  c3 cursor for select * from t3;
  x record;
  t1_relfilenode oid := pg_relation_filenode('t1');
  t2_relfilenode oid := pg_relation_filenode('t2');
  t3_relfilenode oid := pg_relation_filenode('t3');
  t1_buffers int;
  t2_buffers int;
  t3_buffers int;
  total int;
  highest int := 0;
begin
  open c1;
  open c2;
  open c3;
  loop
    fetch next from c1 into x;
    exit when not found;
    fetch next from c2 into x;
    exit when not found;
    fetch next from c3 into x;
    exit when not found;
    if random() < 0.05 then
      -- random sampling because otherwise it's too slow...
      select into t1_buffers, t2_buffers, t3_buffers
             count(*) filter (where relfilenode = t1_relfilenode),
             count(*) filter (where relfilenode = t2_relfilenode),
             count(*) filter (where relfilenode = t3_relfilenode)
        from pg_buffercache
       where pinning_backends > 0;
      total := t1_buffers + t2_buffers + t3_buffers;
      if total > highest then
        highest := total;
        raise notice 'pin counts: % + % + % = %',
              t1_buffers,
              t2_buffers,
              t3_buffers,
              total;
      end if;
    end if;
  end loop;
end;
$$;