hash.sql

application/sql

Download

create table t1 (id int, val text);
with t as (select a from generate_series(1,1000000000) s(a)) insert into
t1 select  a, md5(a::text) from t where hashint4(a)::bit(32) &
0xFFFFF000::bit(32) = 0::bit(32);
insert into t1 select i, md5(i::text) from generate_series(1,1000000) s(i);

insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;

create index on t1(val);
cluster t1 using t1_val_idx;

vacuum analyze;

set work_mem = '1MB';
set hash_mem_multiplier = 1.0;

explain analyze select a.*, b.* from t1 a join t1 b on (a.id = b.id);