test.sql

application/sql

Download

drop table small, large;

BEGIN;

create table small (id bigint, val text);
create table large (id bigint, val text);

insert into small select 1000000000 * random(), md5(i::text)
from generate_series(1, 10000) s(i);

insert into large select 1000000000 * random(), md5(i::text)
from generate_series(1, 700000000) s(i);

COMMIT;

vacuum analyze large;
vacuum analyze small;
alter table large set (autovacuum_enabled = no);
alter table small set (autovacuum_enabled = no);

update pg_class set (relpages, reltuples) = (1000000, 1)
where relname = 'large';

update pg_class set (relpages, reltuples) = (1, 1000000)
where relname = 'small';

checkpoint;

explain analyze select * from small join large using (id);