\timing on

\set count 10000

drop table if exists random_ips;

create table random_ips as
select
  ctr as pk,
  ((random()*255.4)::int::text || '.' ||
   (random()*255.4)::int::text || '.' ||
   (random()*255.4)::int::text || '.' ||
   (random()*255.4)::int::text || '/' ||
   (random()*32.4)::int::text)::inet as inet
from
  generate_series(1, :count) ctr;

insert into random_ips values
  (-1, null),
  (-2, '::1'),
  (-3, '::2/64');

drop table if exists flat_ips;

create table flat_ips as
select
  ctr as pk,
  ((random()*255.4)::int::text || '.' ||
   (random()*255.4)::int::text || '.' ||
   (random()*255.4)::int::text || '.' ||
   (random()*255.4)::int::text || '/32')::inet as inet
from
  generate_series(1, :count) ctr;

insert into flat_ips values
  (-1, null),
  (-2, '::1'),
  (-3, '::2/64');

drop table if exists ref_results;

create table ref_results as
select
  a.pk,
  a.inet,
  (select count(*) from random_ips b where a.inet = b.inet) as n_eq,
  (select count(*) from random_ips b where a.inet <> b.inet) as n_ne,
  (select count(*) from random_ips b where a.inet < b.inet) as n_lt,
  (select count(*) from random_ips b where a.inet <= b.inet) as n_le,
  (select count(*) from random_ips b where a.inet > b.inet) as n_gt,
  (select count(*) from random_ips b where a.inet >= b.inet) as n_ge,
  (select count(*) from random_ips b where a.inet && b.inet) as n_ov,
  (select count(*) from random_ips b where a.inet << b.inet) as n_sub,
  (select count(*) from random_ips b where a.inet <<= b.inet) as n_subeq,
  (select count(*) from random_ips b where a.inet >> b.inet) as n_sup,
  (select count(*) from random_ips b where a.inet >>= b.inet) as n_supeq
from
  random_ips a;

set maintenance_work_mem = '1GB';

create index on random_ips using gist (inet inet_ops);

create index on flat_ips using gist (inet inet_ops);

set enable_seqscan TO 0;

drop table if exists test_results;

create table test_results as
select
  a.pk,
  a.inet,
  (select count(*) from random_ips b where a.inet = b.inet) as n_eq,
  (select count(*) from random_ips b where a.inet <> b.inet) as n_ne,
  (select count(*) from random_ips b where a.inet < b.inet) as n_lt,
  (select count(*) from random_ips b where a.inet <= b.inet) as n_le,
  (select count(*) from random_ips b where a.inet > b.inet) as n_gt,
  (select count(*) from random_ips b where a.inet >= b.inet) as n_ge,
  (select count(*) from random_ips b where a.inet && b.inet) as n_ov,
  (select count(*) from random_ips b where a.inet << b.inet) as n_sub,
  (select count(*) from random_ips b where a.inet <<= b.inet) as n_subeq,
  (select count(*) from random_ips b where a.inet >> b.inet) as n_sup,
  (select count(*) from random_ips b where a.inet >>= b.inet) as n_supeq
from
  random_ips a;

set enable_seqscan TO 1;

select * from ref_results r join test_results t using (pk)
where
  r.n_eq <> t.n_eq OR
  r.n_ne <> t.n_ne OR
  r.n_lt <> t.n_lt OR
  r.n_le <> t.n_le OR
  r.n_gt <> t.n_gt OR
  r.n_ge <> t.n_ge OR
  r.n_ov <> t.n_ov OR
  r.n_sub <> t.n_sub OR
  r.n_subeq <> t.n_subeq OR
  r.n_sup <> t.n_sup OR
  r.n_supeq <> t.n_supeq
;

-- these are for performance testing
set enable_hashjoin to 0;
set enable_mergejoin to 0;
set enable_material to 0;

explain analyze select count(*) from random_ips a, random_ips b
  where a.inet = b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet <> b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet < b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet <= b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet > b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet >= b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet && b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet << b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet <<= b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet >> b.inet;
explain analyze select count(*) from random_ips a, random_ips b
  where a.inet >>= b.inet;

explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet = b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet <> b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet < b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet <= b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet > b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet >= b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet && b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet << b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet <<= b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet >> b.inet;
explain analyze select count(*) from random_ips a, flat_ips b
  where a.inet >>= b.inet;

set enable_hashjoin to 1;
set enable_mergejoin to 1;
set enable_material to 1;