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),
(13, 101, 2222, 0.25),
(13, 101, 3333, 0.25),
(13, 101, 4444, 0.25),

(14, 101, 5555, 0.05),
(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


EXPLAIN (ANALYZE, BUFFERS)
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 t.cname, "Languages";


QUERY PLAN


Sort  (cost=336.45..340.70 rows=1700 width=76) (actual time=0.216..0.218 rows=14 loops=1)

  Sort Key: c.city_name, (string_agg(l.lang_name, ', '::text ORDER BY c.city_name, cljc.c_l_combo_set, l.lang_name))

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=3

  ->  GroupAggregate  (cost=202.74..245.24 rows=1700 width=76) (actual time=0.133..0.197 rows=14 loops=1)

        Group Key: c.city_name, cljc.c_l_combo_set, cljc.c_l_combo_jp

        Buffers: shared hit=3

        ->  Sort  (cost=202.74..206.99 rows=1700 width=76) (actual time=0.114..0.119 rows=42 loops=1)

              Sort Key: c.city_name, cljc.c_l_combo_set, cljc.c_l_combo_jp

              Sort Method: quicksort  Memory: 28kB

              Buffers: shared hit=3

              ->  Hash Join  (cost=75.58..111.52 rows=1700 width=76) (actual time=0.056..0.083 rows=42 loops=1)

                    Hash Cond: (cljc.c_id = c.city_id)

                    Buffers: shared hit=3

                    ->  Hash Join  (cost=38.58..70.05 rows=1700 width=48) (actual time=0.039..0.055 rows=42 loops=1)

                          Hash Cond: (cljc.l_id = l.lang_id)

                          Buffers: shared hit=2

                          ->  Seq Scan on city_lang_jp_combo cljc  (cost=0.00..27.00 rows=1700 width=20) (actual time=0.005..0.009 rows=42 loops=1)

                                Buffers: shared hit=1

                          ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.014..0.014 rows=6 loops=1)

                                Buckets: 2048  Batches: 1  Memory Usage: 17kB

                                Buffers: shared hit=1

                                ->  Seq Scan on language l  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.006..0.007 rows=6 loops=1)

                                      Buffers: shared hit=1

                    ->  Hash  (cost=22.00..22.00 rows=1200 width=36) (actual time=0.013..0.014 rows=4 loops=1)

                          Buckets: 2048  Batches: 1  Memory Usage: 17kB

                          Buffers: shared hit=1

                          ->  Seq Scan on city c  (cost=0.00..22.00 rows=1200 width=36) (actual time=0.009..0.010 rows=4 loops=1)

                                Buffers: shared hit=1

Planning Time: 0.336 ms

Execution Time: 0.257 ms


--
-- Old query...
--

SELECT 
  DISTINCT
  ty.cn AS city,
  STRING_AGG(ty.lang2, ', ' ORDER BY ty.cn, ty.lang1, ty.lang2) AS sa,
  ty.prob
FROM
(
  SELECT
    cl.c_id,
    cl.l_id,
    tx.cs, tx.cid,
    tx.l1, tx.l2,
    tx.cn, tx.lang1, tx.lang2, tx.prob
  FROM 
    city_language cl
  JOIN
  (
    SELECT
    c1.c_l_combo_set AS cs,
    c1.c_id AS cid,
    c1.l_id AS l1,
    c1.c_l_combo_jp AS prob,
    c2.l_id AS l2,
    c.city_name  AS cn,
    l1.lang_name AS lang1,
    l2.lang_name AS lang2
  FROM city_lang_jp_combo c1
  JOIN city c
    ON c1.c_id = c.city_id
  JOIN language l1
    ON c1.l_id = l1.lang_id
  JOIN city_lang_jp_combo c2
    ON c1.c_l_combo_set = c2.c_l_combo_set -- AND c1.l_id != c2.l_id
  JOIN language l2 
    ON c2.l_id = l2.lang_id
--ORDER BY c1.c_l_combo_set, c1.l_id, c2.l_id

  ) AS tx
  ON cl.c_id = tx.cid AND cl.l_id = tx.l1 -- AND cl.l_id != tx.l2;
  ORDER BY tx.cs, cl.c_id, cl.l_id, tx.l1, tx.l2
) AS ty
GROUP BY city, ty.lang1, ty.prob
ORDER BY city, sa


city
sa
prob


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


EXPLAIN (ANALYZE, BUFFERS)
SELECT 
  DISTINCT
  ty.cn AS city,
  STRING_AGG(ty.lang2, ', ' ORDER BY ty.cn, ty.lang1, ty.lang2) AS sa,
  ty.prob
FROM
(
  SELECT
    cl.c_id,
    cl.l_id,
    tx.cs, tx.cid,
    tx.l1, tx.l2,
    tx.cn, tx.lang1, tx.lang2, tx.prob
  FROM 
    city_language cl
  JOIN
  (
    SELECT
    c1.c_l_combo_set AS cs,
    c1.c_id AS cid,
    c1.l_id AS l1,
    c1.c_l_combo_jp AS prob,
    c2.l_id AS l2,
    c.city_name  AS cn,
    l1.lang_name AS lang1,
    l2.lang_name AS lang2
  FROM city_lang_jp_combo c1
  JOIN city c
    ON c1.c_id = c.city_id
  JOIN language l1
    ON c1.l_id = l1.lang_id
  JOIN city_lang_jp_combo c2
    ON c1.c_l_combo_set = c2.c_l_combo_set -- AND c1.l_id != c2.l_id
  JOIN language l2 
    ON c2.l_id = l2.lang_id
--ORDER BY c1.c_l_combo_set, c1.l_id, c2.l_id

  ) AS tx
  ON cl.c_id = tx.cid AND cl.l_id = tx.l1 -- AND cl.l_id != tx.l2;
  ORDER BY tx.cs, cl.c_id, cl.l_id, tx.l1, tx.l2
) AS ty
GROUP BY city, ty.lang1, ty.prob
ORDER BY city, sa


