Postgres 14

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 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE myTable
    (ts varchar(2), usr_id varchar(1), data varchar(1))
;
    
INSERT INTO myTable
    (ts, usr_id, data)
VALUES
    ('11', 'A', 'x'),
    ('11', 'A', 'x'),
    ('11', 'B', 'x'),
    ('11', 'C', 'x'),
    ('11', 'C', 'x'),
    ('11', 'C', 'x'),
    ('22', 'B', 'x'),
    ('33', 'C', 'x'),
    ('33', 'C', 'x'),
    ('33', 'D', 'x'),
    ('33', 'A', 'x')  
;
11 rows affected

SELECT 
    DISTINCT ts, 
    COUNT(CASE WHEN usr_id = 'A' THEN 1 END) AS count_a,
    COUNT(CASE WHEN usr_id = 'B' THEN 1 END) AS count_b,
    COUNT(CASE WHEN usr_id != 'A' AND usr_id != 'B' THEN 1 END) AS count_others
FROM myTable
GROUP BY ts
ORDER BY ts


ts
count_a
count_b
count_others


11
2
1
3

22
0
1
0

33
1
0
3