Postgres 11

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 scores (
    my_date date,
    score int
);

INSERT INTO scores VALUES
('2019-12-01', 2),
('2019-12-01', 4),
('2019-12-01', 5),
('2019-12-02', 3);

SELECT * FROM scores

CREATE TABLE



INSERT 0 4




my_date
score


2019-12-01
2

2019-12-01
4

2019-12-01
5

2019-12-02
3



SELECT 4


SELECT
    my_date,
    COUNT(score) FILTER (WHERE score BETWEEN 0 AND 2) AS "low0-2",
    COUNT(score) FILTER (WHERE score BETWEEN 3 AND 4) AS "high3-4",
    COUNT(score) FILTER (WHERE score = 5) AS "perfect"
FROM
    scores
GROUP BY my_date
ORDER BY my_date


my_date
low0-2
high3-4
perfect


2019-12-01
1
1
1

2019-12-02
0
1
0



SELECT 2


SELECT
    my_date,
    SUM(
        CASE 
            WHEN score BETWEEN 0 AND 2 THEN 1
            ELSE 0
        END
    ) AS "low0-2",
    SUM(
        CASE 
            WHEN score BETWEEN 3 AND 4 THEN 1
            ELSE 0
        END
    ) AS "high3-4",
    SUM(
        CASE 
            WHEN score = 5 THEN 1
            ELSE 0
        END
    ) AS "perfect"
FROM
    scores
GROUP BY my_date
ORDER BY my_date


my_date
low0-2
high3-4
perfect


2019-12-01
1
1
1

2019-12-02
0
1
0



SELECT 2