Postgres 9.5

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 tabela(id serial, col1 varchar(10), col2 integer);
insert into tabela(col1, col2) 
values 
('XX', 1),
('XX', 1),
('XX', 1),
('XX', 1),
('XX', 1),
('XX', 2),
('XX', 2),
('XX', 6),
('XX', 6),
('XX', 6),
('XX', 6),
('XX', 7),
('YY', 2),
('YY', 2),
('YY', 3),
('YY', 3),
('YY', 3),
('YY', 3),
('YY', 3),
('YY', 3),
('YY', 5),
('YY', 5),
('YY', 8),
('YY', 8),
('YY', 8);
25 rows affected

select col1, col2, count(*) over(partition by col1, col2) ilosc from tabela;


col1
col2
ilosc


XX
1
5

XX
1
5

XX
1
5

XX
1
5

XX
1
5

XX
2
2

XX
2
2

XX
6
4

XX
6
4

XX
6
4

XX
6
4

XX
7
1

YY
2
2

YY
2
2

YY
3
6

YY
3
6

YY
3
6

YY
3
6

YY
3
6

YY
3
6

YY
5
2

YY
5
2

YY
8
3

YY
8
3

YY
8
3


select col1, col2, count(*) ilosc from tabela group by col1, col2 order by col1, col2;


col1
col2
ilosc


XX
1
5

XX
2
2

XX
6
4

XX
7
1

YY
2
2

YY
3
6

YY
5
2

YY
8
3


with t as
(select col1, col2, count(*) ilosc from tabela group by col1, col2)
select * from t where (col1, ilosc) in (select col1, max(ilosc) from t group by 1)


col1
col2
ilosc


XX
1
5

YY
3
6

col1	col2	ilosc
XX	1	5
XX	1	5
XX	1	5
XX	1	5
XX	1	5
XX	2	2
XX	2	2
XX	6	4
XX	6	4
XX	6	4
XX	6	4
XX	7	1
YY	2	2
YY	2	2
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	5	2
YY	5	2
YY	8	3
YY	8	3
YY	8	3

col1	col2	ilosc
XX	1	5
XX	1	5
XX	1	5
XX	1	5
XX	1	5
XX	2	2
XX	2	2
XX	6	4
XX	6	4
XX	6	4
XX	6	4
XX	7	1
YY	2	2
YY	2	2
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	5	2
YY	5	2
YY	8	3
YY	8	3
YY	8	3

col1	col2	ilosc
XX	1	5
XX	1	5
XX	1	5
XX	1	5
XX	1	5
XX	2	2
XX	2	2
XX	6	4
XX	6	4
XX	6	4
XX	6	4
XX	7	1
YY	2	2
YY	2	2
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	3	6
YY	5	2
YY	5	2
YY	8	3
YY	8	3
YY	8	3