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 user_role_table  ("uid" int, "role" text);

INSERT INTO user_role_table VALUES
    (1, 'Main1Role'),
    (1, 'Main2Role'),
    (1, 'Sub1Role'),
    (1, 'Sub2Role'),
    (2, 'Main1Role'),
    (2, 'Sub1Role'),
    (3, 'Main1Role'),
    (3, 'Main2Role'),
    (4, 'Sub1Role'),
    (4, 'Sub2Role');
10 rows affected

SELECT
    role,
    SUM (
        CASE WHEN role IN ('Main1Role', 'Main2Role') THEN 1
             ELSE CASE WHEN ARRAY['Main1Role', 'Main2Role'] && array_agg THEN 0
                       ELSE 1 END
        END
    )
FROM (
SELECT
    *,
    array_agg(role) OVER (PARTITION BY uid)
FROM
    user_role_table
) s
GROUP BY role


role
sum


Main1Role
3

Sub2Role
1

Main2Role
2

Sub1Role
1


SELECT
   role_type,
   SUM (
        CASE WHEN role_type = 'MainRole' THEN 1
             ELSE CASE WHEN '{MainRole}' <@ array_agg THEN 0
                       ELSE 1 END
        END
    )
FROM (
    SELECT
        *,
        array_agg(role_type) OVER (PARTITION BY uid)
    FROM (
        SELECT DISTINCT ON (uid, role_type)
            *,
            CASE WHEN role IN ('Main1Role', 'Main2Role') THEN 'MainRole'
                ELSE 'SubRole'
            END AS role_type
        FROM 
            user_role_table
    ) s
) s
GROUP BY role_type
    


role_type
sum


SubRole
1

MainRole
3