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

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


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 values
  (null),
  ('::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 searched table
\set const `psql -XAtc 'select addr from network order by random() limit 1'`

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';

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

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';

explain analyze select * from network, network_join
  where network.addr && network_join.addr;
explain analyze select * from network, network_join
  where network.addr << network_join.addr;
explain analyze select * from network, network_join
  where network.addr <<= network_join.addr;
explain analyze select * from network, network_join
  where network.addr >> network_join.addr;
explain analyze select * from network, network_join
  where network.addr >>= network_join.addr;
