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?.

create table demographics 
(
id serial,
demographics jsonb
);
insert into demographics (demographics)
values
('{

"genre":{"women":10,"men":5} 

}');
1 rows affected

insert into demographics  (demographics)
values
('{

"genre":{"women":20,"men":25} 

}');
1 rows affected

with cte as 
(
   select *
        , genre.key as genre
        , row_number() over (partition by id order by value desc) as ord
     from demographics
    cross
     join lateral jsonb_each(demographics->'genre') genre
)
selecT id, genre, value from cte where ord = 1
 


id
genre
value


1
women
10

2
men
25