Postgres 11

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 crime
(
  cr_rating VARCHAR (25) PRIMARY KEY
);



INSERT INTO crime VALUES ('Low'), ('Medium'), ('High');
3 rows affected

SELECT * FROM crime;


cr_rating


Low

Medium

High


CREATE TABLE weather 
(
  weather_type VARCHAR (50) PRIMARY KEY
);
INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane');
8 rows affected

SELECT * FROM weather;


weather_type


Scorching

Sunny

Mild

Overcast

Drizzle

Rainy

Stormy

Hurricane


CREATE TABLE job (job_id SERIAL PRIMARY KEY, job_type VARCHAR (30) NOT NULL);
INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue collar'), ('White collar'), ('Manager'), ('Self-employed');
6 rows affected

SELECT * FROM job;


job_id
job_type


1
Unknown

2
Unemployed

3
Blue collar

4
White collar

5
Manager

6
Self-employed


CREATE TABLE the_location
(

  loc_key SERIAL PRIMARY KEY,
  loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in your system
  loc_status INTEGER CHECK (loc_status IN (0,1)),
  loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10), 
  loc_crime VARCHAR (25) NOT NULL, 
  -- could use a CHECK CONSTRAINT, but use a lookup table instead
  -- note also that the types should match exactly
  loc_weather VARCHAR (50) NOT NULL,
  -- location_rating_tourism, -- same lookup table idea here (Good, Medium, Poor, Death-zone)
  -- location_rating_income, -- list of social classes table
  -- location_rating_jobs
  CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating),
  CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES weather (weather_type)
);
INSERT INTO the_location 
(loc_coords, loc_status, loc_rating, loc_crime, loc_weather) 
VALUES 
('coord_1', 0, 7, 'Medium', 'Rainy'), 
('coord_2', 1, 7, 'High', 'Mild');
2 rows affected

SELECT * FROM the_location;


loc_key
loc_coords
loc_status
loc_rating
loc_crime
loc_weather


1
coord_1
0
7
Medium
Rainy

2
coord_2
1
7
High
Mild


CREATE TABLE location_job
( 
  lj_loc INTEGER NOT NULL,
  lj_job      INTEGER NOT NULL,
  lj_percent  INTEGER DEFAULT 0 NOT NULL,
  PRIMARY KEY (lj_loc, lj_job),
  CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key),
  CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id)
);
INSERT INTO location_job 
VALUES 
(1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing value (1, 1, 10)
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);
11 rows affected

SELECT * FROM location_job;


lj_loc
lj_job
lj_percent


1
2
10

1
3
10

1
4
10

1
5
10

1
6
50

2
1
30

2
2
30

2
3
10

2
4
10

2
5
10

2
6
10


SELECT 
  tl.loc_key, tl.loc_coords, 
  lj.lj_loc, lj.lj_job, lj.lj_percent, 
  j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;


loc_key
loc_coords
lj_loc
lj_job
lj_percent
job_id
job_type


1
coord_1
1
2
10
2
Unemployed

1
coord_1
1
3
10
3
Blue collar

1
coord_1
1
4
10
4
White collar

1
coord_1
1
5
10
5
Manager

1
coord_1
1
6
50
6
Self-employed

2
coord_2
2
1
30
1
Unknown

2
coord_2
2
2
30
2
Unemployed

2
coord_2
2
3
10
3
Blue collar

2
coord_2
2
4
10
4
White collar

2
coord_2
2
5
10
5
Manager

2
coord_2
2
6
10
6
Self-employed


SELECT 
  tl.loc_key, tl.loc_coords, 
  lj.lj_loc, lj.lj_job, lj.lj_percent, 
  j.job_id, j.job_type
FROM job j
LEFT OUTER JOIN location_job lj ON j.job_id = lj.lj_job
LEFT OUTER JOIN the_location tl ON lj.lj_loc = tl.loc_key
ORDER BY tl.loc_key, j.job_id;


loc_key
loc_coords
lj_loc
lj_job
lj_percent
job_id
job_type


1
coord_1
1
2
10
2
Unemployed

1
coord_1
1
3
10
3
Blue collar

1
coord_1
1
4
10
4
White collar

1
coord_1
1
5
10
5
Manager

1
coord_1
1
6
50
6
Self-employed

2
coord_2
2
1
30
1
Unknown

2
coord_2
2
2
30
2
Unemployed

2
coord_2
2
3
10
3
Blue collar

2
coord_2
2
4
10
4
White collar

2
coord_2
2
5
10
5
Manager

2
coord_2
2
6
10
6
Self-employed


SELECT 
  tl.loc_key, tl.loc_coords, 
  lj.lj_loc, lj.lj_job, lj.lj_percent, 
  j.job_id, j.job_type
FROM job j
RIGHT OUTER JOIN location_job lj ON j.job_id = lj.lj_job
RIGHT OUTER JOIN the_location tl ON lj.lj_loc = tl.loc_key
ORDER BY tl.loc_key, j.job_id;



loc_key
loc_coords
lj_loc
lj_job
lj_percent
job_id
job_type


1
coord_1
1
2
10
2
Unemployed

1
coord_1
1
3
10
3
Blue collar

1
coord_1
1
4
10
4
White collar

1
coord_1
1
5
10
5
Manager

1
coord_1
1
6
50
6
Self-employed

2
coord_2
2
1
30
1
Unknown

2
coord_2
2
2
30
2
Unemployed

2
coord_2
2
3
10
3
Blue collar

2
coord_2
2
4
10
4
White collar

2
coord_2
2
5
10
5
Manager

2
coord_2
2
6
10
6
Self-employed


SELECT 
  tl.loc_key, tl.loc_coords, 
  lj.lj_loc, lj.lj_job, lj.lj_percent, 
  j.job_id, j.job_type
FROM job j
FULL OUTER JOIN location_job lj ON j.job_id = lj.lj_job
FULL OUTER JOIN the_location tl ON lj.lj_loc = tl.loc_key
ORDER BY tl.loc_key, j.job_id;


loc_key
loc_coords
lj_loc
lj_job
lj_percent
job_id
job_type


1
coord_1
1
2
10
2
Unemployed

1
coord_1
1
3
10
3
Blue collar

1
coord_1
1
4
10
4
White collar

1
coord_1
1
5
10
5
Manager

1
coord_1
1
6
50
6
Self-employed

2
coord_2
2
1
30
1
Unknown

2
coord_2
2
2
30
2
Unemployed

2
coord_2
2
3
10
3
Blue collar

2
coord_2
2
4
10
4
White collar

2
coord_2
2
5
10
5
Manager

2
coord_2
2
6
10
6
Self-employed

job_id	job_type
1	Unknown
2	Unemployed
3	Blue collar
4	White collar
5	Manager
6	Self-employed

loc_key	loc_coords	lj_loc	lj_job	lj_percent	job_id	job_type
1	coord_1	1	2	10	2	Unemployed
1	coord_1	1	3	10	3	Blue collar
1	coord_1	1	4	10	4	White collar
1	coord_1	1	5	10	5	Manager
1	coord_1	1	6	50	6	Self-employed
2	coord_2	2	1	30	1	Unknown
2	coord_2	2	2	30	2	Unemployed
2	coord_2	2	3	10	3	Blue collar
2	coord_2	2	4	10	4	White collar
2	coord_2	2	5	10	5	Manager
2	coord_2	2	6	10	6	Self-employed