benchmark_post.sql

-- Drop existing tables if they exist
drop table if exists employee_projects cascade;
drop table if exists employees cascade;
drop table if exists projects cascade;

-- Create employees table
create table employees (
  id bigint generated by default as identity primary key
);

-- Create projects table
create table projects (
  id bigint generated by default as identity primary key,
  name varchar not null,
  project_type varchar,
  project_id bigint
);

-- Create employee_projects table to link employees and projects
create table employee_projects (
  employee_id bigint references employees,
  project_id bigint references projects
);

-- Create index for faster query performance
create index project_id_index
  on employee_projects (project_id);

--- Fake generate data
-- Insert 50,000 employees
insert into employees
select from generate_series(1,50000);

-- Insert 100,000 projects
insert into projects (
  name,
  project_type,
  project_id
)
select
  (array['development', 'research', 'maintenance'])[floor(random() * 3 + 1)],
  null,
  null
from generate_series(1,50000);

insert into projects (
  name,
  project_type,
  project_id
)
select
  'special_task',
  'Department',
  floor(random() * 1000 + 1)
from generate_series(1,50000);

-- Create 500,000 connections between employees and projects
-- Start with 225,000 random general projects
insert into employee_projects (
  employee_id,
  project_id
)
select
  floor(random() * 50000 + 1),
  floor(random() * 50000 + 1)
from generate_series(1,225000);

-- Then 50,000 for the special task projects
insert into employee_projects (
  employee_id,
  project_id
)
select
  floor(random() * 50000 + 1),
  floor(50000 + g.id)
from generate_series(1,50000) as g(id);

-- Then another 225,000 random general projects
insert into employee_projects (
  employee_id,
  project_id
)
select
  floor(random() * 50000 + 1),
  floor(random() * 50000 + 1)
from generate_series(1,225000);


-- Analyze tables for query optimization
analyze employee_projects;
analyze projects;
analyze employees;