Postgres 12

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?.

select version();


version


PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE city
(
  city_id   INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  city_name TEXT NOT NULL,
  city_pop  INTEGER NOT NULL,
  --
  --  more fields
  --
  CONSTRAINT city_pk PRIMARY KEY (city_id),
  CONSTRAINT city_name_uq UNIQUE (city_name) -- might not always be the case,
    -- just have 'Dublin (Ire.)' and ('Dublin (Cal.)') for example
);
CREATE TABLE language
(
  lang_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
  lang_name  TEXT NOT NULL,
  
  CONSTRAINT language_pk PRIMARY KEY (lang_id),
  CONSTRAINT lang_name_uq UNIQUE (lang_name)
);
CREATE TABLE city_language
(
  c_id     INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  l_id     INTEGER NOT NULL,
  c_l_prob DOUBLE PRECISION NOT NULL,
  
  CONSTRAINT city_language_pk PRIMARY KEY (c_id, l_id)
  
);
CREATE TABLE city_lang_jp_combo
(

  c_l_combo_set INTEGER NOT NULL,

  c_id      INTEGER NOT NULL,
  l_id      INTEGER NOT NULL,
  
  c_l_combo_jp DOUBlE PRECISION NOT NULL,
  
  CONSTRAINT c_l_combo_id_pk PRIMARY KEY (c_l_combo_set, c_id, l_id),
  
  CONSTRAINT c_l_combo__jp_exists_fk FOREIGN KEY (c_id, l_id)
    REFERENCES city_language (c_id, l_id)
    
    -- if this doesn't exist, then how can you have a triple combination?
);
INSERT INTO city (city_id, city_name, city_pop) VALUES
(101, 'Dublin', 500000), (102, 'Paris', 2100000), (103, 'Rome', 3000000), (104, 'Madrid', 3250000);
4 rows affected

INSERT INTO language (lang_id, lang_name) VALUES
(1111, 'Java'), (2222, 'C'), (3333, 'C++'), (4444, 'Python'), (5555, 'Nim'), (6666, 'Clojure');
6 rows affected

INSERT INTO city_language (c_id, l_id, c_l_prob) VALUES 

(101, 1111, 0.91),  (101, 2222, 0.92),  (101, 3333, 0.93),  (101, 4444, 0.94),
(102, 1111, 0.81),  (102, 2222, 0.82),  (102, 3333, 0.83),  (102, 4444, 0.84),
(103, 1111, 0.751), (103, 2222, 0.752), (103, 3333, 0.753), (103, 4444, 0.754),
(104, 1111, 0.701), (104, 2222, 0.702), (104, 3333, 0.703), (104, 4444, 0.704),
(101, 5555, 0.2), (101, 6666, 0.10);
18 rows affected

INSERT INTO city_lang_jp_combo VALUES

 (1, 101, 1111, 0.61), 
 (1, 101, 2222, 0.61), 
 (1, 101, 3333, 0.61),

 (2, 102, 1111, 0.62),
 (2, 102, 2222, 0.62),
 (2, 102, 3333, 0.62),

 (3, 103, 1111, 0.63),
 (3, 103, 2222, 0.63),
 (3, 103, 3333, 0.63),

 (4, 104, 1111, 0.64),
 (4, 104, 2222, 0.64),
 (4, 104, 3333, 0.64),
 
 (5, 101, 1111, 0.51),
 (5, 101, 2222, 0.51),
 (5, 101, 4444, 0.51),

 (6, 102, 1111, 0.52),
 (6, 102, 2222, 0.52),
 (6, 102, 4444, 0.52),


 (7, 103, 1111, 0.53),
 (7, 103, 2222, 0.53),
 (7, 103, 4444, 0.53),

 (8, 104, 1111, 0.54),
 (8, 104, 2222, 0.54),
 (8, 104, 4444, 0.54),

 (9, 101, 2222, 0.41),
 (9, 101, 3333, 0.41),
 (9, 101, 4444, 0.41),

(10, 102, 2222, 0.42),
(10, 102, 3333, 0.42),
(10, 102, 4444, 0.42),

