schema.sql

-- =============================================================
-- Section 09 — Analyse Queries (EXPLAIN / EXPLAIN ANALYZE)
-- =============================================================
-- Reuses the same idea as Section 08 — a 300k-row employee table
-- — but lives in its own schema so the two sections don't collide.
-- =============================================================

DROP SCHEMA IF EXISTS adv_explain CASCADE;
CREATE SCHEMA adv_explain;
SET search_path TO adv_explain;

CREATE TABLE employee (
    id           BIGSERIAL PRIMARY KEY,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(255) NOT NULL,
    department   VARCHAR(50)  NOT NULL,
    country      CHAR(2)      NOT NULL,
    salary       INTEGER      NOT NULL,
    is_active    BOOLEAN      NOT NULL,
    hired_on     DATE         NOT NULL
);

CREATE TABLE department (
    name      VARCHAR(50) PRIMARY KEY,
    head_name VARCHAR(100) NOT NULL
);

INSERT INTO department VALUES
('engineering','Alice Engineer'),
('sales','Bob Sales'),
('marketing','Carol Marketing'),
('support','Dave Support'),
('finance','Eve Finance'),
('people','Frank People'),
('design','Gina Design'),
('data','Hugo Data');

INSERT INTO employee (first_name, last_name, email, department, country, salary, is_active, hired_on)
SELECT
    (ARRAY['Ada','Alan','Grace','Linus','Margaret','Dennis','Katherine','Radia','Barbara','Edsger'])[1 + (n % 10)],
    (ARRAY['Lovelace','Turing','Hopper','Torvalds','Hamilton','Ritchie','Johnson','Perlman','Liskov','Dijkstra'])[1 + ((n / 10) % 10)],
    'user' || n || '@example.com',
    (ARRAY['engineering','sales','marketing','support','finance','people','design','data'])[1 + (n % 8)],
    (ARRAY['GB','US','FR','DE','ES','IT','NL','PL','PT','SE'])[1 + (n % 10)],
    30000 + (n % 120) * 500,
    (n % 20) <> 0,
    DATE '2015-01-01' + ((n % 3650) || ' days')::INTERVAL
FROM generate_series(1, 300000) AS n;

ANALYZE employee;
ANALYZE department;