\timing on

\set count 10000


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

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


create table ref_results as
select
  a.pk,
  a.box,
  (select count(*) from ran as b where a.box && b.box) as n_ov,
  (select count(*) from ran as b where a.box <@ b.box) as n_sub,
  (select count(*) from ran as b where a.box @> b.box) as n_sup,
  (select count(*) from ran as b where a.box << b.box) as n_left,
  (select count(*) from ran as b where a.box >> b.box) as n_right,
  (select count(*) from ran as b where a.box <<| b.box) as n_up,
  (select count(*) from ran as b where a.box |>> b.box) as n_down
from
  ran as a;

set maintenance_work_mem = '1GB';

create index on ran using spgist (box);

set enable_seqscan TO 0;


create table test_results as
select
  a.pk,
  a.box,
  (select count(*) from ran as b where a.box && b.box) as n_ov,
  (select count(*) from ran as b where a.box <@ b.box) as n_sub,
  (select count(*) from ran as b where a.box @> b.box) as n_sup,
  (select count(*) from ran as b where a.box << b.box) as n_left,
  (select count(*) from ran as b where a.box >> b.box) as n_right,
  (select count(*) from ran as b where a.box <<| b.box) as n_up,
  (select count(*) from ran as b where a.box |>> b.box) as n_down
from
  ran 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_sub <> t.n_sub OR
  r.n_sup <> t.n_sup OR
  r.n_left <> t.n_left OR
  r.n_right <> t.n_right OR
  r.n_up <> t.n_up OR
  r.n_down <> t.n_down
;
