Postgres 9.6

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 births (
    name text,
    year int,
    month int,
    gender text
);

INSERT INTO births VALUES
('Sarah', 2018, 2, 'f'),
('Jack', 2016, 5, 'm'),
('James', 2017, 7, 'm'),
('Susan', 2017, 1, 'f');

SELECT * FROM births;
4 rows affected



name
year
month
gender


Sarah
2018
2
f

Jack
2016
5
m

James
2017
7
m

Susan
2017
1
f


SELECT 
    * 
FROM 
    births 
WHERE
    gender = 'f' 
    AND ((year = 2017 AND month > 4) OR (year > 2017))


name
year
month
gender


Sarah
2018
2
f


SELECT 
    * 
FROM 
    births 
WHERE
    gender = 'f' 
    AND to_date(year || '-' || month, 'YYYY-MM') > '2017-04-30'


name
year
month
gender


Sarah
2018
2
f