Postgres 12

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 data (group_col int, some_col float);

CREATE TABLE


insert into data
values 
(1, 0.97),
(1, 0.45),
(1, 0.45),
(1, 0.33),
(1, 0.42),

(2, 0.91),
(2, 0.42),
(2, 0.42),
(2, 0.33),
(2, 0.12);

INSERT 0 10


select group_col, 
       array_agg(some_col),
       mode() within group (order by some_col) as most_frequent_value
from data
group by group_col;


group_col
array_agg
most_frequent_value


1
{0.97,0.45,0.45,0.33,0.42}
0.45

2
{0.91,0.42,0.42,0.33,0.12}
0.42



SELECT 2

group_col	array_agg	most_frequent_value
1	{0.97,0.45,0.45,0.33,0.42}	0.45
2	{0.91,0.42,0.42,0.33,0.12}	0.42