-- Schemas
---- Show all schemas
SELECT schema_name FROM information_schema.schemata;

--Create a new schema
CREATE SCHEMA census;

--Create a table with us census
DROP TABLE census.uscensus;
CREATE TABLE census.uscensus (
		id INT primary key not null,
		age INT,
		workclass VARCHAR(20),
		fnlwgt INT,
		education VARCHAR(15),
		educationnum	INT,
		maritalstatus VARCHAR(25),
		occupation VARCHAR(20),
		relationship VARCHAR(20),
		race VARCHAR(20),
		sex VARCHAR(10),
		capitalgain INT,
		capitalloss INT,
		workhours INT,
		country VARCHAR(30),
		income VARCHAR(10));

--The insertion was make with a sql script

--Query with '=' operator to compare b-tree and hash
explain analyse select * from census.uscensus where age='90';
--Query with combinations
explain analyse select count(*) from census.uscensus where age between 20 and 40;
--Query with pattern matching operator
explain analyse select * from census.uscensus where maritalstatus like 'Marr%';

--Create b-tree indexes
create index age_idx on census.uscensus(age);
create index marital_idx on census.uscensus(maritalstatus);
--Create a hash index on age column
create index age_hash_idx on census.uscensus using hash (age);
--This index will not be used
create index marital_hash_idx on census.uscensus using hash (maritalstatus);

--Drop indexes
drop index census.age_idx;
drop index census.marital_idx;
drop index census.age_hash_idx;

--See all indexes created
select indexname, indexdef FROM pg_indexes WHERE schemaname = 'census';


--Query for GIN and GiST
explain analyse select count(*) from census.uscensus where occupation ilike '%the%';   --ilike is like but case-insensitive
select count(*) from census.uscensus where occupation ilike '%the%';

--Create GIN index
create extension if not exists pg_trgm;
create index gin_idx on census.uscensus using gin (occupation gin_trgm_ops);
--Create GiST index
create index gist_idx on census.uscensus using gist (occupation gist_trgm_ops);

--Drop indexes
drop index census.gin_idx;
drop index census.gist_idx;

--Query for multicolumn index
explain analyse select count(*) from census.uscensus where age='35' and education='Bachelors';

--Create multicolumn index
create index age_edu_idx on census.uscensus(age, education);
drop index census.age_edu_idx;

select count(*) from census.uscensus;