renage merge test

\timing
create table r as
  select int4range(g, g+10) ir, g g
  from generate_series(1,1000000) g
  order by random();

create index r_idx on r using gist (ir);

create table s as
  select int4range(g+5, g+15) ir,g g
  from generate_series(1,1000000) g
  order by random();

create index s_idx on s using gist (ir);

vacuum analyze r;
vacuum analyze s;

--baseline performance using GiST
set enable_mergejoin=false;
explain analyze
  select * from r, s where r.ir && s.ir;
explain analyze
  select * from r, s where r.ir && s.ir;

--performance without GiST
set enable_mergejoin=true;
explain analyze
  select * from r, s where r.ir && s.ir;
explain analyze
  select * from r, s where r.ir && s.ir;

--performance in presence of expression index
create index r_idx1 on r(int4range(r.g, r.g+10));
create index s_idx1 on s(int4range(s.g+5, s.g+15));


explain analyze
  select * from r, s where int4range(r.g, r.g+10) && int4range(s.g+5, s.g+15);
explain analyze
  select * from r, s where int4range(r.g, r.g+10) && int4range(s.g+5, s.g+15);


--performance in precence of direct B-tree index
create index r_idx2 on r(ir);
create index s_idx2 on s(ir);

explain analyze
  select * from r, s where r.ir && s.ir;
explain analyze
  select * from r, s where r.ir && s.ir;
 

drop table r;
drop table s;

--here we test that performance is not affected by payload of other attributes in heap tuples
create table r as
  select int4range(g, g+10) ir, g g, 1::float pl1,1::float pl2,1::float pl3,1::float pl4,1::float pl5,1::float pl6,1::float pl7,1::float pl8,1::float pl9,1::float pl0
  from generate_series(1,1000000) g
  order by random();

create table s as
  select int4range(g+5, g+15) ir,g g, 1::float pl1,1::float pl2,1::float pl3,1::float pl4,1::float pl5,1::float pl6,1::float pl7,1::float pl8,1::float pl9,1::float pl0
  from generate_series(1,1000000) g
  order by random();


explain analyze
  select r.ir,s.ir from r, s where r.ir && s.ir;
explain analyze
  select r.ir,s.ir from r, s where r.ir && s.ir;
 

drop table r;
drop table s;