(11, 103, 2222, 0.43),
(11, 103, 3333, 0.43),
(11, 103, 4444, 0.43),

(12, 104, 2222, 0.44),
(12, 104, 3333, 0.44),
(12, 104, 4444, 0.44),


(13, 101, 1111, 0.25),  -- <<== 4 langauge combo - Java, C, C++ & Python
(13, 101, 2222, 0.25),
(13, 101, 3333, 0.25),
(13, 101, 4444, 0.25),

(14, 101, 5555, 0.05),  -- <=== 2 language combo - Nim and Clojure
(14, 101, 6666, 0.05);


--
-- Now, consider cl_combo_set = 777 below - it's 
-- essentially a duplicate of the cl_combo_set = 1
--
-- But the problem is, how does one enforce uniqueness over a set of
-- records?
--


-- (777, 101, 1111, 0.33),
-- (777, 101, 2222, 0.33),
-- (777, 101, 3333, 0.33),

-- 
-- BUT, if I add a 4th
-- record to the set, the UNIQUE constraint no longer applies because
-- we've added a 4th language---
--
-- say, like this:
--
-- (888, 101, 1111, 0.15),
-- (888, 101, 2222, 0.15),
-- (888, 101, 3333, 0.15),
-- (888, 101, 5555, 0.15)
--
--
-- The p-Value of a combination of languages must be **AT MOST** <= the 
-- p-Value of the lowest p-Value for any of the languages - how to enforce?
--
--
-- For example, SQL (appears to me anyway) to be "tacked on" to virtually
-- every job ad - so, you might have (for Dublin - city code 101) -
-- SQL code say 
--
-- Now, this is a valid set of records for the 3 languages Java, C & C++
-- (999, 101, 1111, 0.33),
-- (999, 101, 2222, 0.33),
-- (999, 101, 3333, 0.33);
--
-- and say, SQL was language code 7777, one could have:
--
-- (1000, 101, 1111, 0.33),
-- (1000, 101, 2222, 0.33),
-- (1000, 101, 3333, 0.33),
-- (1000, 101, 7777, 0.33);
--
-- this would **_ALSO_** be a valid set of records for Dublin - difficlt
-- to enforce uniqueness constraints for this and also the CHECK that the
-- p-Value is less than or equal to (at most) the p-Value for any of them.


42 rows affected

SELECT 
  c.city_name AS cname, 
  cljc.c_l_combo_set AS combo, 
  cljc.c_id AS cid, 
  l.lang_name AS ln,
  cljc.c_l_combo_jp AS prob
FROM  city_lang_jp_combo cljc
JOIN language l 
  ON l.lang_id = cljc.l_id
JOIN city c
  ON c.city_id = cljc.c_id
ORDER BY cname, combo;


cname
combo
cid
ln
prob


Dublin
1
101
C++
0.61

Dublin
1
101
Java
0.61

Dublin
1
101
C
0.61

Dublin
5
101
Python
0.51

Dublin
5
101
C
0.51

Dublin
5
101
Java
0.51

Dublin
9
101
Python
0.41

Dublin
9
101
C++
0.41

Dublin
9
101
C
0.41

Dublin
13
101
Java
0.25

Dublin
13
101
C
0.25

Dublin
13
101
C++
0.25

Dublin
13
101
Python
0.25

Dublin
14
101
Nim
0.05

Dublin
14
101
Clojure
0.05

Madrid
4
104
C
0.64

Madrid
4
104
Java
0.64

Madrid
4
104
C++
0.64

Madrid
8
104
Java
0.54

Madrid
8
104
C
0.54

Madrid
8
104
Python
0.54

Madrid
12
104
C++
0.44

Madrid
12
104
Python
0.44

Madrid
12
104
C
0.44

Paris
2
102
Java
0.62

Paris
2
102
C
0.62

Paris
2
102
C++
0.62

Paris
6
102
Java
0.52

Paris
6
102
C
0.52

Paris
6
102
Python
0.52

Paris
10
102
C
0.42

