\set count 							10000
\set join_count 					1000
\set null_count     				1000
\set duplicate_count 				1000
\set duplicate_distribution 		100
\set statistics						100

--select pg_backend_pid();
--select pg_sleep(10);

drop table if exists network;
drop table if exists network_join;

create table network as
select
  ((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 addr
from
  generate_series(1, :count) as ctr;

create table network_join as
select
  ((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 addr
from
  generate_series(1, :join_count) as ctr;

insert into network
select null
from
  generate_series(1, :null_count) as ctr;

insert into network values
  ('::1'),
  ('::2/64');

-- Duplicate some values to create MCV list
insert into network
select
  nth_value(addr, (random() * :duplicate_distribution)::int + 1)
  		over (order by random())
from
  network,
  generate_series(0, (:duplicate_count / :count)::int) as multiplier
limit :duplicate_count;

insert into network_join
select
  nth_value(addr, (random() * :duplicate_distribution)::int + 1)
  		over (order by random())
from
  network_join,
  generate_series(0, (:duplicate_count / :join_count)::int) as multiplier
limit :duplicate_count;

alter table network
  alter column addr set statistics :statistics;
alter table network_join
  alter column addr set statistics :statistics;

analyze network;
analyze network_join;

select * from pg_stats
  where tablename like 'network%' and attname = 'addr';

-- Random constant from the join table
\set const `psql -XAtc 'select addr from network_join order by random() limit 1'`

\echo
\echo
\echo === Tests with a constant ===
\echo
explain analyze select * from network
  where addr && :'const';
explain analyze select * from network
  where addr << :'const';
explain analyze select * from network
  where addr <<= :'const';
explain analyze select * from network
  where addr >> :'const';
explain analyze select * from network
  where addr >>= :'const';

\echo
\echo
\echo === Inner join tests ===
\echo
explain analyze select * from network
  join network_join on network.addr = network_join.addr;
explain analyze select * from network
  join network_join on network.addr && network_join.addr;
explain analyze select * from network
  join network_join on network.addr << network_join.addr;
explain analyze select * from network
  join network_join on network.addr <<= network_join.addr;
explain analyze select * from network
  join network_join on network.addr >> network_join.addr;
explain analyze select * from network
  join network_join on network.addr >>= network_join.addr;

\echo
\echo
\echo === Left join tests ===
\echo
explain analyze select * from network
  left join network_join on network.addr = network_join.addr;
explain analyze select * from network
  left join network_join on network.addr && network_join.addr;
explain analyze select * from network
  left join network_join on network.addr << network_join.addr;
explain analyze select * from network
  left join network_join on network.addr <<= network_join.addr;
explain analyze select * from network
  left join network_join on network.addr >> network_join.addr;
explain analyze select * from network
  left join network_join on network.addr >>= network_join.addr;

\echo
\echo
\echo === Right join tests ===
\echo
explain analyze select * from network
  right join network_join on network.addr = network_join.addr;
explain analyze select * from network
  right join network_join on network.addr && network_join.addr;
explain analyze select * from network
  right join network_join on network.addr << network_join.addr;
explain analyze select * from network
  right join network_join on network.addr <<= network_join.addr;
explain analyze select * from network
  right join network_join on network.addr >> network_join.addr;
explain analyze select * from network
  right join network_join on network.addr >>= network_join.addr;

\echo
\echo
\echo === Semi-join tests ===
\echo
explain analyze select * from network
  where exists (select 1 from network_join
  	where network.addr = network_join.addr);
explain analyze select * from network
  where exists (select 1 from network_join
  	where network.addr && network_join.addr);
explain analyze select * from network
  where exists (select 1 from network_join
  	where network.addr << network_join.addr);
explain analyze select * from network
  where exists (select 1 from network_join
  	where network.addr <<= network_join.addr);
explain analyze select * from network
  where exists (select 1 from network_join
  	where network.addr >> network_join.addr);
explain analyze select * from network
  where exists (select 1 from network_join
  	where network.addr >>= network_join.addr);

\echo
\echo
\echo === Anti-join tests ===
\echo
explain analyze select * from network
  where not exists (select 1 from network_join
  	where network.addr = network_join.addr);
explain analyze select * from network
  where not exists (select 1 from network_join
  	where network.addr && network_join.addr);
explain analyze select * from network
  where not exists (select 1 from network_join
  	where network.addr << network_join.addr);
explain analyze select * from network
  where not exists (select 1 from network_join
  	where network.addr <<= network_join.addr);
explain analyze select * from network
  where not exists (select 1 from network_join
  	where network.addr >> network_join.addr);
explain analyze select * from network
  where not exists (select 1 from network_join
  	where network.addr >>= network_join.addr);