\timing on

\set count 10000

select pg_backend_pid();


create table rand as
select
  ctr as pk,
  box(point(random() / 10000, random() / 10000),
	  point(random() / 10000, random() / 10000)) as box
from
  generate_series(1, :count) ctr;

insert into rand values
  (-1, null),
  (-2, '(-infinity,-infinity)(infinity,infinity)');


create table ref_results as
select
  a.pk,
  a.box,
  (select count(*) from rand as b where a.box && b.box) as n_ov,
  (select count(*) from rand as b where a.box <@ b.box) as n_contained,
  (select count(*) from rand as b where a.box @> b.box) as n_contains,
  (select count(*) from rand as b where a.box << b.box) as n_left,
  (select count(*) from rand as b where a.box &< b.box) as n_overleft,
  (select count(*) from rand as b where a.box >> b.box) as n_right,
  (select count(*) from rand as b where a.box &> b.box) as n_overright,
  (select count(*) from rand as b where a.box <<| b.box) as n_above,
  (select count(*) from rand as b where a.box &<| b.box) as n_overabove,
  (select count(*) from rand as b where a.box |>> b.box) as n_below,
  (select count(*) from rand as b where a.box |&> b.box) as n_overbelow,
  (select count(*) from rand as b where a.box ~= b.box) as n_same
from
  rand as a;

set maintenance_work_mem = '1GB';

create index on rand using spgist (box);

set enable_seqscan TO 0;


create table test_results as
select
  a.pk,
  a.box,
  (select count(*) from rand as b where a.box && b.box) as n_ov,
  (select count(*) from rand as b where a.box <@ b.box) as n_contained,
  (select count(*) from rand as b where a.box @> b.box) as n_contains,
  (select count(*) from rand as b where a.box << b.box) as n_left,
  (select count(*) from rand as b where a.box &< b.box) as n_overleft,
  (select count(*) from rand as b where a.box >> b.box) as n_right,
  (select count(*) from rand as b where a.box &> b.box) as n_overright,
  (select count(*) from rand as b where a.box <<| b.box) as n_above,
  (select count(*) from rand as b where a.box &<| b.box) as n_overabove,
  (select count(*) from rand as b where a.box |>> b.box) as n_below,
  (select count(*) from rand as b where a.box |&> b.box) as n_overbelow,
  (select count(*) from rand as b where a.box ~= b.box) as n_same
from
  rand as a;

set enable_seqscan TO 1;

select * from ref_results r join test_results t using (pk)
where
  r.n_ov <> t.n_ov OR
  r.n_contained <> t.n_contained OR
  r.n_contains <> t.n_contains OR
  r.n_left <> t.n_left OR
  r.n_overleft <> t.n_overleft OR
  r.n_right <> t.n_right OR
  r.n_overright <> t.n_overright OR
  r.n_above <> t.n_above OR
  r.n_overabove <> t.n_overabove OR
  r.n_below <> t.n_below OR
  r.n_overbelow <> t.n_overbelow OR
  r.n_same <> t.n_same;