Paris
10
102
C++
0.42

Paris
10
102
Python
0.42

Rome
3
103
C++
0.63

Rome
3
103
Java
0.63

Rome
3
103
C
0.63

Rome
7
103
Java
0.53

Rome
7
103
C
0.53

Rome
7
103
Python
0.53

Rome
11
103
Python
0.43

Rome
11
103
C++
0.43

Rome
11
103
C
0.43


SELECT 
  t.cname AS "City name", 
  STRING_AGG(t.ln, ', ' ORDER BY t.cname, t.combo, ln) AS "Languages", 
  t.prob AS "Probability"
FROM
(
  SELECT 
    c.city_name AS cname, 
    cljc.c_l_combo_set AS combo, 
    cljc.c_id AS cid, 
    l.lang_name AS ln,
    cljc.c_l_combo_jp AS prob
  FROM  city_lang_jp_combo cljc
  JOIN language l 
    ON l.lang_id = cljc.l_id
  JOIN city c
    ON c.city_id = cljc.c_id
  -- ORDER BY cname, combo
) AS t
GROUP BY t.cname, t.combo, t.prob
ORDER BY "City name", "Languages";


City name
Languages
Probability


Dublin
C, C++, Java
0.61

Dublin
C, C++, Java, Python
0.25

Dublin
C, C++, Python
0.41

Dublin
C, Java, Python
0.51

Dublin
Clojure, Nim
0.05

Madrid
C, C++, Java
0.64

Madrid
C, C++, Python
0.44

Madrid
C, Java, Python
0.54

Paris
C, C++, Java
0.62

Paris
C, C++, Python
0.42

Paris
C, Java, Python
0.52

Rome
C, C++, Java
0.63

Rome
C, C++, Python
0.43

Rome
C, Java, Python
0.53

cname	combo	cid	ln	prob
Dublin	1	101	C++	0.61
Dublin	1	101	Java	0.61
Dublin	1	101	C	0.61
Dublin	5	101	Python	0.51
Dublin	5	101	C	0.51
Dublin	5	101	Java	0.51
Dublin	9	101	Python	0.41
Dublin	9	101	C++	0.41
Dublin	9	101	C	0.41
Dublin	13	101	Java	0.25
Dublin	13	101	C	0.25
Dublin	13	101	C++	0.25
Dublin	13	101	Python	0.25
Dublin	14	101	Nim	0.05
Dublin	14	101	Clojure	0.05
Madrid	4	104	C	0.64
Madrid	4	104	Java	0.64
Madrid	4	104	C++	0.64
Madrid	8	104	Java	0.54
Madrid	8	104	C	0.54
Madrid	8	104	Python	0.54
Madrid	12	104	C++	0.44
Madrid	12	104	Python	0.44
Madrid	12	104	C	0.44
Paris	2	102	Java	0.62
Paris	2	102	C	0.62
Paris	2	102	C++	0.62
Paris	6	102	Java	0.52
Paris	6	102	C	0.52
Paris	6	102	Python	0.52
Paris	10	102	C	0.42
Paris	10	102	C++	0.42
Paris	10	102	Python	0.42
Rome	3	103	C++	0.63
Rome	3	103	Java	0.63
Rome	3	103	C	0.63
Rome	7	103	Java	0.53
Rome	7	103	C	0.53
Rome	7	103	Python	0.53
Rome	11	103	Python	0.43
Rome	11	103	C++	0.43
Rome	11	103	C	0.43

City name	Languages	Probability
Dublin	C, C++, Java	0.61
Dublin	C, C++, Java, Python	0.25
Dublin	C, C++, Python	0.41
Dublin	C, Java, Python	0.51
Dublin	Clojure, Nim	0.05
Madrid	C, C++, Java	0.64
Madrid	C, C++, Python	0.44
Madrid	C, Java, Python	0.54
Paris	C, C++, Java	0.62
Paris	C, C++, Python	0.42
Paris	C, Java, Python	0.52
Rome	C, C++, Java	0.63
Rome	C, C++, Python	0.43
Rome	C, Java, Python	0.53