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 mytable (
id varchar(10),
rowNum int,
status varchar(10)
);
insert into mytable values
('aa23' ,1 ,'HIT'),
('aa23' ,1 ,'HIT'),
('aa23' ,1 ,'HIT'),
('aa23' ,2 ,'MISS'),
('aa23' ,2 ,'MISS'),
('aa24' ,2 ,'MISS'),
('aa24' ,2 ,'MISS');
CREATE TABLE
INSERT 0 7
with cte as (
select id, rowNum, status
from mytable
group by id, rowNum, status
)
select status, count(1)
from cte
where id = 'aa23'
group by status;
| status | count |
|---|---|
| HIT | 1 |
| MISS | 1 |
SELECT 2