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 students(
id int,
student_name text
);
INSERT INTO students VALUES
(1, 'A'),(2,'B'),(3,'C');
SELECT * FROM students
3 rows affected
| id | student_name |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
CREATE TABLE courses(
id int,
student_id int,
course_name text,
credits int
);
INSERT INTO courses VALUES
(1, 1, 'math', 3),
(2, 1, 'history', 3),
(3, 2, 'math', 5),
(4, 2, 'history', 3),
(5, 2, 'english', 1),
(6, 3, 'math', 5);
SELECT * FROM courses
6 rows affected
| id | student_id | course_name | credits |
|---|---|---|---|
| 1 | 1 | math | 3 |
| 2 | 1 | history | 3 |
| 3 | 2 | math | 5 |
| 4 | 2 | history | 3 |
| 5 | 2 | english | 1 |
| 6 | 3 | math | 5 |
SELECT
*,
(c.course_name = 'math' AND c.credits = 5)
OR
(c.course_name = 'history' AND c.credits = 3)
FROM students s
JOIN courses c
ON c.student_id = s.id
| id | student_name | id | student_id | course_name | credits | ?column? |
|---|---|---|---|---|---|---|
| 1 | A | 1 | 1 | math | 3 | f |
| 1 | A | 2 | 1 | history | 3 | t |
| 2 | B | 3 | 2 | math | 5 | t |
| 2 | B | 4 | 2 | history | 3 | t |
| 2 | B | 5 | 2 | english | 1 | f |
| 3 | C | 6 | 3 | math | 5 | t |
SELECT
s.id,
COUNT(*) FILTER (WHERE
(c.course_name = 'math' AND c.credits = 5)
OR
(c.course_name = 'history' AND c.credits = 3)
)
FROM students s
JOIN courses c
ON c.student_id = s.id
GROUP BY s.id
| id | count |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
SELECT
s.id
FROM students s
JOIN courses c
ON c.student_id = s.id
GROUP BY s.id
HAVING COUNT(*) FILTER (WHERE
(c.course_name = 'math' AND c.credits = 5)
OR
(c.course_name = 'history' AND c.credits = 3)
) = 2
| id |
|---|
| 2 |