some SQL to test JSONB Performance versus first level columns

jsonb_performance_benchmark.sql

--create database testing_json_performance;
--create table test_table (income float, start_period integer, end_period integer, name varchar(50), invitation_only boolean, properties jsonb);
--select * from test_table;
 
--delete from test_table;
-- -- create index
--create index idx_income on test_table (income);
--create index idx_period on test_table (start_period, end_period);
--create index idx_name on test_table (name);
--create index idx_properties_income on test_table using gin ((properties -> 'income') jsonb_path_ops);
--create index idx_properties_period on test_table using gin ((properties -> 'period' -> 'start_period') jsonb_path_ops, (properties -> 'period' -> 'end_period') jsonb_path_ops);
--drop index idx_income;
--drop index idx_properties_income;
--drop index idx_properties_period;

-- -- generate random values
--do $$
--declare
--income float;
--invitation_only boolean;
--start_period integer;
--end_period integer;
--name varchar;
--begin
--for r in 1..100000 loop
--income := random() *100000;
--invitation_only := round(random());
--start_period := random() * 18;
--end_period := random() * 18 + random() * 18;
--name := md5(random()::text);
--
--insert into test_table(income, invitation_only, start_period, end_period, name, properties)
--  values(income, invitation_only, start_period, end_period, name,
--   ('{"income": ' || income || ',"invitation_only":' || invitation_only ||',"name":"'|| name ||'","period":{"start_period":' || start_period ||  ',"end_period":' || end_period || '}}')::JSONB);
--end loop;
--end;
--$$

-- test float
--explain analyze select count(*) from test_table where income >= 1000;
--explain analyze select count(*) from test_table where (properties ->> 'income')::FLOAT >= 1000;
-- test boolean
--explain analyze select count(*) from test_table where invitation_only = true;
--explain analyze select count(*) from test_table where (properties ->> 'invitation_only')::BOOLEAN = true;
-- test integer (with second level nested)
--explain analyze select count(*) from test_table where start_period <= 10 AND end_period >= 18;
--explain analyze select count(*) from test_table
--  where (properties -> 'period' ->> 'start_period')::INTEGER <= 10
--  and  (properties -> 'period' ->> 'end_period')::INTEGER >= 18;
--explain analyze select count(*) from test_table
--where name like '%a%a%b%';
--explain analyze select count(*) from test_table
--where (properties ->> 'name') like '%a%a%b%';

--delete from test_table;

--select count(*) from test_table;
--select * from test_table;
--drop database testing_json_performance;