Postgres 16

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 tmp (
  c TEXT
);

insert into tmp values
('{
                        "SerialNumber":"940860",
                        "ChannelIDs":[17,13,11,12,14],
                        "BeginningDate":"2023-05-20T11:53:39",
                        "EndDate":"2023-05-22T09:04:04"
}');

CREATE TABLE



INSERT 0 1


select
    c::json ->> 'SerialNumber' as SerialNumber,
    c::json ->> 'BeginningDate' as BeginningDate,
    c::json ->> 'EndDate' as EndDate
from tmp;


serialnumber
beginningdate
enddate


940860
2023-05-20T11:53:39
2023-05-22T09:04:04



SELECT 1


select row_number() over (order by ChannelID::text::int ) as ID, ChannelID
from tmp
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelIDs') as ChannelID 


id
channelid


1
11

2
12

3
13

4
14

5
17



SELECT 5