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

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


-- 全てのシステムを利用する社員
-- 利用するシステム以外の全てのシステムはない社員
select
  e.Employee
from
  Employees AS e
where	
  NOT EXISTS (
      -- 全ての利用されるシステム
      select
        a.AppSystem
      from
        AppSystems AS a
    EXCEPT
      -- 利用するシステム以外
      select
        eas.AppSystem
      from
        EmployeeAppSystems AS eas
      where
        eas.Employee = e.Employee
  )


employee


min

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