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 t (user_uuid text, value int, state bool);
insert into t values
('uuid_1', 111, true),
('uuid_1', 111, false),
('uuid_1', 111, false),
('uuid_1', 111, false),
('uuid_2', 333, true),
('uuid_2', 444, true),
('uuid_2', 555, false),
('uuid_3', 666, true),
('uuid_4', 777, false),
('uuid_1', 222, true),
('uuid_1', 222, true);

CREATE TABLE



INSERT 0 11


SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (SUM(CASE WHEN value = '111' AND state = true THEN 1 ELSE 0 END)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid


user_uuid
cnt
percent


uuid_2
3
0.0

uuid_3
1
0.0

uuid_1
6
16.7

uuid_4
1
0.0



SELECT 4


SELECT
    user_uuid,
    COUNT(*) cnt,
    ROUND (100.0 * (COUNT(*) FILTER (WHERE value = '111' AND state = true)) / COUNT(*), 1) AS percent
FROM
    t
GROUP BY
    user_uuid


user_uuid
cnt
percent


uuid_2
3
0.0

uuid_3
1
0.0

uuid_1
6
16.7

uuid_4
1
0.0



SELECT 4