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 resultdata 
(
  class INTEGER NOT NULL,
  roll_number INTEGER NOT NULL,
  subjects VARCHAR (15) NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO resultdata(class,roll_number,subjects,marks) VALUES
(8, 1, 'math', 98),
(8, 1,'english', 88),
(8, 1,'science', 96),
(8, 1,'computer', 94),
(8, 2,'math', 78),
(8, 2,'english', 89),
(8, 2,'science', 87),
(8, 2,'computer', 99),
(9, 1,'math', 90),
(9, 1,'english', 85),
(9, 1,'science', 85),
(9, 1,'computer', 85),
(9, 2,'math', 95),
(9, 2,'english', 30),
(9, 2,'science', 40),
(9, 2,'computer', 80),
(10, 1,'math', 85),
(10, 1,'english', 90),
(10, 1,'science', 65),
(10, 1,'computer', 45),
(10, 2,'math', 50),
(10, 2,'english', 95),
(10, 2,'science', 65),
(10, 2,'computer', 85),
(10, 3,'math', 51),
(10, 3,'english', 61),
(10, 3,'science', 71),
(10, 3,'computer', 81);
28 rows affected

-- SELECT * FROM resultdata;  -- WORKS!
SELECT t1.class, t1.roll_number  -- Simple join - WORKS!
FROM resultdata t1
JOIN
(
  SELECT t2.class, t2.roll_number
  FROM resultdata t2
) AS t2
ON t1.class = t2.class;


class
roll_number


8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
1

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

8
2

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
1

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

9
2

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
1

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
2

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3

10
3


SELECT t1.class, t1.roll_number,      -- Query 1 - works!
  SUM(CASE WHEN t1.subjects = 'math'     
    THEN t1.marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN t1.subjects = 'computer' 
    THEN t1.marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN t1.subjects = 'english' 
    THEN t1.marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN t1.subjects = 'science' 
    THEN t1.marks ELSE 0 END)  AS sciemark
FROM resultdata t1
GROUP BY t1.class, t1.roll_number;


class
roll_number
mathmark
compmark
englmark
sciemark


10
2
50
85
95
65

8
2
78
99
89
87

9
2
95
80
30
40

10
3
51
81
61
71

9
1
90
85
85
85

10
1
85
45
90
65

8
1
98
94
88
96


SELECT class, MAX(marks) AS maxmark  -- Query 2 - works!
FROM resultdata
WHERE subjects = 'english'
GROUP BY class;


class
maxmark


8
89

9
85

10
95


SELECT t1.class, t1.roll_number,
  SUM(CASE WHEN t1.subjects = 'math'     
    THEN t1.marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN t1.subjects = 'computer' 
    THEN t1.marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN t1.subjects = 'english' 
    THEN t1.marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN t1.subjects = 'science' 
    THEN t1.marks ELSE 0 END)  AS sciemark
FROM resultdata t1
GROUP BY t1.class, t1.roll_number 
JOIN 
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
) AS t2
ON t1.class = t2.class AND
   t1.englmark = t2.maxmark;

ERROR:  syntax error at or near "JOIN"
LINE 12: JOIN 
         ^


WITH cte1 AS
(
  SELECT t1.class, t1.roll_number,      -- Query 1 - works!
  SUM(CASE WHEN t1.subjects = 'math'     
    THEN t1.marks ELSE 0 END) AS mathmark,
  SUM(CASE WHEN t1.subjects = 'computer' 
    THEN t1.marks ELSE 0 END) AS compmark,
  SUM(CASE WHEN t1.subjects = 'english' 
    THEN t1.marks ELSE 0 END)  AS englmark,
  SUM(CASE WHEN t1.subjects = 'science' 
    THEN t1.marks ELSE 0 END)  AS sciemark
  FROM resultdata t1
  GROUP BY t1.class, t1.roll_number
),
cte2 AS 
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
)
SELECT t1.class, t1.roll_number, t1.mathmark, t1.englmark
FROM cte1 t1
JOIN cte2 t2
  ON t1.class = t2.class AND
     t1.englmark = t2.maxmark
ORDER BY class ASC;


class
roll_number
mathmark
englmark


8
2
78
89

9
1
90
85

10
2
50
95

class	roll_number
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3

class	roll_number	mathmark	compmark	englmark	sciemark
10	2	50	85	95	65
8	2	78	99	89	87
9	2	95	80	30	40
10	3	51	81	61	71
9	1	90	85	85	85
10	1	85	45	90	65
8	1	98	94	88	96

class	roll_number
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3

class	roll_number	mathmark	compmark	englmark	sciemark
10	2	50	85	95	65
8	2	78	99	89	87
9	2	95	80	30	40
10	3	51	81	61	71
9	1	90	85	85	85
10	1	85	45	90	65
8	1	98	94	88	96

class	roll_number
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	1
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
8	2
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	1
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
9	2
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	1
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	2
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3
10	3

class	roll_number	mathmark	compmark	englmark	sciemark
10	2	50	85	95	65
8	2	78	99	89	87
9	2	95	80	30	40
10	3	51	81	61	71
9	1	90	85	85	85
10	1	85	45	90	65
8	1	98	94	88	96