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
5 rows affected

3 rows affected

2 rows affected

9 rows affected

12 rows affected

25 rows affected

-- 複雑になってしまっている例
-- データアナリスト用システムのみを利用する社員
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
),

/* データアナリスト用システムもほかのロール用システムも利用する社員を取得します。 */
employee_uses_notonly_da_systems as (
  select
    da.Employee
  from
    employee_uses_da_systems as da
      left join EmployeeAppSystems as eas
        on da.Employee = eas.Employee
          left join (select * from RoleAppSystems where Role = 'Data Analyst') as ras
            on eas.AppSystem = ras.AppSystem
  where
    ras.Role is null
  group by
    da.Employee
),

/* データアナリスト用システムを利用する社員から他のロール用システムも利用する社員を除外します。*/
employee_uses_only_da_systems as (
  select
    e.Employee
  from
    employee_uses_da_systems as e
      left join employee_uses_notonly_da_systems as n
        on e.Employee = n.Employee
  where
    n.Employee is null
)

select * from employee_uses_only_da_systems;


employee


tee


SELECT	*
FROM	AppSystems;

SELECT	*
FROM	AppSystemCategories;

SELECT	*
FROM	Employees;

SELECT	*
FROM	EmployeeAppSystems;

SELECT	*
FROM	Roles;

SELECT	* 
FROM	RoleAppSystems;


appsystem
category


Redshift
Tech

Redash
Tech

Jira
Tech

Tableau
Tech

Figma
Tech

Office
Tech

MoneyForward
Biz

Salesforce
Biz

Notion
Biz




category


Tech

Biz




employee


bibi

tee

mimi

lee

min




employee
appsystem


bibi
Redshift

bibi
Redash

bibi
MoneyForward

bibi
Notion

tee
Redshift

tee
Redash

tee
Tableau

tee
MoneyForward

tee
Notion

mimi
Figma

mimi
Jira

mimi
MoneyForward

mimi
Notion

mimi
Tableau

lee
Notion

lee
Salesforce

min
Redshift

min
Redash

min
Jira

min
Tableau

min
Figma

min
Office

min
MoneyForward

min
Salesforce

min
Notion




role


Data Analyst

Engineer

Business




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

appsystem	category
Redshift	Tech
Redash	Tech
Jira	Tech
Tableau	Tech
Figma	Tech
Office	Tech
MoneyForward	Biz
Salesforce	Biz
Notion	Biz

employee	appsystem
bibi	Redshift
bibi	Redash
bibi	MoneyForward
bibi	Notion
tee	Redshift
tee	Redash
tee	Tableau
tee	MoneyForward
tee	Notion
mimi	Figma
mimi	Jira
mimi	MoneyForward
mimi	Notion
mimi	Tableau
lee	Notion
lee	Salesforce
min	Redshift
min	Redash
min	Jira
min	Tableau
min	Figma
min	Office
min	MoneyForward
min	Salesforce
min	Notion

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