QUERY PLAN


Unique  (cost=3091.16..3105.61 rows=1445 width=104) (actual time=0.983..1.000 rows=14 loops=1)

  Buffers: shared hit=7

  ->  Sort  (cost=3091.16..3094.77 rows=1445 width=104) (actual time=0.983..0.990 rows=42 loops=1)

        Sort Key: ty.cn, (string_agg(ty.lang2, ', '::text ORDER BY ty.cn, ty.lang1, ty.lang2)), ty.prob

        Sort Method: quicksort  Memory: 28kB

        Buffers: shared hit=7

        ->  GroupAggregate  (cost=2816.63..3015.32 rows=1445 width=104) (actual time=0.695..0.914 rows=42 loops=1)

              Group Key: ty.cn, ty.lang1, ty.prob

              Buffers: shared hit=7

              ->  Sort  (cost=2816.63..2852.76 rows=14450 width=104) (actual time=0.664..0.678 rows=128 loops=1)

                    Sort Key: ty.cn, ty.lang1, ty.prob

                    Sort Method: quicksort  Memory: 35kB

                    Buffers: shared hit=7

                    ->  Subquery Scan on ty  (cost=1637.60..1818.23 rows=14450 width=104) (actual time=0.473..0.513 rows=128 loops=1)

                          Buffers: shared hit=7

                          ->  Sort  (cost=1637.60..1673.73 rows=14450 width=128) (actual time=0.472..0.485 rows=128 loops=1)

                                Sort Key: c1.c_l_combo_set, cl.c_id, cl.l_id, c2.l_id

                                Sort Method: quicksort  Memory: 41kB

                                Buffers: shared hit=7

                                ->  Hash Join  (cost=306.64..639.19 rows=14450 width=128) (actual time=0.233..0.364 rows=128 loops=1)

                                      Hash Cond: (c2.l_id = l2.lang_id)

                                      Buffers: shared hit=7

                                      ->  Merge Join  (cost=268.07..562.57 rows=14450 width=92) (actual time=0.198..0.289 rows=128 loops=1)

                                            Merge Cond: (c2.c_l_combo_set = c1.c_l_combo_set)

                                            Buffers: shared hit=6

                                            ->  Index Only Scan using c_l_combo_id_pk on city_lang_jp_combo c2  (cost=0.15..73.65 rows=1700 width=8) (actual time=0.021..0.054 rows=42 loops=1)

                                                  Heap Fetches: 42

                                                  Buffers: shared hit=2

                                            ->  Sort  (cost=267.91..272.16 rows=1700 width=88) (actual time=0.173..0.186 rows=127 loops=1)

                                                  Sort Key: c1.c_l_combo_set

                                                  Sort Method: quicksort  Memory: 28kB

                                                  Buffers: shared hit=4

                                                  ->  Hash Join  (cost=131.82..176.70 rows=1700 width=88) (actual time=0.108..0.152 rows=42 loops=1)

                                                        Hash Cond: (c1.l_id = l1.lang_id)

                                                        Buffers: shared hit=4

                                                        ->  Hash Join  (cost=93.25..133.65 rows=1700 width=56) (actual time=0.075..0.107 rows=42 loops=1)

                                                              Hash Cond: (c1.c_id = c.city_id)

                                                              Buffers: shared hit=3

                                                              ->  Hash Join  (cost=56.25..92.17 rows=1700 width=28) (actual time=0.046..0.067 rows=42 loops=1)

                                                                    Hash Cond: ((c1.c_id = cl.c_id) AND (c1.l_id = cl.l_id))

                                                                    Buffers: shared hit=2

                                                                    ->  Seq Scan on city_lang_jp_combo c1  (cost=0.00..27.00 rows=1700 width=20) (actual time=0.005..0.010 rows=42 loops=1)

                                                                          Buffers: shared hit=1

                                                                    ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.021..0.022 rows=18 loops=1)

                                                                          Buckets: 2048  Batches: 1  Memory Usage: 17kB

                                                                          Buffers: shared hit=1

                                                                          ->  Seq Scan on city_language cl  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.009 rows=18 loops=1)

                                                                                Buffers: shared hit=1

                                                              ->  Hash  (cost=22.00..22.00 rows=1200 width=36) (actual time=0.013..0.013 rows=4 loops=1)

                                                                    Buckets: 2048  Batches: 1  Memory Usage: 17kB

                                                                    Buffers: shared hit=1

                                                                    ->  Seq Scan on city c  (cost=0.00..22.00 rows=1200 width=36) (actual time=0.006..0.007 rows=4 loops=1)

                                                                          Buffers: shared hit=1

                                                        ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.021..0.021 rows=6 loops=1)

                                                              Buckets: 2048  Batches: 1  Memory Usage: 17kB

                                                              Buffers: shared hit=1

                                                              ->  Seq Scan on language l1  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.006..0.008 rows=6 loops=1)

                                                                    Buffers: shared hit=1

                                      ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.019..0.019 rows=6 loops=1)

                                            Buckets: 2048  Batches: 1  Memory Usage: 17kB

                                            Buffers: shared hit=1

                                            ->  Seq Scan on language l2  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.009..0.011 rows=6 loops=1)

                                                  Buffers: shared hit=1

Planning Time: 2.466 ms

Execution Time: 1.168 ms
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