Postgres 9.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE Employees (
  Employee 
    VARCHAR(30) NOT NULL 
    PRIMARY KEY
  );

CREATE TABLE Roles	(
  Role 
    VARCHAR(30) NOT NULL
    PRIMARY KEY
  );

CREATE TABLE AppSystemCategories	(
  Category
    VARCHAR(30) NOT NULL
    PRIMARY KEY
  );

CREATE TABLE AppSystems	(
  AppSystem
    VARCHAR(30) NOT NULL
    PRIMARY KEY,
  Category
    VARCHAR(30) NOT NULL
    REFERENCES AppSystemCategories(Category)
  );

CREATE TABLE EmployeeAppSystems	(
  Employee
    VARCHAR(30) NOT NULL
    REFERENCES Employees(Employee),
  AppSystem
    VARCHAR(30) NOT NULL
    REFERENCES AppSystems(AppSystem),
  PRIMARY KEY (Employee, AppSystem)
  );

CREATE TABLE RoleAppSystems	(
  Role
    VARCHAR(30) NOT NULL
    REFERENCES Roles(Role),
  AppSystem
    VARCHAR(30) NOT NULL
    REFERENCES AppSystems(AppSystem),
  PRIMARY KEY (Role, AppSystem)
  );

INSERT INTO Employees(Employee)
VALUES ('bibi'),('tee'),('mimi'),('lee'), ('min');

INSERT INTO Roles(Role)
VALUES ('Data Analyst'), ('Engineer'), ('Business');

INSERT INTO AppSystemCategories(Category)
VALUES ('Tech'), ('Biz');

INSERT INTO AppSystems(AppSystem, Category)
VALUES	('Redshift','Tech'), ('Redash','Tech'), ('Jira','Tech'), ('Tableau','Tech'), ('Figma','Tech'), ('Office','Tech'),
    ('MoneyForward','Biz'), ('Salesforce','Biz'), ('Notion','Biz');

INSERT INTO RoleAppSystems(Role, AppSystem)
VALUES	('Data Analyst','Redshift'), ('Data Analyst','Redash'), ('Data Analyst','Tableau'), ('Data Analyst','MoneyForward'), ('Data Analyst','Notion'),
    ('Engineer','Figma'), ('Engineer','Jira'), ('Engineer','MoneyForward'), ('Engineer','Notion'),
    ('Business','Notion'), ('Business','MoneyForward'), ('Business','Office');
    -- MoneyForward and Notion are used by all roles

INSERT INTO EmployeeAppSystems(Employee, AppSystem)
VALUES	('bibi','Redshift'), ('bibi','Redash'), ('bibi','MoneyForward'), ('bibi','Notion'), 
    -- Partial match for Data Analyst, match for Biz systems
    ('tee','Redshift'), ('tee','Redash'), ('tee','Tableau'), ('tee','MoneyForward'), ('tee','Notion'), 
    -- Perfect match for Data Analyst
    ('mimi','Figma'), ('mimi','Jira'), ('mimi','MoneyForward'), ('mimi','Notion'), ('mimi','Tableau'), 
    -- Over qualified for Engineer (uses extra systems)
    ('lee','Notion'), ('lee','Salesforce'), 
    -- No tech skills
    ('min','Redshift'), ('min','Redash'), ('min','Jira'), ('min','Tableau'), ('min','Figma'), ('min','Office'), ('min','MoneyForward'), ('min','Salesforce'), ('min','Notion'); 
    -- min Has it all
    -- all candidates use notion
INSERT 0 25

-- 複雑になってしまっている例
-- データアナリスト用システムを利用する社員
with
/* データアナリスト用システムを取得して、
   全ての社員と cross join して直積結合を作ります。*/
data_analyst_systems as (
  select
    *
  from
  (select
    distinct(AppSystem) as DataAnalystAppSystems
  from
    RoleAppSystems
  where
    Role = 'Data Analyst'
  ) as app_systems
  cross join
    Employees
),

/* データアナリスト用システムを使わない社員を取得します。*/
employee_not_using_da_systems as (
  select
    das.Employee
  from
    data_analyst_systems as das
      left join EmployeeAppSystems as eas
        on eas.AppSystem = das.DataAnalystAppSystems
        and eas.Employee = das.Employee
  where
    eas.Employee is null
  group by
    das.Employee
),

/* 全ての社員からデータアナリスト用システムを使わない社員を除外します。*/
employee_uses_da_systems as (
  select
    e.Employee
  from
    Employees as e
      left join employee_not_using_da_systems as n
        on e.Employee = n.Employee
  where
    n.Employee is null
)
select * from employee_uses_da_systems;


employee


tee

min


SELECT 2

SELECT	*
FROM	AppSystems;

SELECT	*
FROM	AppSystemCategories;

SELECT	*
FROM	Employees;

SELECT	*
FROM	EmployeeAppSystems;

SELECT	*
FROM	Roles;

SELECT	* 
FROM	RoleAppSystems;


role
appsystem


Data Analyst
Redshift

Data Analyst
Redash

Data Analyst
Tableau

Data Analyst
MoneyForward

Data Analyst
Notion

Engineer
Figma

Engineer
Jira

Engineer
MoneyForward

Engineer
Notion

Business
Notion

Business
MoneyForward

Business
Office


SELECT 12

`role`	`appsystem`
`Data Analyst`	`Redshift`
`Data Analyst`	`Redash`
`Data Analyst`	`Tableau`
`Data Analyst`	`MoneyForward`
`Data Analyst`	`Notion`
`Engineer`	`Figma`
`Engineer`	`Jira`
`Engineer`	`MoneyForward`
`Engineer`	`Notion`
`Business`	`Notion`
`Business`	`MoneyForward`
`Business`	`Office`

`employee`
`tee`
`min`