Postgres 16

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 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE TABLE t (STUDENT int, SCORE int, DATE date);

CREATE TABLE


INSERT INTO t
    (STUDENT, SCORE, DATE)
VALUES
    (1, 6, DATE '2022-02-01'),
    (1, 2, DATE '2022-03-12'),
    (1, 5, DATE '2022-04-30'),
    (2, 2, DATE '2022-04-12'),
    (2, 0, DATE '2022-04-17'),
    (2, 7, DATE '2022-05-08'),
    (3, 2, DATE '2022-03-16'),
    (3, 3, DATE '2022-03-18'),
    (3, 2, DATE '2022-04-02'),
    (3, 9, DATE '2022-04-27'),
    (4, 4, DATE '2022-02-24'),
    (4, 0, DATE '2022-02-26'),
    (5, 3, DATE '2022-01-28'),
    (5, 0, DATE '2022-02-21'),
    (5, 4, DATE '2022-04-05');

INSERT 0 15


SELECT student, MIN(CASE WHEN score = 2 THEN date END) AS min_date
FROM t
GROUP BY student
ORDER BY student;


student
min_date


1
2022-03-12

2
2022-04-12

3
2022-03-16

4
null

5
null



SELECT 5