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.0 (Debian 12.0-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE author
(
  author_id SERIAL NOT NULL PRIMARY KEY,
  author_name TEXT NOT NULL,
  author_name_no SMALLINT NOT NULL, -- to take account of authors with the same
                                   -- name - happens a lot, I've done a small
                                   -- bit of work in this area
  -- other author details - institution, nationality, dob, honours...
  -- maybe separate table? Institutions/Organisations/Consortia?
  CONSTRAINT author_name_name_no_uq UNIQUE (author_id, author_name_no)
);
INSERT INTO author (author_name, author_name_no) 
VALUES ('Paul', 1), ('Jim',  1), ('Mary', 1), ('Fred', 1); 
SELECT * FROM author ORDER BY author_id;
4 rows affected



author_id
author_name
author_name_no


1
Paul
1

2
Jim
1

3
Mary
1

4
Fred
1


CREATE TABLE subject
(
  subject_id SERIAL NOT NULL PRIMARY KEY,
  subject_name TEXT NOT NULL
);

INSERT INTO subject (subject_name)  -- only 4 for brevity in example
VALUES
('Genetics'), ('Maths'), ('Economics'), ('Comp_Sci'); 
SELECT * FROM subject ORDER BY subject_id;
4 rows affected



subject_id
subject_name


1
Genetics

2
Maths

3
Economics

4
Comp_Sci


CREATE TABLE work
(
  work_id SERIAL NOT NULL PRIMARY KEY,
  work_title TEXT NOT NULL,
  work_type TEXT NOT NULL  -- 'journal', 'book', 'newspaper article'...
                           -- This can get very complicated with 'chapter', 'section'
                           -- and lots of other stuff. Maybe other reference table?
  -- other work details... 
  -- deliberately not putting in DATE for example, this fiddle is complex enough
);  
INSERT INTO work (work_title, work_type) 
VALUES 
('G_1', 'Book'), ('G_2', 'Journal'), 
('M_1', 'Book'), ('M_2', 'Journal'), ('M_3', 'Journal'),
('E_1', 'Book'),
('CS_1', 'Book'), ('CS_2', 'Book'), ('CS_3', 'Journal'),
('G_CS_1', 'Book'), ('G_CS_2', 'Journal'), ('G_CS_3', 'Journal'),
('G_M_1', 'Journal'), ('G_M_2', 'Journal'),
('M_E_1', 'Book'), ('M_E_2', 'Book'), ('M_E_3', 'Journal'),
('E_CS_1', 'Journal'), ('E_CS_2', 'Book'), ('E_CS_3', 'Journal'),
('G_M_CS_1', 'Book');
SELECT * FROM work ORDER BY work_id;
21 rows affected



work_id
work_title
work_type


1
G_1
Book

2
G_2
Journal

3
M_1
Book

4
M_2
Journal

5
M_3
Journal

6
E_1
Book

7
CS_1
Book

8
CS_2
Book

9
CS_3
Journal

10
G_CS_1
Book

11
G_CS_2
Journal

12
G_CS_3
Journal

13
G_M_1
Journal

14
G_M_2
Journal

15
M_E_1
Book

16
M_E_2
Book

17
M_E_3
Journal

18
E_CS_1
Journal

19
E_CS_2
Book

20
E_CS_3
Journal

21
G_M_CS_1
Book


CREATE TABLE work_subject -- obivously a work can have many areas, note that all
                          -- have 1 or 2 except work 21 which has 3
(
  work_id INTEGER NOT NULL,
  subject_id INTEGER NOT NULL,
  CONSTRAINT ws_pk PRIMARY KEY (work_id, subject_id),
  CONSTRAINT ws_work_id_fk FOREIGN KEY (work_id) REFERENCES work (work_id),
  CONSTRAINT ws_subject_id_fk FOREIGN KEY (subject_id) REFERENCES subject (subject_id)
);
INSERT INTO work_subject VALUES
(1, 1), (2, 1), (3, 2), (4, 2), (5, 2), (6, 3), (7, 4), (8, 4), (9, 4), (10, 1),
(10, 4), (11, 1), (11, 4), (12, 1), (12, 2), (13, 1), (13, 2), (14, 1), (14, 2),
(15, 2), (15, 3), (16, 2), (16, 3), (17, 2), (17, 3), (18, 3), (18, 4), (19, 3),
(19, 4), (20, 3), (20, 4), (21, 1), (21, 2), (21, 4);
SELECT * FROM work_subject;
34 rows affected



work_id
subject_id


1
1

2
1

3
2

4
2

5
2

6
3

7
4

8
4

9
4

10
1

10
4

11
1

11
4

12
1

12
2

13
1

13
2

14
1

14
2

15
2

15
3

16
2

16
3

17
2

17
3

18
3

18
4

19
3

19
4

20
3

20
4

21
1

21
2

21
4


CREATE TABLE work_author  -- obviously a work can have many authors
(
  work_id INTEGER NOT NULL,
  author_id INTEGER NOT NULL,
  CONSTRAINT wa_pk PRIMARY KEY (work_id, author_id),
  CONSTRAINT wa_work_id_fk FOREIGN KEY (work_id) REFERENCES work (work_id),
  CONSTRAINT wa_author_id_fk FOREIGN KEY (author_id) REFERENCES author (author_id)
);
INSERT INTO work_author
VALUES
(1, 1), (2, 1), (2, 3), (3, 3), (4, 3), (4, 4), (5, 3), (5, 4), (6, 4), (7, 2),
(8, 2), (9, 1), (9, 2), (10, 1), (11, 1), (11, 2), (12, 1), (12, 2), (13, 3),
(14, 1), (14, 3), (15, 2), (15, 4), (16, 2), (17, 4), (18, 2), (18, 4),
(19, 4), (20, 2), (21, 1), (21, 2), (21, 3);
SELECT * FROM work_author;
32 rows affected



work_id
author_id


1
1

2
1

2
3

3
3

4
3

4
4

5
3

5
4

6
4

7
2

8
2

9
1

9
2

10
1

11
1

11
2

12
1

12
2

13
3

14
1

14
3

15
2

15
4

16
2

17
4

18
2

18
4

19
4

20
2

21
1

21
2

21
3


SELECT COUNT(*) FROM work_subject ws
WHERE ws.subject_id = 1;


count


8


SELECT COUNT(*) FROM work_subject ws
WHERE ws.subject_id = 
(
  SELECT s.subject_id FROM subject s 
  WHERE subject_name = 'Genetics'
);


count


8


-- What are the names of authors and their works which are in 
-- either Genetics or Computer Science?

SELECT DISTINCT author_id, waw, author_name, work_title, work_type
FROM
(
  SELECT
    wa.work_id AS waw, wa.author_id, 
    ws.work_id AS wsw, ws.subject_id,
    a.author_name, 
    w.work_title,
    w.work_type
  FROM work_author  wa 
  JOIN work_subject ws
    ON wa.work_id = ws.work_id
  JOIN author a
    ON wa.author_id = a.author_id
  JOIN work w
    ON ws.work_id = w.work_id
  WHERE ws.subject_id = 1 OR ws.subject_id = 4
  ORDER BY wa.work_id, wa.author_id
) AS tab_1
ORDER BY waw, author_id, work_title;


author_id
waw
author_name
work_title
work_type


1
1
Paul
G_1
Book

1
2
Paul
G_2
Journal

3
2
Mary
G_2
Journal

2
7
Jim
CS_1
Book

2
8
Jim
CS_2
Book

1
9
Paul
CS_3
Journal

2
9
Jim
CS_3
Journal

1
10
Paul
G_CS_1
Book

1
11
Paul
G_CS_2
Journal

2
11
Jim
G_CS_2
Journal

1
12
Paul
G_CS_3
Journal

2
12
Jim
G_CS_3
Journal

3
13
Mary
G_M_1
Journal

1
14
Paul
G_M_2
Journal

3
14
Mary
G_M_2
Journal

2
18
Jim
E_CS_1
Journal

4
18
Fred
E_CS_1
Journal

4
19
Fred
E_CS_2
Book

2
20
Jim
E_CS_3
Journal

1
21
Paul
G_M_CS_1
Book

2
21
Jim
G_M_CS_1
Book

3
21
Mary
G_M_CS_1
Book


SELECT 
  STRING_AGG(DISTINCT a.author_name, ',') AS names, 
  w.work_title AS title
FROM work_author wa
JOIN work_subject ws
  ON wa.work_id = ws.work_id
JOIN author a 
  ON wa.author_id = a.author_id
JOIN work w
  ON wa.work_id = w.work_id
WHERE wa.work_id IN
(
  SELECT wa.work_id
  FROM work_author wa
  GROUP BY wa.work_id
  HAVING COUNT(wa.work_id) > 2
)
GROUP BY title;


names
title


Jim,Mary,Paul
G_M_CS_1

work_id	work_title	work_type
1	G_1	Book
2	G_2	Journal
3	M_1	Book
4	M_2	Journal
5	M_3	Journal
6	E_1	Book
7	CS_1	Book
8	CS_2	Book
9	CS_3	Journal
10	G_CS_1	Book
11	G_CS_2	Journal
12	G_CS_3	Journal
13	G_M_1	Journal
14	G_M_2	Journal
15	M_E_1	Book
16	M_E_2	Book
17	M_E_3	Journal
18	E_CS_1	Journal
19	E_CS_2	Book
20	E_CS_3	Journal
21	G_M_CS_1	Book

work_id	subject_id
1	1
2	1
3	2
4	2
5	2
6	3
7	4
8	4
9	4
10	1
10	4
11	1
11	4
12	1
12	2
13	1
13	2
14	1
14	2
15	2
15	3
16	2
16	3
17	2
17	3
18	3
18	4
19	3
19	4
20	3
20	4
21	1
21	2
21	4

work_id	author_id
1	1
2	1
2	3
3	3
4	3
4	4
5	3
5	4
6	4
7	2
8	2
9	1
9	2
10	1
11	1
11	2
12	1
12	2
13	3
14	1
14	3
15	2
15	4
16	2
17	4
18	2
18	4
19	4
20	2
21	1
21	2
21	3

author_id	waw	author_name	work_title	work_type
1	1	Paul	G_1	Book
1	2	Paul	G_2	Journal
3	2	Mary	G_2	Journal
2	7	Jim	CS_1	Book
2	8	Jim	CS_2	Book
1	9	Paul	CS_3	Journal
2	9	Jim	CS_3	Journal
1	10	Paul	G_CS_1	Book
1	11	Paul	G_CS_2	Journal
2	11	Jim	G_CS_2	Journal
1	12	Paul	G_CS_3	Journal
2	12	Jim	G_CS_3	Journal
3	13	Mary	G_M_1	Journal
1	14	Paul	G_M_2	Journal
3	14	Mary	G_M_2	Journal
2	18	Jim	E_CS_1	Journal
4	18	Fred	E_CS_1	Journal
4	19	Fred	E_CS_2	Book
2	20	Jim	E_CS_3	Journal
1	21	Paul	G_M_CS_1	Book
2	21	Jim	G_M_CS_1	Book
3	21	Mary	G_M_CS_1	Book

work_id	subject_id
1	1
2	1
3	2
4	2
5	2
6	3
7	4
8	4
9	4
10	1
10	4
11	1
11	4
12	1
12	2
13	1
13	2
14	1
14	2
15	2
15	3
16	2
16	3
17	2
17	3
18	3
18	4
19	3
19	4
20	3
20	4
21	1
21	2
21	4

work_id	author_id
1	1
2	1
2	3
3	3
4	3
4	4
5	3
5	4
6	4
7	2
8	2
9	1
9	2
10	1
11	1
11	2
12	1
12	2
13	3
14	1
14	3
15	2
15	4
16	2
17	4
18	2
18	4
19	4
20	2
21	1
21	2
21	3

work_id	subject_id
1	1
2	1
3	2
4	2
5	2
6	3
7	4
8	4
9	4
10	1
10	4
11	1
11	4
12	1
12	2
13	1
13	2
14	1
14	2
15	2
15	3
16	2
16	3
17	2
17	3
18	3
18	4
19	3
19	4
20	3
20	4
21	1
21	2
21	4

work_id	author_id
1	1
2	1
2	3
3	3
4	3
4	4
5	3
5	4
6	4
7	2
8	2
9	1
9	2
10	1
11	1
11	2
12	1
12	2
13	3
14	1
14	3
15	2
15	4
16	2
17	4
18	2
18	4
19	4
20	2
21	1
21	2
21	3